BEFORE UPDATE trigger with timestamp is not working as expected

Midhun GT · Oct 13, 2015 3:32AM

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.

Unknown · Oct 26, 2015 1:47PM

But for some reason, even after creating "AFTER" update trigger, it is not working as expected. Meaning - the main table is not having the latest timestamp for given

That is CORRECT - as I told you in my reply earlier. Reread my reply.

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)

As I told you before your UPDATE statement occurs BEFORE the trigger is fired.

In spite of what other responders have said it makes NO DIFFERENCE if you use a BEFORE UPDATE or AFTER UPDATE trigger. Your UPDATE statement STILL executes BEFORE the trigger.

IT HAS TO - it is your update statement being processed that causes the trigger to fire.

Your update statement includes SYSTIMESTAMP. So when your update statement is processed the value of SYSTIMESTAMP 'at that exact moment' is captured.

Then your trigger fires and begins executing. ANY reference to SYSTIMESTAMP that you use in your trigger can NOT be earlier than the value from BEFORE the trigger was executed. That is IMPOSSIBLE.

The trigger can use the SAME value by referencing :NEW and the column name you store the value in. Or the trigger can get its own value as your code is doing now.

But the value of SYSTIMESTAMP in the trigger will NEVER be earlier than the value in your query.

And neither of those values can actually be used to tell you when the changes were really COMMITTED since the trigger does not, and CAN NOT, know when, or if, a commit occurs.

Reread my first reply - it explains all of that.

Zlatko Sirotic · Oct 13, 2015 4:33AM

Do not use the BEFORE row trigger for auditing, but AFTER row trigger.

BEFORE trigger can be run multiple times for a single row.

Regards,

Zlatko

wbriceno · Oct 14, 2015 3:10PM

Hi Midhun;

I was testing what you did. Following my results:

---------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

CREATE TABLE T1

( id NUMBER,

operation VARCHAR2(10),

maker VARCHAR2(10),

date_tmst TIMESTAMP );

CREATE TABLE T1_AUDIT

( id NUMBER,

operation VARCHAR2(10),

maker VARCHAR2(10),

date_tmst TIMESTAMP );

CREATE OR REPLACE TRIGGER t1_before_upd

BEFORE UPDATE

ON T1

FOR EACH ROW

DECLARE

v_error VARCHAR2(1000);

BEGIN

INSERT INTO T1_AUDIT (id, operation, maker, date_tmst)

VALUES (:old.id, :old.operation, :old.maker, SYSTIMESTAMP);

END;

/

DELETE FROM T1;

DELETE FROM T1_AUDIT;

COMMIT;

SELECT 'T1', id, operation, maker, date_tmst FROM T1

UNION

SELECT 'T2', id, operation, maker, date_tmst FROM T1_AUDIT;

INSERT INTO T1 (id, operation, maker, date_tmst)

VALUES (1, 'I', 'IBM', SYSTIMESTAMP);

COMMIT;

SELECT 'T1', id, operation, maker, date_tmst FROM T1

UNION

SELECT 'T2', id, operation, maker, date_tmst FROM T1_AUDIT;

UPDATE T1

SET operation = 'U',

date_tmst = SYSTIMESTAMP

WHERE id = 1;

COMMIT;

SELECT 'T1', id, operation, maker, date_tmst FROM T1

UNION

SELECT 'T2', id, operation, maker, date_tmst FROM T1_AUDIT;

========================================================================

1 rows deleted.

committed.

no rows selected

1 rows inserted.

committed.

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 I IBM 14/10/2015 01:03:19.531000000 PM

1 rows updated.

committed.

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 U IBM 14/10/2015 01:03:19.540000000 PM

T2 1 I IBM 14/10/2015 01:03:19.540000000 PM

As you can see, trigger is working fine, and timestamp is good for both rows.

Tell us, what Oracle Database version are you using?

Regards.

JuanM · Oct 14, 2015 3:22PM

Zlatko Sirotic wrote:

Do not use the BEFORE row trigger for auditing, but AFTER row trigger.
BEFORE trigger can be run multiple times for a single row.

Regards,
Zlatko

Hi,

@Zlatko Sirotic, in fact, he does not have to implement triggers to audit dml operations when AUDIT solution exists in database.

@Midhun GT don't re inventing black thread. It's better using AUDIT.

Here an example of AUDIT. Check teh doc AUDIT

Auditing Inserts and Updates on a Table: Example To choose auditing for every statement that inserts or updates a row in the customers table in the schema oe, issue the following statement: 
AUDIT INSERT, UPDATE ON oe.customers;

Regards,

Juan M

Unknown · Oct 14, 2015 4:09PM

