update /insert query i table should have a before/after image statement-CodePudding

I am required to have before/after image statement for every update /insert operation done on a table in DB in order to properly record the before /after image in the spool file of the script.

I am not sure how to do this like example for this query :

update employee set divison = 'IT' where empId = 2223;

Any help would be appreciated.

CodePudding user response：

if I understand Your question correctly, I would use table triggers. You have option to write code that executes before and after insert/update statament.

Since update command is complex, you have both images, "BEFORE" and "AFTER" (data did exist before update and exists after update). So, I would write trigger that executes BEFORE update, and that collects all the :OLD data (this notation is used for table fields before data is changed using update command) and wrote them to the output (or insert them into some kind of history table for later audit). Also I would write another trigger that is executed AFTER update and do the same thing with :NEW data (wrote to output or something else). I think that for insert is trivial, you don't have "BEFORE" image, only "AFTER" so You can write AFTER trigger, as for update command. I hope that You can understand my answer.

To add some code here:

CREATE OR REPLACE TRIGGER BU_EMP
BEFORE UPDATE
ON EMP
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('BEFORE: '||:OLD.EMP_ID||'  '||:OLD.EMP_NAME||' '||:OLD.DEPARTMENT); 
EXCEPTION
    WHEN OTHERS THEN RAISE;
END;

And, similar:

CREATE OR REPLACE TRIGGER AU_EMP
AFTER UPDATE
ON EMP
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('AFTER: '||:NEW.EMP_ID||'  '||:NEW.EMP_NAME||'  '||:NEW.DEPARTMENT); 
EXCEPTION
    WHEN OTHERS THEN RAISE;
END;

And now, if You have record with EMP_ID=2233, after update You will have two dbms_output lines, one with data BEFORE, and one with data AFTER update command.

Best regards, Aleksandar

CodePudding user response：

It sounds like you just want to select from the table before and after the update.

-- Before
SELECT *
FROM   employee
WHERE  empId = 2223;

--Change
UPDATE employee
SET   division = 'IT'
WHERE empId = 2223;

-- After
SELECT *
FROM   employee
WHERE  empId = 2223;