Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> trigger question
How does one differentiate between updated and old values for triggers? For example, suppose I have the following tables:
create table foo (ts date);
create table foo_upd (upd_ts date, upd_old_ts date);
And I want to create a trigger that makes a copy of the new data inserted into foo.
create trigger foo_1 after insert on foo
referencing new as new for each row
begin
insert into foo_upd(upd_ts, upd_old_ts) values (sysdate, new.ts);
end;
Now for this case, new.ts is redundant to ts, but what about updates? What if I need the old and new values in the body of the trigger? For example:
create trigger foo_2 after update on foo
referencing new as new for each row
begin
insert into foo_upd(upd_ts, upd_old_ts) values (old.ts, new.ts);
end;
Both of these triggers compile with errors ("Identifier NEW.TS (or OLD.TS) must be declared") and fail re-validation at run-time.
What am I missing?
Ben Received on Mon Feb 19 2001 - 08:41:59 CST