When ever i do the update on the columns like Emp_Name or Emp_Salary a new
record is inserted into the same table with new S.No
For Ex. Emp_Name "John" Changed to "Sunil" new record is inserted into the table with S.No"5".
Note:Emp_ID will remain same and it can't be changed/updated.
Now My requirement is "I should generate the report(using Cursors in SQL Stored procedure)
for audit log" for the "employee_Id 123" The report should be like below
Changed Field Old Value New value
Employee Name John Sunil
Employee Name Sunil Raj
Employee Nmae Raj Joseph
Employee Salary 100 500
Employee Salary 500 101
I would suggest this:
DECLARE C CURSOR FOR
SELECT S.NO, EMP_ID, EMP_NAME, EMP_SALARY
FROM EMP_TABLE
GROUP BY EMP_ID
ORDER BY S.NO;
Fetch each row, store it in working storage, fetch next row.
Compare the EMP_IDs of current and previous. Both should be same. Identify the changed value prepare a report.
As simple as that.
I assume that for each update the insert is done according the increasing order of S.No.
Manikanth
P.S: Do I make any sense or Am I completely wrong?
