Re: AFTER ROW Trigger Vs BEFORE ROW

Geoff Bruce wrote:
> It seems to me that whatever I can do in AFTER ROW trigger, I can do it in
> BEFORE ROW.
>
> Are there any functionalities that can only be achieved by AFTER ROW trigger
> but not BEFORE ROW?

Actions based on the NEW values can only be "safely" performed in an AFTER ROW trigger, because in a BEFORE ROW trigger they are not yet stable. A simple (if rather silly) example:

create trigger emp_trg1
before insert on emp
for each row
begin
  if :new.sal > 10000 then
    insert into high_paid_emps (empno) values (:new.empno);   end if;
end;
/

create trigger emp_trg2
before insert on emp
for each row
begin
  if :new.deptno = 10 then
    :new.sal := 2000;
  end if;
end;
/

Now consider the following insert:

insert into emp (empno, deptno, sal) values (1234, 10, 12000);

If emp_trg1 fires before emp_trg2 then a row will be (wrongly) created in high_paid_emps (remember, the order of trigger firing is indeterminate). Received on Mon Jul 11 2005 - 09:47:52 CDT