we have a scenario where i have to audit update operations on a table.

EXCELLENT! Just enable AUDITING for that table and Oracle will do ALL of the work for you.

The Oracle docs have a tutorial that show you EXACTLY how to do that easily

https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50528

Tutorial: Creating a Standard Audit Trail

Suppose you wanted to audit SELECT statements on the OE.CUSTOMERS table. In this tutorial, you enable standard auditing, enable auditing for the SELECT SQL statement, run the SELECT SQL statement on the OE.CUSTOMERS table, and then check its audit file.

Try that example - which uses SELECT.

Once you have that working modify the example to use UPDATE and you have your solution.

 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.

Oracle can ALREADY do that for you - why are you reinventing everything yourself?

It is called FLASHBACK - you can look at data as it existed in the past if you have a problem.

Again - the Oracle docs are your FRIEND. Get to know your friend.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm

Purpose Use the FLASHBACKTABLE statement to restore an earlier state of a table in the event of human or application error.

You haven't told us what BUSINESS REQUIREMENT you are trying to solve. All you said was you have to audit updates on a table.

WHY? What is the goal/purpose of doing that? Without knowing how you intend to use the audit we can't really give you any 'solutions' other than using AUDIT functionality.

 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.

Does that make SENSE to you? It makes NO SENSE to me.

It is the UPDATE that causes the trigger to fire. So how can the trigger happen BEFORE the update?

 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)

Again - tell us the ACTUAL REQUIREMENTS.

NO DML IS PERMANENT in the database until a COMMIT happens.

A trigger does not, and can not, know if a commit will EVER happen.

The UPDATE statement might be executed at 11 pm and fire the trigger. The COMMIT, if it happens at all, might happen several hours later on the next day.

So two users can execute update statements at the same time but whoever commits LAST will have the SAME timestamp on that 'changed' data as the one that committed first - even if those commits happen hours apart.

A trigger can NOT capture the timestamp for when a change was recorded n the database. At best it can capture when you STARTED to make a change.

Don't try to use timestamps in triggers to capture the DATE/TIME that a change occured. Changes occur when COMMITs happen.

The REDO logs will already capture those for you. Use LOG MINER and examine those logs to your hearts content. But don't muck up the tables with triggers and code to do what Oracle already does CORRECTLY.

wbriceno · Oct 14, 2015 4:29PM

Hi,

I did one more test, but using an AFTER trigger. Look at results.

1 rows inserted.

committed.

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 I IBM 14/10/2015 02:24:13.822000000 PM

1 rows updated.

committed.

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 U IBM 14/10/2015 02:24:13.835000000 PM

T2 1 I IBM 14/10/2015 02:24:13.836000000 PM

For better results, use an AFTER trigger for audit values.

Regards.

Midhun GT · Oct 26, 2015 9:30AM

thanks @wbriceno and @Zlatko Sirotic for the valuable feedback. But for some reason, even after creating "AFTER" update trigger, it is not working as expected. Meaning - the main table is not having the latest timestamp for given

eg: this is @wbriceno result:

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 U IBM 14/10/2015 02:24:13.835000000 PM

T2 1 I IBM 14/10/2015 02:24:13.836000000 PM

But i wanted

'T1' ID OPERATION MAKER DATE_TMST

---- ---------- ---------- ---------- ----------------------------------

T1 1 U IBM 14/10/2015 02:24:13.836000000 PM

T2 1 I IBM 14/10/2015 02:24:13.835000000 PM

So when we generate a report order by timestamp column descending, T1's ID=1 will be first row in the list followed by T2's ID=1 (past image)

At least, that is what my understanding when i crated "BEFORE" trigger.

Note: This auditing is for reporting purpose only. "Compliance audit" is done by guardium (external tool) in our environment.

Midhun GT · Oct 26, 2015 9:43AM

hi @juanM. update: This auditing is for reporting purpose only. "Compliance audit" is done by guardium (external tool) in our environment. Application need this specific requirement for reporting purpose.

Zlatko Sirotic · Oct 26, 2015 10:40AM

This is expected behavior.

Your UPDATE statement is: UPDATE TEST_TRIGGER_1 SET IBM_SNAPOPERATION = 'U', ibm_snaplogmarker = SYSTIMESTAMP;

This SYSTIMESTAMP is smaller (earlier) of SYSTIMESTAMP you will read later in the BEFORE or AFTER trigger.

Regards,

Zlatko

Unknown · Oct 26, 2015 1:47PM

But for some reason, even after creating "AFTER" update trigger, it is not working as expected. Meaning - the main table is not having the latest timestamp for given

That is CORRECT - as I told you in my reply earlier. Reread my reply.

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)