Re: triggers

Steve Howard wrote:
> Charles Hooper wrote:
> 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

Looking through the notes that I typed up when reading the "Expert Oracle Database Architecture" book, I found these paraphrased notes: "If a row to be changed in an update query is changed by another session before the row is touched by the first session, the first session's transaction will be silently rolled back, the update will be started again in SELECT FOR UPDATE mode, which will cause all rows to be locked, and the update will start again. This could cause serious performance hits and cause triggers (that send emails, write to files, etc.) to fire a second time. Use the DBMS_JOB or the Scheduler facility in 10g to avoid this problem when sending emails."

It looks like this is discussed on pages 249-253 of the book.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Nov 16 2006 - 16:19:11 CST