triggers

Hafiz Muhammad Mushtaq · #1 (**permalink**) May 11th, 2007, 01:10 AM

what is the difference b/w statement level and row level triggers.i have a confusion in understanding the difference, because in my openion, e,g if we have made a statment level trigger in such a way that it should be fired when we insert a row in a table, n i think that, if we insert, for example 10 rows in the table, then the trigger will be fired 10 times, but our teacher told that the row level trigger is fired only once.In the case of a row level trigger, our teacher told that it is fired as many times as there are rows in a table, n if we are inserting 10 rows in a table, then it will be fired 10 times, as was the satement level trigger, then what is the diference b/w them?.please give examples

m_soni21 · #2 (**permalink**) June 11th, 2007, 06:19 AM

Hii,
got some document on triggers, hope this explains ur query...

A row level trigger is defined using the clause for each row. If this clause is not given, the trigger is assumed to be a statement trigger. A row trigger executes once for each row after (before) the event. In contrast, a
statement trigger is executed once after (before) the event, independent of how many rows are affected by the event. For example, a row trigger with the event specification after update is executed once for each row affected by the update. Thus, if the update affects 20 tuples, the trigger is executed 20 times, for each row at a time. In contrast, a statement trigger is only executed once.

ashu_from_india · #3 (**permalink**) June 29th, 2007, 08:29 AM

does it mean tht Statement Triggers are only for Update & Delete statements???

programmed · #4 (**permalink**) July 7th, 2007, 03:17 AM

Not necessarily that statement triggers are for update and delete statements, but as explained above, statement triggers i think are invoked once for the entire sequence of statements bundled together while the row level are fired each time any turple is affected.

rajessnit@gmail.com · #5 (**permalink**) October 18th, 2007, 09:17 AM

yes,the statement level trigger fired when the seuqence of mentioned operations(in block) is completed e.g within a block u are inserting,updating ,deleting etc records, for all operations single statement level trigger will be fired,but in case of row level trigger,whenever a row operation is done,updated or inserted
etc,it will be fired by database everytime.

With thanks
rajesh,
India

akhil333@gmail.com · #6 (**permalink**) April 9th, 2009, 05:25 PM

hi..!

if u issue an insert commondvlike

Code:

insert into emp1(fname,lname)
select fname,lname from emp2;

the select statement will return all the rows from table emp2, that will be inserted in table emp1. if u have an statement level trigger, it will be fired only ones irrespective of no. of rows being inserted.
if the trigger is on row level, it will be fired for each row being inserted to emp1.

the default is statement level. if u wanna a trigger on row level, u have to tell it explicitly as follows..

Code:

create trigger MY_TRG
after insert
on emp1
FOR EACH ROW

if u ommit FOR EACH ROW it would be an statement level trigger.

-- AKHIL

Need to download code?

Navigation