trigger advice please

From: Glen A. Stromquist <glenstr_at_my-deja.com>
Date: Thu, 25 Jan 2001 05:11:30 GMT
Message-ID: <94ochv$k6u$1@nnrp1.deja.com>

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/ Received on Wed Jan 24 2001 - 23:11:30 CST