Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Unanswered: After Update Trigger Question

    Basically, I want to complete the following process.

    Update table with data

    Insert data to another table for auditing

    Set the original table field to NULL after the audit is completed.

    Below is my trigger, any assistance would be great.

    CREATE OR REPLACE TRIGGER audit_TABLENAME_stk
    after insert or delete or update on TABLENAME
    for each row
    declare
    time_now DATE;
    begin
    time_now := sysdate;
    if updating ('QTYONHAND') then
    insert into audit_TABLENAME_stk values
    ('QTYONHANDupdate',user || ' ' || :new.userid,: old.item,time_now, : old.allocatedoe, :new.allocatedoe, : old.qtyonhand, :new.qtyonhand, : old.stkloc);
    end if;
    update TABLENAME set userid = NULL where stkloc = :new.stkloc and item = new.item;
    end;
    /
    Jason

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    CREATE OR REPLACE TRIGGER audit_TABLENAME_stk
    after update OF QTYONHAND on TABLENAME
    for each row
    begin
    insert into audit_TABLENAME_stk values
    ('QTYONHANDupdate',user || ' ' || :new.userid,: old.item,SYSDATE, : old.allocatedoe, :new.allocatedoe, : old.qtyonhand, :new.qtyonhand, : old.stkloc);
    :NEW.USERID := NULL;
    end;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Posts
    53
    Bill,

    Thanks for the suggestion, however, I tried this previously and it states you cannot change values in an after trigger.

    Jason
    Jason

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why not use a BEFORE trigger then?

  5. #5
    Join Date
    Jan 2004
    Posts
    53
    I cannot use a before trigger because the audit will not function correctly.

    I need to capture the user with the trigger and then set the user field in the original table to null.
    Jason

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by jhuck
    I cannot use a before trigger because the audit will not function correctly.

    I need to capture the user with the trigger and then set the user field in the original table to null.
    Why do you think it won't function properly? The audit record will still be written, and the column will still be set to null?

  7. #7
    Join Date
    Jan 2004
    Posts
    53
    I will give it a shot, I was of the understanding that the audit would then display the user as null.
    Jason

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sorry, I forgot to change the after to a before, my mistake.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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