When does a Trigger trigger?

Question

Post reply

When does a Trigger trigger?

Anusha Krishnamoorthy

SSC Rookie

Points: 26
More actions
December 3, 2007 at 2:43 pm

#377286

Hi,
In several articles about triggers, I read that an associated trigger is called whenever an Insert, Update or Delete occurs on a single row of a table. However, recently, I found out that that is not so, and that a trigger is actually executed after the entire batch completes. For instance, if I have the following statement that deletes 10 rows from the table (MyTable), then the corresponding Delete trigger for MyTable will be called for all of the 10 rows at once, and all these rows will be present in the 'deleted' virtual table.
DELETE FROM MyTable where delValue = 'D';
My earlier understanding was that the trigger will be fired once for each row, making 10 times in all. Unfortunately, I do not have access to SQL Server now, so I cannot verify this for myself. Please advise...
Thanks,
Anusha

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

your sources are right, it is a set based operation...only one execution for the all affected rows.

this is the advantage of Set Based Operations

if you update a table so that some flag='Y' in a 10 million row table, it's just one single operation, and it just happens to affect a lot of rows.

Otherwise, you'd suffer from the Modenism of RBAR...Row By Agonizing Row, which brings performance to a standstill when it's not necessary.

without fail, if you think that something needs to be handled on a row by row basis, if you think about it, you can do it as a set based operation instead.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 2

In several articles about triggers,

Triggers operate quite differently between RDBMS's... if you really want to know about triggers in SQL Server, read about them in Books Online instead of the Web. That'll give you the hot-straight skinny from MS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

The ISO SQL standards has, amoung others, these trigger options:

1. "Before", "After" and "Instead"

2. "for each statement" and "for each row"

3. firing order

SQL Server 2000 supports:

1. "After" and "Instead" but does not support "Before"

2. "for each statement" but does not support "for each row"

3. For Order, allows specification of "first" and "last"

Oracle supports:

1. "Before" and "After" on tables but "Instead" only for views.

2. "for each row" but Oracle's "for each statement" does not follow the ISO standard as it does not allow access to the rows being changed.

3. Order cannot be specified and is random.

Sybase and SQL Anywhere support:

1. "Before" and "After" but not "Instead"

2. "for each statement" and "for each row"

3. Full support for firing order specification.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 4

You can gain some very fine control over the "order" of what get's done by triggers... put all the code in the correct order in one trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anusha Krishnamoorthy SSC Rookie Points: 26 More actions · Answer 5

Thanks a lot for your help! I understand triggers much better now!

Regards,

Anusha