Basic Oracle Trigger Audit Table

Oracle Audit table using trigger

The reason why :old.v_tab_col_nt(r) and :new.v_tab_col_nt(r) are not working is because :old and :new are references ONLY for the old and new (duh!...) values in the affected columns in the table, and not for your user defined type declared inside the trigger.

The values you are actually looking for are: :old.<name of column1>, or :new.<name of column1>.

So for your requirement you trigger must look something like this:

create or replace trigger my_trigger
after insert or update on temp12
for each row
referencing old as old new as new
begin

    /*When-Insert block. 1 record for each column in audit*/
    if (INSERTING) then -- 
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
         .
         . --(same for every column)
         .
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Insert block*/

    /*When-Update block. A new record in audit just for the updated column(s) */
    if (UPDATING ( '<name of column1>' )) then --col 1
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column2>' )) then --col 2
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column3>' )) then --col 3
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
    end if;
     . 
     . --(same for every column)
     .
    if (UPDATING ( '<name of column16>' )) then --col 16
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Update block*/

end;

And now about tracking the logged user into your application there are two considerations:

1. If your whole application implemented all its users at a DB Level,
   this is a ORACLE user for each logged individual, if this is so, the
   reserved word "USER" within every PL/SQL block or DDL executed, will
   retrieve that user name every time.

2. In the other hand if your application decided to manage users on
   their own, by creating a custom user control, then that value must
   be passed either by a parameter (in case of a named PL/SQL block) or
   including a (NOT NULL) column in every table (at least every table
   you want to monitor for user activity), so that every INSERT statement is
   forced to send that info, and you can read it using :new.<name of user monitor column> inside
   your triggers code, or you can use something like

IF (:new.< name of user monitor column > is null) then raise_application_error(-20001, 'User must be specified').
END IF;

May the force be with you.

create audit trail using oracle trigger

You cannot "loop" through the OLD and NEW values, you have to reference each one explicitly i.e.

INSERT INTO audit_table
    (
    ACTION,
    TABLE_ID,
    OLD_VALUE,
    NEW_VALUE,
    USERNAME,
    TS_CREATED
    ) VALUES (
    var_action,
    :OLD.ID,
    :OLD.column1VALUE,
    :new.COLUMN1VALUE,
    ociidentifier,
    systimestamp
    );
INSERT INTO audit_table
    (
    ACTION,
    TABLE_ID,
    OLD_VALUE,
    NEW_VALUE,
    USERNAME,
    TS_CREATED
    ) VALUES (
    var_action,
    :OLD.ID,
    :OLD.column2VALUE,
    :new.COLUMN2VALUE,
    ociidentifier,
    systimestamp
    );
... etc.

You may be able to simplify the code somewhat by writing a procedure to wrap the INSERT statement and calling that many times instead of the INSERT statement itself.

PLSQL Trigger To Record Update To Audit Table If Column_X Updates

Thank you Gurwinder Singh, I think this is what you were telling me to try. This works considering my data is always going to/from a NULL status, though there probably is a prettier way to write it.

CREATE OR REPLACE TRIGGER audit_table
AFTER UPDATE ON table_1
FOR EACH ROW
WHEN ((new.column_x IS NULL AND old.column_x IS NOT NULL) OR (new.column_x IS NOT NULL AND old.column_x IS NULL))
BEGIN
INSERT INTO audit_table (
    column_1,
    old_column_x,
    new_column_x,
    auditdtm
    )
VALUES (
    :old.column_1,
    :old.column_x,
    :new.column_x,
    SYSDATE); 
END

how to use trigger in a way to have all updated columns of base table to multiple rows of audit table?

As mentioned over comments, There's no option to fetch OLD and NEW values specific to each column name dynamically. All you could do is to write explicit inserts for all the columns with hardcoded column names.

 ...
 ...

BEGIN

INSERT INTO frame_audit (Id ,Column_Name , Old_Value , New_Value)
SELECT  audit_seq.nextval,'bowler_id',old.bowler_id,new.bowler_id 
FROM DUAL WHERE 
 (    old.bowler_id <> new.bowler_id OR
      (old.bowler_id is NULL     and new.bowler_id is NOT NULL) OR
      (old.bowler_id is NOT NULL and new.bowler_id is NULL   ) 
 );

