Trigger Order Of Execution?

Question

Post reply

Trigger Order Of Execution?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Books online doesn't say either way, from what I could see.

Try running this. The trigger gets executed once. All rows inserted are in the inserted table.

CREATE

TABLE Test (

name VARCHAR(200)

)

GO

CREATE TRIGGER trg_Test ON dbo.Test

AFTER INSERT

AS

DECLARE @rowCount INT

SELECT @rowCount =COUNT(*) FROM inserted

PRINT

'There are ' + CAST (@rowCount AS VARCHAR(3)) + ' rows in the inserted table'

GO

INSERT

INTO Test (name)

SELECT name FROM sysobjects

GO

DROP

TRIGGER trg_Test

GO

DROP

TABLE Test

GO

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Stamey SSCrazy Points: 2695 More actions · Answer 2

Thanks. Good learning experience.

I think I am still in good shape though, because of this:

CREATE TABLE Test (

[ID] Int Identity,

name VARCHAR(200),

Val VarChar(10)

)

GO

CREATE TRIGGER trg_Test ON dbo.Test

AFTER INSERT

AS

DECLARE @rowCount INT

SELECT @rowCount = Len(1) FROM inserted

Update T

Set Val = Len(I.name)

From Test T Inner Join Inserted I On T.ID = I.ID

PRINT 'There are ' + CAST (@rowCount AS VARCHAR(3)) + ' rows in the inserted table'

GO

INSERT INTO Test (name)

SELECT name FROM sysobjects

Curiously, it only states 1 row is inserted, but puts the length of each record's Name field data in correctly.

Query results:

(396 row(s) affected)

There are 1 rows in the inserted table

In the grand scheme I don't think I care how many times the trigger fires, as long as it does what it is supposed to to each record. Is there a reason I should care?

So, any ideas why, in my original trigger, having the various sections reordered would make things work or not work?

Thanks,

Chris

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It states 1 row inserted cause that's what you set @rowcount to...

SELECT @rowCount = Len(1) FROM inserted

Change that back to SELECT @rowCount = count(*) FROM inserted and the print will read 'There are 396 rows in the inserted table'

The trigger only fires once, otherwise you would have had the print line printed out 396 times, and it has all the affected records in it.

As to the reason for the trigger problems, honestly I'm not sure, without having the table structure and the data.

The easiest (only) way to debug triggers effectivly is to scatter prints and selects through them. Print out the values of your variables, do selects of what you're about to update, then run the inserts in query analyser and look at the results.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Stamey SSCrazy Points: 2695 More actions · Answer 4

Dang, I always miss something in a query....

Comes from juggling as many projects as I am, I guess.

I will mess around with the trigger some more to see if I can determine why the order of the code in the trigger makes a difference. Thanks for your time to help out with this.

On another note, the other trigger they were complaining about actually is working, and it was a figment of their imagination that it was not working.

Thanks,

Chris

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That's users for you.

My pleasure, good luck hunting. If you do fnd the cause, please post your solution here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

stax68 SSChampion Points: 11711 More actions · Answer 6

This almost certainly is to do with the original order of execution (of which there must have been one, even if we don't know what it will be).

One of the statements is probably updating a value which is then used in a subsequent statement. The statements in s2 could be put into a single statement using

UPDATE tab t1
SET col = CASE WHEN t1.col2 IS NULL THEN t2.col ELSE t1.col END
, col3 = ....
WHERE (t1.col2 IS NULL OR ....)

which might help performance.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant