Hi,

What is difference between "for each row" and "for each statement" in trigger action after insert.

My system: db2 v8.1fp6 on win2000

Thanks,
Grofaty

If you issue a multi-row INSERT statement, the 'each statement' trigger is invoked only once whereas a "each row" trigger is invoked for every row ..

Eg of a multi-row INSERT
insert into tab1 values(2,2),(3,3)

Hi,

Thanks. What about if SQL is written like this:
1. INSERT INTO schema.tab1 SELECT * FROM schema.tab2
or like this:
2. UPDATE schema.tab1 WHERE value = value (I would like to involke trigger on whole table)

My question: how many times is trigger fired out if ROW or STATEMENT is used in trigger for query 1 and query 2.

Thanks,
Grofaty

Hi,

I have noticed that 'for statement' works 100-times faster then 'for row'.
I am still interested in above questions.

Thanks,
Grofaty

Grofaty,
For statement 1, a trigger with STATEMENT will execute only once, while a trigger with ROW will execute SELECT count(*) FROM schema.tab2 times.

Similiarly for statement 2, a trigger with STATEMENT will execute only once, while one with ROW will execute select count(*) from schema.tab1 WHERE value = value

HTH

Andy

Hi,

arwinner: thanks a lot. 'With ROW' is 100-times slower. What is the benefit of using 'with ROW' option? When must be 'with ROW' used instread of 'with STATEMENT'.

Grofaty

There is no hard and fast rule ... it depends on the business requirement rather than technical ...

For eg, consider and UPDATE trigger ... If the business requirement is to log before image of each row along with the update timestamp, then you will have to use ROW .. But, if the requirement is to just log the time when a table is updated, then use the STATEMENT

Cheers
Sathyaram

Hi,

Thanks to you all.

Grofaty