INSERT INTO frame_audit (Id ,Column_Name , Old_Value , New_Value)
SELECT  audit_seq.nextval,'game_id',old.game_id,new.game_id 
FROM DUAL WHERE 
 (    old.game_id <> new.game_id OR
      (old.game_id is NULL     and new.game_id is NOT NULL) OR
      (old.game_id is NOT NULL and new.game_id is NULL   ) 
 );

--similar inserts for all other columns

 ...

END;

Here I'm assuming you have a sequence created to generate ids for Audit table.

You may simplify the lengthy NULL check expression using NVL or COALESCE, but you should use it as per the data type of each column. If the column is defined as not null, a simple <> check would be sufficient. For DATE/TIMESTAMP related columns, you would need to use TO_CHAR if you want to store it in a desired format.

auditing 50 columns using oracle trigger

Your immediate problem with the else always being called is because you're using your index variable r directly, rather than looking up the relevant column name:

for r in v_tab_col_nt.first..v_tab_col_nt.last
loop
    if updating(v_tab_col_nt(r)) then
        insert into data_table values(1,'i am updating '||v_tab_col_nt(r));
    else
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end if;
end loop;

You're also only showing an id column in your table creation, so when r is 2, it will always say it's inserting name, never updating. More importantly, if you did have a name column and were only updating that for a given id, this code would show the id as inserting when it hadn't changed. You need to split the insert/update into separate blocks:

if updating then
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        if updating(v_tab_col_nt(r)) then
            insert into data_table values(1,'i am updating '||v_tab_col_nt(r));
        end if;
    end loop;
else /* inserting */
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end loop;
end if;

This will still say it's inserting name even if the column doesn't exist, but I assume that's a mistake, and I guess you'd be trying to populate the list of names from user_tab_columns anyway if you really want to try to make it dynamic.

---

I agree with (at least some of) the others that you'd probably be better off with an audit table that takes a copy of the whole row, rather than individual columns. Your objection seems to be the complication of individually listing which columns changed. You could still get this information, with a bit of work, by unpivoting the audit table when you need column-by-column data. For example:

create table temp12(id number, col1 number, col2 number, col3 number);
create table temp12_audit(id number, col1 number, col2 number, col3 number,
    action char(1), when timestamp);

create or replace trigger temp12_trig
before update or insert on temp12
for each row
declare
    l_action char(1);
begin
    if inserting then
        l_action := 'I';
    else
        l_action := 'U';
    end if;

    insert into temp12_audit(id, col1, col2, col3, action, when)
    values (:new.id, :new.col1, :new.col2, :new.col3, l_action, systimestamp);
end;
/

insert into temp12(id, col1, col2, col3) values (123, 1, 2, 3);
insert into temp12(id, col1, col2, col3) values (456, 4, 5, 6);
update temp12 set col1 = 9, col2 = 8 where id = 123;
update temp12 set col1 = 7, col3 = 9 where id = 456;
update temp12 set col3 = 7 where id = 123;

select * from temp12_audit order by when;

        ID       COL1       COL2       COL3 A WHEN
---------- ---------- ---------- ---------- - -------------------------
       123          1          2          3 I 29/06/2012 15:07:47.349
       456          4          5          6 I 29/06/2012 15:07:47.357
       123          9          8          3 U 29/06/2012 15:07:47.366
       456          7          5          9 U 29/06/2012 15:07:47.369
       123          9          8          7 U 29/06/2012 15:07:47.371

So you have one audit row for each action taken, two inserts and three updates. But you want to see separate data for each column that changed.

select distinct id, when,
    case
        when action = 'I' then 'Record inserted'
        when prev_value is null and value is not null
            then col || ' set to ' || value
        when prev_value is not null and value is null
            then col || ' set to null'
        else col || ' changed from ' || prev_value || ' to ' || value
    end as change
