Re: triggers

Charles Hooper wrote:
> Steve Howard wrote:
> > schw wrote:
> > > hi all,
> > >
> > > can one trigger be fired twice simultaneously ?

<snip>

> > > regards,
> > > schw
> >
> > In a given session for a given operation it only fires once.

<snip>

> > HTH,
> >
> > Steve
>
> Isn't there a possibility that a BEFORE trigger may be executed more
> than once, while an AFTER trigger will only be executed once? This
> would not be a problem for a normal transaction, but would be a problem
> for a transaction that is writing to an operating system file. It
> seems that Tom Kyte mentioned something about this in his "Expert
> Oracle Database Architecture" book, where there may be times when a
> transaction will be automatically rolled back and then reapplied -
> obviously Oracle will not be able to roll back the operating system
> file. I can't seem to find the pages in the book that mention that
> type of event.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Charles,

I couldn't duplicate it, but I would be interested to see what it is if you can (simultaneous firing, not re-firing). Even if it re-fires, it could cause havoc with an OS file as you and Ed Stevens noted earlier.

SQL> create table t1116(c number);

Table created.

SQL> create or replace trigger foo_bir
  2 before insert on t1116
  3 for each row
  4 begin
  5 :new.c := :new.c + 1;
  6 dbms_output.put_line('i fired, and the new value is ' || :new.c);   7 end;
  8 /

Trigger created.

SQL> set serveroutput on
SQL> insert into t1116 values(1);
i fired, and the new value is 2

1 row created.

SQL> rollback;

Rollback complete.

SQL> Let me know if you get a test case.

Thanks,

Steve Received on Thu Nov 16 2006 - 14:47:25 CST