Re: quick question about after statement triggers

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:4353f2c3$0$4303$626a54ce_at_news.free.fr...
>
> "Jack Addington" <jaddington_at_shaw.ca> a écrit dans le message de news:
> pZR4f.226350$tl2.108107_at_pd7tw3no...
> | "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> | news:o1q7l1d7p0cam42dug88r5hchdb1ii33lq_at_4ax.com...
> | > On Mon, 17 Oct 2005 17:54:41 GMT, "Jack Addington"
> | > <jaddington_at_shaw.ca> wrote:
> | >
> | >>If I have an after statement trigger defined as
> | >>
> | >>cREATE OR REPLACE TRIGGER some_table_aius
> | >>after INSERT or update
> | >>on some_table
> | >>declare
> | >>
> | >>begin
> | >>
> | >> do_some_process( xxx );
> | >>
> | >>end;
> | >>
> | >>and then I do:
> | >>
> | >>insert into some_Table(...)
> | >>insert into some_table (...)
> | >>update some_table set xxx where yyy
> | >>
> | >>commit;
> | >>
> | >>will the do_some_process function fire twice, once for updates and
> once
> | >>for
> | >>inserts or just once?
> | >>
> | >>thx
> | >>
> | >>jack
> | >>
> | >
> | > You have three distinct statements, so the trigger will fire 3 times.
> | >
> | > --
> | > Sybrand Bakker, Senior Oracle DBA
> |
> | This is a statement trigger, not a row trigger.
> |
> |
>
> This is exactly what Sybrand said you: 3 statements
> (insert+insert+update) -> 3 executions.
> If it was row level trigger there'ld be n+p+q executions (n rows inserted
> + p rows inserted + q rows updated).
>
> Regards
> Michel Cadot
>

hmmm.. so just to clarify, the statement triggers are firing for each statement, not each type of statement. I had this embarassing illusion/idea they were statement triggers were more related to the transaction/commit. So the below is wrong:

Insert into table y

• trigger before insert y statement
• trigger before insert y row
• trigger after insert y row

Insert into table y

• trigger before insert y row
• trigger after insert y row

Insert into table y

• trigger before insert y row
• trigger after insert y row
• trigger after insert y statement

commit;

In essence then, the only way you get more row triggers that statement triggers is if the single dml statement affects multiple row. Does the same happen with a forall bulk update or insert?

thx
Jack Received on Thu Oct 20 2005 - 15:45:24 CDT