Re: trigger advice please

Hi Glen,

This should do it for you..

create or replace trigger emp_upd
after update on emp
for each row
begin

insert into audit_emp(empno,
old_sal,new_sal,username,date_changed)
values(:new.empno,:old.sal,:new.sal,
user,sysdate);
end;
/

where 'audit_emp' is your audit table,user is the logged in user.

Good luck,

Steve

In article <94ochv$k6u$1_at_nnrp1.deja.com>,   Glen A. Stromquist <glenstr_at_my-deja.com> wrote:
> Ok, I have a table that has a few columns in it that I want to monitor
> for changes. I created a table for a trigger to insert rows in when
> these columns are updated, in this table I have two columns for each
> one column that I want tracked in the other table, one of them for the
> old value, one for the new.
>
> Following is the trigger I wrote to accomplish this:
>
> create or replace trigger tr_aud_table
> before update on schema.table_name
> for each row
> begin
> insert into schema.audit_changes
> (ref_number,
> change_date,
> user,
> old_col1,
> old_col2,
> old_col3)
> VALUES
> (:OLD.ref_NUMBER,
> SYSDATE,
> USER,
> OLD:col1,
> OLD:col2,
> OLD:col3)
> UPDATE schema.AUDIT_CHANGES
> SET
> new_col1 = NEW:col1,
> new_col2 = NEW:col2,
> new_col3 = NEW:col3
> WHERE ref_number = OLD:ref_number
> END;
>
> will this work?
> does "USER" pick up the user changing the table?
>
> thanks in advance
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 25 2001 - 07:23:46 CST