we have a scenario where i have to audit update operations on a table.
i created a before update TRIGGER, So that when ever an update statement is happening on the main table, a before image of the rows is captured in the audit table with timestamp.
since it is BEFORE UPDATE, Ideally the audit table timestamp (TRG_INS_TMST) should be less than main table timestamp (IBMSNAP_LOGMARKER) VALUE, I mean TRIGGER should happen before the update.
(i could somehow understand the UPDATE statement is phrased with earlier SYSTIMESTAMP before the TRIGGER is evaluated and hence UPDATE is having earlier timestamp than TRIGGER, but this is not what we wanted. We want BEFORE update)
'Table' IBM_SNAPOPERATION IBM_SNAPLOGMARKER
---- ----------------- -------------------------------
T1 U 13-OCT-15 03.07.01.775236 AM <<---------- This is the main table, This should have the latest timestamp
T2 I 13-OCT-15 03.07.01.775953 AM
here is my test case.
DELETE FROM TEST_TRIGGER_1;
DELETE FROM TEST_TRIGGER_2;
SELECT 'T1', ibm_snapoperation, ibm_snaplogmarker FROM TEST_TRIGGER_1
UNION
SELECT 'T2', ibm_snapoperation, TRG_INS_TMST FROM TEST_TRIGGER_2;
INSERT INTO TEST_TRIGGER_1 (ID,ibm_snapoperation, ibm_snaplogmarker)
VALUES (1, 'I', SYSTIMESTAMP);
COMMIT;
SELECT 'T1', ibm_snapoperation, ibm_snaplogmarker FROM TEST_TRIGGER_1
UNION
SELECT 'T2', ibm_snapoperation, TRG_INS_TMST FROM TEST_TRIGGER_2;
UPDATE TEST_TRIGGER_1
SET IBM_SNAPOPERATION = 'U', ibm_snaplogmarker = SYSTIMESTAMP;
COMMIT;
SELECT 'T1', ibm_snapoperation, ibm_snaplogmarker FROM TEST_TRIGGER_1
UNION
SELECT 'T2', ibm_snapoperation, TRG_INS_TMST FROM TEST_TRIGGER_2;
Trigger def:
CREATE OR REPLACE TRIGGER etl_dbo.TEST_TRIGGER_1_TRG BEFORE UPDATE OF IBM_SNAPOPERATION
ON TEST_TRIGGER_1 REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
WHEN (
NEW.IBM_SNAPOPERATION= 'U'
)
DECLARE
V_SQLCODE VARCHAR2(3000);
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO etl_dbo.TEST_TRIGGER_2
(ID,
IBM_SNAPOPERATION,
IBM_SNAPLOGMARKER,
TRG_INS_TMST
)
VALUES (:OLD.ID,:OLD.IBM_SNAPOPERATION,:OLD.IBM_SNAPLOGMARKER,SYSTIMESTAMP)
;
--COMMIT;
END;
/
Output is something like this
1 row deleted.
1 row deleted.
no rows selected.
1 row created.
Commit complete.
'T1' IBM_SNAPOPERATION IBM_SNAPLOGMARKER
---- ----------------- -------------------------------
T1 I 13-OCT-15 03.07.00.927546 AM
1 row selected.
1 row updated.
Commit complete.
'T1' IBM_SNAPOPERATION IBM_SNAPLOGMARKER
---- ----------------- -------------------------------
T1 U 13-OCT-15 03.07.01.775236 AM <<---------- This is the main table, This should have the latest timestamp
T2 I 13-OCT-15 03.07.01.775953 AM
2 rows selected.