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