This discussion is archived
6 Replies Latest reply: Dec 12, 2012 1:51 AM by 972703 RSS

Table Level Audit Trigger

972703 Newbie
Currently Being Moderated
For the life of me, I can't figure out why this isn't working:
SQL Developer Version 3.2.20.09


CREATE OR REPLACE
trigger TRG_PROP_AUDIT

AFTER insert or update or delete on PROP_BOOK_SI
for each row

begin
IF INSERTING THEN
INSERT INTO PROPERTY_AUDIT
(AUDIT_DATE, ENTERED_BY, OPERATION)
VALUES
(AUDIT_DATE, ENTERED_BY, 'INSERT');

ELSIF updating then
INSERT INTO PROPERTY_AUDIT
(AUDIT_DATE, ENTERED_BY, OPERATION)
VALUES
(AUDIT_DATE, ENTERED_BY, 'UPDATE');

ELSIF deleting then
INSERT INTO PROPERTY_AUDIT
(AUDIT_DATE, ENTERED_BY, OPERATION)
VALUES
(AUDIT_DATE, ENTERED_BY, 'DELETE');


end if;
end TRG_PROP_AUDIT;

I'm getting this error:
Error(3,1): PL/SQL: SQL Statement ignored
Error(6,22): PL/SQL: ORA-00984: column not allowed here
Error(9,1): PL/SQL: SQL Statement ignored
Error(12,22): PL/SQL: ORA-00984: column not allowed here
Error(15,1): PL/SQL: SQL Statement ignored


What am I doing wrong? I've researched this extensively on the web, and supposedly you are not allowed to enter the column names in the [VALUES] expression. So how am I supposed to put that information in the appropriate columns? I'm so confused right now. I would appreciate any help...
  • 1. Re: Table Level Audit Trigger
    evrm Pro
    Currently Being Moderated
    Hi,

    I am not sure this is the right forum for your message.
    Next time post it in the [url https://forums.oracle.com/forums/forum.jspa?forumID=75]SQL and PL/SQL forum

    But anyway you can't use column name directly in a trigger.
    You need to use :new or :old as a prefix like so:
    CREATE OR REPLACE 
    trigger TRG_PROP_AUDIT
    
    AFTER insert or update or delete on PROP_BOOK_SI
    for each row
    
    begin
    IF INSERTING THEN
    INSERT INTO PROPERTY_AUDIT
    (AUDIT_DATE, ENTERED_BY, OPERATION)
    VALUES 
    (:new.AUDIT_DATE, :new.ENTERED_BY, 'INSERT');
    
    ELSIF updating then
    INSERT INTO PROPERTY_AUDIT
    (AUDIT_DATE, ENTERED_BY, OPERATION)
    VALUES 
    (:new.AUDIT_DATE, :new.ENTERED_BY, 'UPDATE');
    
    ELSIF deleting then 
    INSERT INTO PROPERTY_AUDIT
    (AUDIT_DATE, ENTERED_BY, OPERATION)
    VALUES 
    (:old.AUDIT_DATE, :old.ENTERED_BY, 'DELETE');
    end if;
    end TRG_PROP_AUDIT;
    regards,
    Erik-jan
  • 2. Re: Table Level Audit Trigger
    972703 Newbie
    Currently Being Moderated
    That worked! Thank you very much!

    How do I get the [app_user] data into the audit table? Thanks for your help!
  • 3. Re: Table Level Audit Trigger
    evrm Pro
    Currently Being Moderated
    Hi,

    >
    How do I get the [app_user] data into the audit table? Thanks for your help!
    >

    In the trigger code you can use the v function to get the value of APP_USER.
    v('APP_USER')
    regards,
    Erik-jan
  • 4. Re: Table Level Audit Trigger
    972703 Newbie
    Currently Being Moderated
    I think I tried that, but it came back with some error about [not being allowed to use virtual columns]. Ever hear of that error?
  • 5. Re: Table Level Audit Trigger
    chrdei Newbie
    Currently Being Moderated
    Using the v('APP_USER') works. I am using this in triggers as well.
    Do not forget to fallback to the database user if the APP_USER is empty! Maybe you will insert data from time to time in the corresponding tables without a valid APEX session.
    nvl( v('APP_USER'), user );
    Edited by: chrdei on 11.12.2012 09:40
  • 6. Re: Table Level Audit Trigger
    972703 Newbie
    Currently Being Moderated
    Thanks a lot. Works like a charm....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points