Wrox Programmer Forums
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 11th, 2007, 12:10 AM
Registered User
Join Date: Apr 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default triggers

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

Old June 11th, 2007, 05:19 AM
Authorized User
Join Date: Jan 2006
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts

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.
Old June 29th, 2007, 07:29 AM
Friend of Wrox
Join Date: Oct 2004
Posts: 224
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to ashu_from_india Send a message via Yahoo to ashu_from_india

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

Old July 7th, 2007, 02:17 AM
Authorized User
Join Date: Oct 2003
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts

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.

Old October 18th, 2007, 08:17 AM
Authorized User
Join Date: Oct 2006
Posts: 25
Thanks: 0
Thanked 0 Times in 0 Posts

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
Old April 9th, 2009, 04:25 PM
Authorized User
Join Date: Mar 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts


if u issue an insert commondvlike
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..
create trigger MY_TRG
after insert
on emp1
if u ommit FOR EACH ROW it would be an statement level trigger.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Triggers? odezzie Classic ASP Databases 1 March 28th, 2007 06:09 PM
Triggers mrookey SQL Server 2000 2 October 24th, 2006 07:11 AM
Triggers? prabodh_mishra Oracle 2 March 30th, 2006 05:51 AM
Triggers shahchi1 SQL Server 2000 1 November 1st, 2004 06:28 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.