Forum Stats

  • 3,838,846 Users
  • 2,262,407 Discussions
  • 7,900,768 Comments

Discussions

BEFORE UPDATE trigger with timestamp is not working as expected

Midhun GT
Midhun GT Member Posts: 31 Blue Ribbon
edited Oct 27, 2015 11:47AM in General Database Discussions

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.
Tagged:
Midhun GT

Best Answer

  • Unknown
    edited Oct 26, 2015 1:47PM 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

  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited 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

    Midhun GT
  • wbriceno
    wbriceno Member Posts: 64
    edited 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.

    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.

    Midhun GT
  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited 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

    Midhun GT
  • Unknown
    edited 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
    wbriceno Member Posts: 64
    edited 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
  • Midhun GT
    Midhun GT Member Posts: 31 Blue Ribbon
    edited Oct 26, 2015 9:38AM

    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
    Midhun GT Member Posts: 31 Blue Ribbon
    edited 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
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited 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
    edited Oct 26, 2015 1:47PM 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.

  • Midhun GT
    Midhun GT Member Posts: 31 Blue Ribbon
    edited Oct 27, 2015 11:47AM

    @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

This discussion has been closed.