Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
BEFORE UPDATE trigger with timestamp is not working as expected

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.
Best Answer
-
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.
Answers
-
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 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.
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.
-
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 schemaoe
, issue the following statement: AUDIT INSERT, UPDATE ON oe.customers;Regards,
Juan M
-
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 theOE.CUSTOMERS
table. In this tutorial, you enable standard auditing, enable auditing for theSELECT
SQL statement, run theSELECT
SQL statement on theOE.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
FLASHBACK
TABLE
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.
-
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.
-
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.
-
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.
-
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
-
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.
-
@rp0428 your first post was not pleasant to read (might be because of too much CAPS?). The second post is making sense to me. It is confirming my understanding of this trigger.
And the below idea is good. I will try this (might be by introducing an artificial delay like :NEW - 1second etc) and let me update on how it goes.
/*
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.
*/
Thanks you very much for your valuable time
Thanks,
MidhunGT