Friday, November 27, 2009

Oracle difference between before and after triggers

Try to compile 1. and 2. to see the difference:

1.
create or replace trigger upd_trigger
before update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
:new.salary := 1000;
end if;
end;
/

2.

create or replace trigger upd_trigger
after update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
:new.salary := 1000;
end if;
end;
/

However when you compile 3. and 4. you
will see no difference. And both these triggers
will have the same effect, i.e. they won't allow
the corresponding updates to take place.

Also, in 3. and 4. even if a single row has
its new salary < old salary, none of
the updates will be executed.


3.
create or replace trigger upd_trigger
before update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
raise_application_error(-20000,'not allowed');
end if;
end;
/

4.

create or replace trigger upd_trigger
after update of salary on newemp
for each row
begin
if :new.salary < :old.salary then
raise_application_error(-20000,'not allowed');
end if;
end;
/

No comments:

Blog Archive