René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

:new and :old in Oracle triggers

create sequence seq_log;

create table trigger_log (
  id  number primary key,
  txt varchar2(40)
);

create trigger trg_log before insert on trigger_log
  for each row
begin
  select seq_log.nextval into :new.id from dual;
end;
/
create table trigger_tbl (
  a varchar2(20)
);
create trigger trg_before_insert before insert on trigger_tbl
  for each row
begin
  insert into trigger_log (txt) values ('[I] :old.a=' || :old.a || ', :new.a='||:new.a);
end;
/


select * from trigger_log;
create trigger trg_before_update before update on trigger_tbl
  for each row
begin
  insert into trigger_log (txt) values ('[U] :old.a=' || :old.a || ', :new.a='||:new.a);
end;
/

create trigger trg_before_delete before delete on trigger_tbl
  for each row
begin
  insert into trigger_log (txt) values ('[D] :old.a=' || :old.a || ', :new.a='||:new.a);
end;
/
insert into trigger_tbl values ('hello');

select * from trigger_log;
1 [I] :old.a=, :new.a=hello
update trigger_tbl set a='salut';

select * from trigger_log;
1 [I] :old.a=, :new.a=hello
2 [U] :old.a=hello, :new.a=salut
delete from trigger_tbl where a='salut';

select * from trigger_log;
1 [I] :old.a=, :new.a=hello
2 [U] :old.a=hello, :new.a=salut
3 [D] :old.a=salut, :new.a=