the same way you capture the old_value and new_value. Just modify the code generator to pass it along when it passes them.

don't see what column id has to do with it.

take the query:

select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/



modify it to something like:

select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ', :new.ROWID );'
from user_tab_columns where table_name = upper('&1')
/


then make the audit_pkg take the rowid as the last parameter for each function:

create or replace package audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,
l_rowid in varchar2 );


and record the rowid.

You know what -- if you don't know what the code is doing or how it does it (and cannot figure it out) -- it might be best if you didn't use it.


You did not follow the instructions above (else you would have :new.rowid not 'rowid' in the insert statement).  I don't know what else you did wrong.  Just to put it to bed, here you go.  Just CUT AND PASTE this.  Try to understand what it does and how it does it.

create table audit_tbl
(   timestamp    date,
    who          varchar2(30),
    tname        varchar2(30),
    cname        varchar2(30),
    old          varchar2(2000),
    new          varchar2(2000),
    rid          rowid
)
/

create or replace package audit_pkg
as
    procedure check_val( l_tname in varchar2,
                         l_cname in varchar2,
                         l_new in varchar2,
                         l_old in varchar2,
                         l_rowid in varchar2 );

    procedure check_val( l_tname in varchar2,
                         l_cname in varchar2,
                         l_new in date,
                         l_old in date,
                         l_rowid in varchar2 );

    procedure check_val( l_tname in varchar2,
                         l_cname in varchar2,
                         l_new in number,
                         l_old in number,
                         l_rowid in varchar2 );
end;
/


create or replace package body audit_pkg
as

procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
                     l_new in varchar2,
                     l_old in varchar2,
                     l_rowid in varchar2  )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
                             l_old, l_new, l_rowid );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in date, l_old in date,
                     l_rowid in varchar2  )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ), l_rowid );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in number, l_old in number,
                     l_rowid in varchar2  )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
                                 l_old, l_new, l_rowid );
    end if;
end;

end audit_pkg;
/


And the script to generate the trigger would be:

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||
          ''', ' || ':new.' || column_name || ', :old.' ||
             column_name || ', :new.rowid);'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp


The test I used is:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dept as select * from scott.dept where rownum = 1;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> @test dept
create or replace trigger aud#dept
after update on dept
for each row
begin
    audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO, :new.rowid);
    audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME, :new.rowid);
    audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC, :new.rowid);
end;
/

Trigger created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update dept set deptno = deptno+1;

1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update dept set dname = lower(dname);

1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update dept set loc = initcap(loc), dname = upper(dname);

1 row updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> @printtbl8 'select * from audit_tbl';
TIMESTAMP                     : 26-feb-2002 12:29:11
WHO                           : OPS$TKYTE
TNAME                         : DEPT
CNAME                         : DEPTNO
OLD                           : 10
NEW                           : 11
RID                           : AAAGl7AABAAAFSaAAA
-----------------
TIMESTAMP                     : 26-feb-2002 12:29:11
WHO                           : OPS$TKYTE
TNAME                         : DEPT
CNAME                         : DEPTNO
OLD                           : 20
NEW                           : 21
RID                           : AAAGl7AABAAAFSaAAB
-----------------
TIMESTAMP                     : 26-feb-2002 12:29:11
WHO                           : OPS$TKYTE
TNAME                         : DEPT
CNAME                         : DEPTNO
OLD                           : 30
NEW                           : 31
RID                           : AAAGl7AABAAAFSaAAC
....
-----------------
TIMESTAMP                     : 26-feb-2002 12:32:56
WHO                           : OPS$TKYTE
TNAME                         : DEPT
CNAME                         : LOC
OLD                           : NEW YORK
NEW                           : New York
RID                           : AAAGl9AABAAAFSaAAA
-----------------

PL/SQL procedure successfully completed. 

re-read the original answer please. focus on that bit that says:

And the script to generate the trigger would be:


If you read slower, it would let you code faster ultimately.

I have no idea where you got the idea the trigger looked like that. I wrote a script to CREATE a trigger, there is no code that even remotely resembles what you posted here on this page.

even i am not very clear about why inside a trigger, dynamic does not work with
:new and :old,

because they do???

couple of things here.

a) USE BIND VARIABLES
b) USE BIND VARIABLES
...
z) USE BIND VARIABLES

Oh my gosh - and I almost forgot - USE BIND VARIABLES.

ops$tkyte%ORA11GR2> create table test(id number);

Table created.

ops$tkyte%ORA11GR2> create table log(id number);

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace trigger trg1 after insert on test
  2  for each row
  3  declare
  4          v_str varchar2(100);
  5  begin
  6          v_str:='insert into log(id) values( :x )';
  7          execute immediate v_str USING :new.id;
  8  end;
  9  /

Trigger created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into test (id) values( 42 );

1 row created.

ops$tkyte%ORA11GR2> select * from log;

        ID
----------
        42

Suggestions:

a) use bind variables
b) you do not need dynamic sql from where I'm sitting, I don't see any need for it
c) :new and :old can be referenced just like any other variable can be in a trigger, as demonstrated
d) get a better grasp of PL/SQL before you set off developing real life production code - I hope you have a really good mentor to review all you develop for a while. The lack of bind variables here really scares me to DEATH.

I could not set the :NEW values in after insert trigger before. it works in
before insert though.

Sam - I said "referenced". That means to look at them.


Think about why you cannot set the :new values AFTER insert. Think about it. And check out the documentation - it is not a hidden fact.

sam,

think about it.

You are in an AFTER trigger. If you are in an AFTER trigger - has the insert

a) happened?
b) not happened?


If the insert has in fact happened - it is done already - what meaning would changing :new have? The row is *already* inserted.


from that chapter:
http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHHEJCD

Because the trigger uses the BEFORE keyword, it can access the new values before they go into the table, and can change the values if there is an easily-corrected error by assigning to :NEW.column_name.


http://docs.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHIEFAE

Only the BEFORE EACH ROW section can change the value of :NEW.





It seems you can do things in a before insert that you cannot do in an after insert Sam, you can modify values.

Does that mean "you can accomplish same thing with before insert instead of after insert most of the time."??? I would say:

NO

You use a before for each row trigger IF and ONLY IF you need to modify the :new values.

You use an after for each row trigger in ALL CASES where you do not.



If you wrote a "before insert for each row" trigger for auditing you've done it completely wrong. Not just for "it is my opinion" reasons - but because someone else could come along and put a trigger on the table that modifies the :new values AFTER your trigger fires. You would end up auditing the wrong information. You have to wait until the AFTER trigger - when the data stabilizes - when the data is known - to audit.

ONLY code that needs to modify :new values should be in before for each row
ALL other code needs to be in an after for each row (data validations, everything)



Yet another reason I hate triggers. Not many people know how they actually work.