Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    17

    Unanswered: Using AfterInsertTrigger for audit table

    Hi All,

    I am using After Insert Trigger to re-write the record going to base table into the corresponding audit table. The struction of both tables are identical, except that the audit table has one more column - audit_time. Here is how I defined the trigger:

    Create or replace trigger dup_rec after insert on table_A for each row
    begin PK_comm.create_audit_rec(table_A, table_b, :new.ROWID); end;


    In the package PK_comm, I defined the procedure create_audit_rec as "PRAGMA AUTONOMOUS_TRANSACTION" as following:


    Procedure create_audit_rec(baseTable IN VARCHAR2, auditTable IN VARCHAR2, cRowId IN VARCHAR2)
    IS
    PRAGME AUTONOMOUS_TRANSACTION;

    Insert into auditTable(audit_time, col1, col2, col3)
    select systimestamp, col1, col2, col3
    from baseTable
    where ROWID = chartorowid('v_rowID');

    It seems the record is NOT added to the audit table when I tried to insert one record into the base table. Is it possible that the insertion to baseTable have not completed when the trigger is fired? If so, what is the work around?

    Thanks for your help,

    Mike

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I commend you on doing an excellent job at obfuscating reality.
    You have a mystery & we have no clues.
    How can we reproduce what you report?
    Please post results from following SQL:
    SELECT * FROM V$VERSION;

    hmmm.....
    http://forums.oracle.com/forums/thre...63769&tstart=0
    Last edited by anacedent; 08-04-11 at 16:40.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    Create or replace trigger dup_rec 
    after insert on table_A 
    for each row
    begin
    Insert into auditTable(audit_time, col1, col2, col3)
    values(systimestamp,:new.col1,:new.col2,:new.col3);
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2010
    Posts
    17
    Yeah, it works this way! However, I would like to know it does NOT work when I use ROWID to get the value. With ROWID approach, I don't need replace the trigger when there is any structure change on the table. I want write every column in base table into audit table.

    Thanks,

Posting Permissions

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