from (
    select *
    from (
        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)
order by when, id;

        ID WHEN                      CHANGE
---------- ------------------------- -------------------------
       123 29/06/2012 15:07:47.349   Record inserted
       456 29/06/2012 15:07:47.357   Record inserted
       123 29/06/2012 15:07:47.366   col1 changed from 1 to 9
       123 29/06/2012 15:07:47.366   col2 changed from 2 to 8
       456 29/06/2012 15:07:47.369   col1 changed from 4 to 7
       456 29/06/2012 15:07:47.369   col3 changed from 6 to 9
       123 29/06/2012 15:07:47.371   col3 changed from 3 to 7

The five audit records have turned into seven updates; the three update statements show the five columns modified. If you'll be using this a lot, you might consider making that into a view.

So lets break that down just a little bit. The core is this inner select, which uses lag() to get the previous value of the row, from the previous audit record for that id:

        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit

That gives us a temporary view which has all the audit tables columns plus the lag column which is then used for the unpivot() operation, which you can use as you've tagged the question as 11g:

    select *
    from (
        ...
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )

Now we have a temporary view which has id, action, when, col, value, prev_value columns; in this case as I only have three columns, that has three times the number of rows in the audit table. Finally the outer select filters that view to only include the rows where the value has changed, i.e. where value != prev_value (allowing for nulls).

select
    ...
from (
    ...
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)

I'm using case to just print something, but of course you can do whatever you want with the data. The distinct is needed because the insert entries in the audit table are also converted to three rows in the unpivoted view, and I'm showing the same text for all three from my first case clause.

Auditing in Oracle

You don't need write your own triggers.

Oracle ships with flexible and fine grained audit trail services. Have a look at this document (9i) as a starting point.
(Edit: Here's a link for 10g and 11g versions of the same document.)

You can audit so much that it can be like drinking from the firehose - and that can hurt the server performance at some point, or could leave you with so much audit information that you won't be able to extract meaningful information from it quickly, and/or you could end up eating up lots of disk space. Spend some time thinking about how much audit information you really need, and how long you might need to keep it around. To do so might require starting with a basic configuration, and then tailoring it down after you're able to get a sample of the kind of volume of audit trail data you're actually collecting.

Auditing from a trigger in oracle

As per this link Use v('APP_USER') as default value for column in Oracle Apex There are other options than V('APP_USER'). Since Apex 5, the APP_USER is stored in the sys_context and that is a lot more performant than the V() function. It is available as SYS_CONTEXT('APEX$SESSION','APP_USER').

Please try the below and see if your issue is getting resolved.

        TRIGGER trg_tableA before insert or update 
                on tableA REFERENCING OLD AS OLD NEW AS NEW 
                FOR EACH ROW 
                
                begin 

                :new.insert_date:=sysdate;
                :new.inserted_by:= nvl(sys_context('APEX$SESSION','APP_USER'),user);

                :new.modified_date:=sysdate;
                :new.modified_by:= nvl(sys_context('APEX$SESSION','APP_USER'),user);

        end trg_tableA;

Audit trigger inserting multiple rows into audit table

Your query is fatally flawed, because it does not take into account multiple (or zero) rows being inserted or updated.

Your trigger should probably look something like this:

CREATE TRIGGER dbo.SOP10100_TRDISAMT
ON dbo.SOP10100
AFTER INSERT, UPDATE
AS 

INSERT INTO SOP10100_TRDISAMT_AUDIT
    (soptype, sopnumbe, MSTRNUMB, DOCID, SUBTOTAL, MISCAMT, TRDISAMT,
    TRDISAMT_B4, FRTAMNT, TAXAMNT, DOCAMNT, USERNAME, TheTime)
SELECT i.soptype, i.sopnumbe, i.mstrnumb, i.docid, i.subtotal, i.MISCAMNT, i.TRDISAMT,
    i.TRDISAMT, i.FRTAMNT, i.TAXAMNT, i.DOCAMNT, SUSER_SNAME(), GETDATE()
FROM inserted i
WHERE i.TRDISAMT <> 0;

GO

• If you want to exclude rows where the values have not been changed at all by the UPDATE statement, you need to add

EXCEPT
SELECT d.soptype, d.sopnumbe.....
FROM deleted d

• I note that char is an unusual data type, and should only be used where the value is fixed at that length, otherwise you will get trailing spaces.

---

Related Topics