new at triggers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: new at triggers

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82

    Exclamation

    I want to write a trigger that will show me the following:

    who makes changes to a table(s) & when
    which field(s) changed
    the before and after values of said field(s)

    my pl/sql skills are limited, and writing trigers is completely new to me.

    Advice anyone?


    thanks in advance

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Dunno how you would get the before and after values of the fields that have changed, but as far as who makes the changes is concerned, you can audit them.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You can do it in trigger.
    Use :NEW and :OLD in the trigger. All you need is another table where you always insert a row with old value and new value, the user id, data and time stamp.


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You would write an AFTER INSERT, UPDATE, DELETE trigger. Inside this trigger, you would inspect the :new and :old values of each field to see if they have changed. If they have changed, you would insert the appropriate information into your audit table.

    I did something similar to this before. Basically, we had a table TABLE_A and we wanted a history of what changes were made to the data. We created another table AUDIT_TABLE_A that had the same structure as TABLE_A, but contained three additional fields, USERNAME, AUDIT_TIMESTAMP, OPERATION. When an insert happened, we captured the :new values (1 row in AUDIT_TABLE_A). When a delete happened, we captured the :old values (1 row in AUDIT_TABLE_A). When an update happened, we captured both the :new and :old values (2 rows in AUDIT_TABLE_A). AUDIT_TABLE_A was partitioned by day and we kept a rolling window of 30 days in AUDIT_TABLE_A.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is the trigger. I used scott user and emp table.
    I used EMP_LOG table with additional columns ROW_TYPE ,
    UPDATED_DATE, USERID .

    create or replace trigger TR_EMP_BU
    before update on emp
    for each row
    begin
    INSERT INTO EMP_LOG (EMPNO,
    ENAME,
    JOB ,
    MGR ,
    HIREDATE ,
    SAL ,
    COMM ,
    DEPTNO ,
    ROW_TYPE ,
    UPDATED_DATE,
    USERID )
    VALUES ( :OLD.EMPNO,
    :OLD.ENAME,
    :OLD.JOB ,
    :OLD.MGR ,
    :OLD.HIREDATE ,
    :OLD.SAL ,
    :OLD.COMM ,
    :OLD.DEPTNO ,
    'OLD',
    SYSDATE,
    USER);
    INSERT INTO EMP_LOG (EMPNO,
    ENAME,
    JOB ,
    MGR ,
    HIREDATE ,
    SAL ,
    COMM ,
    DEPTNO ,
    ROW_TYPE ,
    UPDATED_DATE,
    USERID )
    VALUES ( :NEW.EMPNO,
    :NEW.ENAME,
    :NEW.JOB ,
    :NEW.MGR ,
    :NEW.HIREDATE ,
    :NEW.SAL ,
    :NEW.COMM ,
    :NEW.DEPTNO ,
    'NEW',
    SYSDATE,
    USER);

    end;
    /

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    As shown you can do this with triggers, but i thought LogMiner is a utility that can give you this information; in 8.1.6.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    LogMiner will not give history of changes (updates) made on the table.

  8. #8
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    Thanks guys - what I had in mind was I'd create a new table, with 2 columns for each column in the audited table, one for the old value, one for the new, rather than 2 rows , one with old & one with new values.

    So here is what I came up with:

    create or replace trigger tr_aud_table
    before update on schema.audit_changes
    for each row
    begin
    insert into schema.audit_changes
    (ref_number,
    change_date,
    user,
    OLD_col1,
    OLD_col2,
    OLD_col3)
    values
    (:OLD.ref_NUMBER,
    SYSDATE,
    USER,
    OLD:col1,
    OLD:col2,
    OLD:col3)
    UPDATE schema.AUDIT_CHANGES
    SET
    NEW_col1 = NEW:col1,
    NEW_col2 = NEW:col2,
    NEW_col3 = NEW:col3
    WHERE ref_number = OLD:ref_number
    END;

    will this work?
    does "USER" pick up the user changing the table?

    thanks again

  9. #9
    Join Date
    Jul 2000
    Posts
    296
    The function user returns the current user, in your case the user changing the table.

    With this trigger the table is mutating, the table is affected by the triggering event. The trigger updates the table audit_changes on update of the table audit_changes.

    Why do you first insert and then update the table? Now you only see the last changes made.

    I think tamilselvan has given the the trigger you need.

  10. #10
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    I realized my mistake later, it should read schema.table_name - that is, referring to the table I want to audit.

    Yes, I will try that trigger I believe it will fit my needs. I am still going to try one that only has one row in my audit table for each row changed in the audited table.

    thanks again for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width