November 16, 2009 at 10:11 pm
I created triggers with SMO and set trigger order via properties on a Trigger Object.
The generated trigger looks like this:
.... trigger logic
END <--- end statement
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_IUD]', @order=N'Last', @stmttype=N'DELETE'
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_IUD]', @order=N'Last', @stmttype=N'INSERT'
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_IUD]', @order=N'Last', @stmttype=N'UPDATE'
There is no GO statement between the trigger and set trigger execute statements. If I use SSMS to generate CREATE TRigger code, the set order execute statements are generated without "GO" between "End" and the first EXEC.
If I generate ALTER Trigger, the trigger create is generated with all EXECUTE statements again .. but at the bottom this is appended:
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_DataCapture_IUD]', @order=N'Last', @stmttype=N'DELETE'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_DataCapture_IUD]', @order=N'Last', @stmttype=N'INSERT'
GO
EXEC sp_settriggerorder @triggername=N'[dbo].[tr_Table_1_DataCapture_IUD]', @order=N'Last', @stmttype=N'UPDATE'
My question is if that seems to look ok. Are those EXEC statements a part of the trigger code that get executed when the trigger fires? I tried to check with a debugger but SSMS crashes when the breakpoint reaches the line with the execute set order.
November 17, 2009 at 12:17 am
Those GO statements are important, they seperate one section from the other. Beyond that you really shouldn't do this. Although SQL supports it it is a bad practice to have triggers all named the same..
CEWII
November 18, 2009 at 3:38 pm
So, to you does it look like a bug in a SMO generated code?
It's all one trigger....do you suggest that there should be a separate trigger for Insert, Update and Delete?
Why is it bad practice to have one vs. three? Could you deliberate?
November 18, 2009 at 4:09 pm
1. At first glance it looks like hinky SMO created code, without seeing exactly how it was executed against the database I can't be sure.
2. I saw sp_settriggerorder and I made an assumption, that was bad. My point was that although SQL supports having three triggers all named the same that is a bad practice. I was not refering to a single trigger that handles all actions.
On the subject off one vs. three that is more subjective. Keep in mind that this is the way *I* like to do things so mileage may vary..
I generally don't track deletes, when I do it is usually for logging purposes.
I generally don't track inserts but when I do it is usually to apply some business logic that I can't handle through foreign keys or check constraints, sometimes for logging, but not very often.
I generally only track updates. I want to know what was changed by who.
One of the reason I split them up is that in many cases I want to handle the three actions very differently and when they are all in the same trigger I have to determine which action occured.
I also don't track changes unless I need to, too much overhead associated with it..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply