Trigger Order Of Execution?
-
I'm trying to figure out of there's some kind of trigger order of operations on SQL 2000, or any other version. I have an Insert trigger that was condensed from 3 triggers. Originally
Section 1: Update another table with values, depending on a mandatory date field value in the insert.
Section 2: Pull forward data values in a previous record if the values were not specified in the inserted record.
Section 3: Set defaults for certain fields if they don't have a value in the insert.
With the trigger code set in this order the last 2 sections did not work. To make them work I had to move the secodn 2 sections to the top of the trigger code. I do not know yet if the 3rd section works now that it is the last section of code in the trigger.
I'll include the trigger code here if necessary, but I am trying to figure out if there are rules of trigger code order so that everything in the trigger happens as it is supposed to.
Thanks,
Chris
-
Check out topic "triggers, first trigger" in BOL.
_____________
Code for TallyGenerator -
You can set which trigger is the first to fire and which is the last. If that isn't done, then the triggers will fire in no defined order.
Looking at your summary, the defaults could be done with defaults on the columns. I'd guess, looking at the summary, that the order should have been 3,2,1, but without understanding the design and reason, I'm just guessing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
That BOL topic discusses order of triggers, not order of operations within a single trigger, which is my problem.
The order of the sections in the trigger was random when they were condensed. As long as all of the functions happen, the order doesn't really matter. It has just been a pain to debug and I was wondering if I was missing something that I could avoid in the future, as I plan to be condensing more triggers. Some of our tables have 8-10 triggers on them.
Thanks
Chris
-
Order of operations within a trigger is the same as within any other sql code, from top to bottom according to normal control flow commands (while, if)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
That's what I thought too, until part of the trigger didn't work, and reordering the code in the trigger, with no other mods, made all parts of the trigger work properly.
Here is the current trigger. The comments still reflect the original order of sections, but the sections are actually ordered as 2, 3, 1 now, and all parts of the trigger work.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EJobINSERTTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[EJobINSERTTrig]
GO
CREATE TRIGGER EJobINSERTTrig ON dbo.EJob FOR INSERT AS
/* This trigger is stored in Source control in the DBAdmins area of ClearCase.
This trigger has different sections that perform different functions.
Condensed by Chris Stamey, 2-22-2006.
Version 1.0
1. Initial condensation of triggers.
Version 1.01, 6-26-2006
1.Updated/debugged by vendor.
Version 1.02, 6-27-2006, Chris Stamey
1. Changed ejPrimary default to look for Null in addition to zero-length string.
2. Added HCE field to pull forward.
3. Updated ejPrimary update section.
Section 1: Row-level security changes.
Section 2: Pull-forward of previous record data, for custom fields.
Section 3: Set field default, where applicable.
*/
/*
Section 2:
This section is for the "pull-forward" of custom fields that need to be put into new records
when the application inserting the data does not specify a value on Insert.
The following create statement is merely here for historical value as this section
was once a separate trigger.
CREATE TRIGGER EJob_INSERTTrig_PS705770 ON dbo.EJob FOR INSERT AS
*/
DECLARE @FlxID2 INT
DECLARE @FlxIDeb INT
DECLARE @EjDateBeg DATETIME
DECLARE @EjWorksite VARCHAR
DECLARE @EjWorkJobTitle VARCHAR
DECLARE @EjContractedHours VARCHAR
Declare @ejHCE Char(3)
SELECT @FlxID2 = I.ejflxid,
@FlxIDeb = I.ejflxideb,
@EjDateBeg = I.ejdatebeg,
@EjWorksite = I.ejworksite,
@EjWorkJobTitle = I.ejworkjobtitle,
@EjContractedHours = I.ejcontractedhours,
@ejHCE = I.ejHighlyCompExempt
FROM Inserted I
IF @EjWorksite IS NULL
UPDATE EJob
SET EjWorksite =
(SELECT EjWorksite FROM EJob
WHERE EjFlxIDEb = @FlxIDEb
AND EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = @FlxIDEb AND EjFlxID <> @FlxID2))
WHERE EjFlxID = @FLXID2
IF @EjWorkJobTitle IS NULL
UPDATE EJob
SET EjWorkJobTitle =
(SELECT EjWorkJobTitle FROM EJob
WHERE EjFlxIDEb = @FlxIDEb
AND EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = @FlxIDEb AND EjFlxID <> @FlxID2))
WHERE EjFlxID = @FLXID2
IF @EjContractedHours IS NULL
UPDATE EJob
SET EjContractedHours =
(SELECT EjContractedHours FROM EJob
WHERE EjFlxIDEb = @FlxIDEb
AND EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = @FlxIDEb AND EjFlxID <> @FlxID2))
WHERE EjFlxID = @FLXID2
IF @ejHCE IS NULL
UPDATE EJob
SET ejHighlyCompExempt =
(SELECT ejHighlyCompExempt FROM EJob
WHERE EjFlxIDEb = @FlxIDEb
AND EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = @FlxIDEb AND EjFlxID <> @FlxID2))
WHERE EjFlxID = @FLXID2
--*************************
/*
Section 3:
This section is for field Defaults, to set the value of fields that have no value set for them on Insert.
Set ejDepartment default value.
*/
Update eJob
Set ejDepartment = '(none)'
From Inserted Ins
Inner Join EJob EJ On Ins.ejFlxID = EJ.ejFlxID
Where EJ.ejDepartment = ''
--Set ejPrmary default value.
Update EJ
Set EJ.ejPrimary = 'P'
From Inserted I
Inner Join EJob EJ On I.ejFlxID = EJ.ejFlxID
Where EJ.ejPrimary = '' Or EJ.ejPrimary Is Null
/*
Section 1: This section is for updating the eBase table for row-level
security users, so they see the correct information.
*/
DECLARE @DateBeg DATETIME
DECLARE @DateEnd DATETIME
DECLARE @Temp VARCHAR(1)
DECLARE @ChangedValue0 VARCHAR(255)
DECLARE @ChangedValue1 VARCHAR(255)
DECLARE @FLXID INT
BEGIN
SELECT @FLXID = EjFlxIDEb, @DateBeg = EjDateBeg, @DateEnd = EjDateEnd
FROM INSERTED
IF @DateEnd < GetDate()
BEGIN
RETURN
END
IF @DateBeg <= GetDate()
BEGIN
SELECT @ChangedValue0=EjRegion FROM INSERTED
SELECT @ChangedValue1=EjDivision FROM INSERTED
UPDATE Ebase
SET EbRLSString1 = @ChangedValue0
WHERE EbFlxID = @FlxID
UPDATE Ebase
SET EbRLSString2 = @ChangedValue1
WHERE EbFlxID = @FlxID
RETURN
END
BEGIN
SELECT @Temp = 'x'
FROM EJob
WHERE EjFLXIDEb=@FLXID
AND EjDateBeg <= GetDate()
AND (EjDateEnd IS NULL OR EjDateEnd >= GetDate())
IF @@ROWCOUNT >= 1
RETURN
END
BEGIN
SET ROWCOUNT 1
SELECT @ChangedValue0 = ''
SELECT @ChangedValue1 = ''
SELECT @ChangedValue0=EjRegion
FROM EJob
WHERE EjFLXIDEb=@FLXID
AND EjDateBeg > GetDate()
ORDER BY EjDateBeg
UPDATE Ebase
SET EbRLSString1 = @ChangedValue0
WHERE EbFLXID = @FLXID
SELECT @ChangedValue1=EjDivision
FROM EJob
WHERE EjFLXIDEb=@FLXID
AND EjDateBeg > GetDate()
ORDER BY EjDateBeg
UPDATE Ebase
SET EbRLSString2 = @ChangedValue1
WHERE EbFLXID = @FLXID
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
What do you mean by 'didn't work'? Threw an error? Gave unexpected results?
One thing I noticed in the 'section 1' was the line SET ROWCOUNT 1 which, from what I can see, never gets reset to 0. Any query running after that line would only affect one record.
Also possible that the reordering was necessary from a data point of view, that one of the updates in section 2 or 3 was required by some part of section 1
On a broader point, do you only ever update 1 record at a time? If you update multiple, the trigger is going to give unexpected results, because of the selection of values from inserted into variables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Some the the fields did not "move forward", as is supposed to happen in section 2, and only one of the defaults got set, the ejDepartment, the first of the two default setting queries. There were no erros of any kind.
Correct me if I'm wrong, but since this is an Insert trigger it should only affetc one record at a time anyway, right? If that is correct, would the RowCount 1 make any difference here?
I did not write the code for section 1. I supplemented the code for section 2 and 3, to add fields, and then condensed 3 triggers into this one. The vendor wrote the original code for section 1 and 2. This would be why I cannot explain some of the stuff that was done in section 1.
I do not believe that any section depends on another section's data actions here.
I am open to ideas though, and will clean up the mess if the vendor screwed up.
Thanks,
Chris
-
Insert triggers, like any other trigger, fire once per operation. If an insert inserts 10 records into a table in a single operation, the trigger will fire once and the inserted table will have 10 records in.
It may be that whatever application is inserting does the inserts one row at a time, but that's the app.
I'll take a more detailed look at your trigger tomorrow and give you any suggestions then.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I thought that a trigger fired for each record that X action happened to, which is why actions on tables with triggers are inherently slower than without.
Also, I am not familiar with a way to insert multiple records in one action. I have always used a separate insert statement for each record I want inserted. Have I been missing something here? If I am I surely need to know as my triggers are written as if they should run for each record that gets inserted/updated or deleted, as applicable.
Thanks,
Chris
-
No, they run once per statement, whether it be insert, update or delete. Actions are slower because of the additional code and stuff in the trigger. A well-written trigger shouldn't have much overhead, but will always have some
You can insert multiple records using
INSERT INTO tbl
SELECT * FROM other tbl WHERE ...
as opposed to
INSERT INTO tbl
VALUES (...)
In the first, all the records returned by the select will be inserted into the table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh year, forgot about that insert. Kind of pathetic because I use it all the time.
So I cannot expect my trigger to fire for each record inserted? I'm going to have to re-evaluate soem triggers in this DB, those I have written as well as others, because I don't know if they take this into account.
How would the trigger have to change to account for this? Will I need a process, hopefully not a cursor, to loop through the records in the insert and do actions as necessary against each record?
Thanks,
Chris
-
You probably don't need to loop through the records. With any luck (and good db design) you can rewrite your code to use the whole of the inserted and/or deleted tables at once, in SQL statements - just as you would try to do outside a trigger. If you want any advice on rewriting the triggers, post your code.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Not cursors, please. Cursors in triggers kill performance. By the looks of the trigger you should be able to change the updates to set-based.
Here's a section of your trigger that I modified. I won't guarentee that it works, as I can't test it, but it should give you some ideas.
Old:
IF @EjWorksite IS NULL
UPDATE EJob
SET EjWorksite =
(SELECT EjWorksite FROM EJob
WHERE EjFlxIDEb = @FlxIDEb
AND EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = @FlxIDEb AND EjFlxID <> @FlxID2))
WHERE EjFlxID = @FLXID2
New:
IF EXISTS (SELECT 1 FROM inserted WHERE ejworksite IS NULL)
UPDATE EJob
SET ejworksite = DefaultJob.EjWorksite
FROM EJob DefaultJob , inserted
WHERE DefaultJob.EjFlxIDEb = inserted.ejflxideb
AND DefaultJob.EjDateBeg = (SELECT Max(EjDateBeg) FROM Ejob
WHERE EjFlxIDEb = inserted.ejflxideb AND EjFlxID not in (select EjFlxID from inserted)))
WHERE EJob.EjFlxID=inserted.EjFlxID and inserted.ejworksite IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I have been reading up on triggers and while the documentation doesn't come right out and say it, it does imply that with After triggers they fire once per record, and Instead triggers for once per action. If this is true then I'm not gonna get bit in the butt on this.
Thanks for the trigger changes. I will see about incorprating them into my trigger. Right now I have to review another trigger that is supposed to pull-forward data, but does not seem to be doing so. It worked fine in February of 2005, when I wrote it, but now I get reports it has stopped working.
Thanks,
Chris
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply