October 13, 2008 at 11:17 pm
Comments posted to this topic are about the item Introduction to DML Triggers
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 12:35 am
Good informative article... Well explained...
October 14, 2008 at 1:03 am
Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.
A well behaved client has code like this:
start transaction
try
update/insert/delete/sp
commit
except
rollback;
other actions like inform the user
end;
More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.
October 14, 2008 at 6:09 am
Jack,
Thanks for the article. I don't know if it is your cheeky picture or your writing style, but you have become one of my favorites on this forum.
I am very familiar with Oracle triggers and I have not read up on the SQL Server implementation, but you left me with the impression, based on your set examples, that the trigger only fires once for each insert command, versus for every record inserted. I would assume it is for every record inserted, so the need for set stuff is moot. Did I just not get the reasoning behind your example?
Tobar
<><
Livin' down on the cube farm. Left, left, then a right.
October 14, 2008 at 7:17 am
I had a faulty trigger implementation a while back that I couldn't figure out. You're article let me see the problem.
Thanks Jack
October 14, 2008 at 7:29 am
Tobar (10/14/2008)
Jack,Thanks for the article. I don't know if it is your cheeky picture or your writing style, but you have become one of my favorites on this forum.
I am very familiar with Oracle triggers and I have not read up on the SQL Server implementation, but you left me with the impression, based on your set examples, that the trigger only fires once for each insert command, versus for every record inserted. I would assume it is for every record inserted, so the need for set stuff is moot. Did I just not get the reasoning behind your example?
Tobar
Tobar,
You had the correct impression. In SQL Server a trigger fires once per DML operation (Insert\Update\Delete). So, if I insert 3 rows in 1 operation the trigger fires once and the virtual inserted table will hold 3 rows and I will need to use set-based operations. Your understanding is why I wrote the article, because most folks new to SQL Server believe a trigger fires once per row, but triggers fire once per operation regardless of the number of rows affected.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 7:49 am
Thanks for the clarification. Makes it harder on the trigger writer but potentially less stressful to the data base.
<><
Livin' down on the cube farm. Left, left, then a right.
October 14, 2008 at 7:51 am
IF Exists(Select * From inserted Where ContactId > 10)
BEGIN
Insert Into Person.ContactLog
( ContactID,Action )
Select
ContactID
'Update'
From
inserted
Where
ContactId > 12
END
Jeff I have a question about the need to check for existance when nothing is being done on the else statement.
Does this not create added overhead because if it doesn't exist it's filtered by the where clause of your select statement.
I'm asking because in one of our systems we had a
if (select count(*) from inserted) = 0 then
return
update MyTable
set modifieddate = getdate()
inner join inserted on
Mytable.ID = inserted.ID
When we commented out our if statement we saw a huge improvement in execution speed of the trigger.
October 14, 2008 at 7:57 am
Robert (10/14/2008)
Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.A well behaved client has code like this:
start transaction
try
update/insert/delete/sp
commit
except
rollback;
other actions like inform the user
end;
More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.
Robert,
Thanks for the comment. I believe you are incorrect in stating that the transaction will not fail on an error with the trigger. An error in a trigger will cause a rollback in my experience. Your are correct in stating that the client application should handle it, and then you can re-submit a corrected transaction, but left to itself the data modification is rolled back either explicitly like in your code, or implicitly. The trigger takes place WITHIN the outer transaction whether explicit or implicit. For example with this trigger in the AdventureWorks database:
[font="Courier New"]ALTER TRIGGER [Person].[uContact] ON [Person].[Contact]
AFTER UPDATE NOT FOR REPLICATION
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Person].[Contact]
SET [Person].[Contact].[ModifiedDate] = GETDATE()
FROM
#inserted -- non-existent table used intentionally
WHERE
inserted.[ContactID] = [Person].[Contact].[ContactID];
END;[/font]
If you run this code with explicit transactions:
[font="Courier New"]-- row before update
SELECT
'Last Name Before Update' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
GO
BEGIN Try
BEGIN TRANSACTION
UPDATE [AdventureWorks].[Person].[Contact]
SET [LastName] = 'Changed'
WHERE
ContactID = 12
-- this will never be hit due to error in trigger
COMMIT TRANSACTION
SELECT 'After Commit'
END Try
BEGIN Catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
-- if the explicit rollback is not entered another error is raised
-- and the transcation is rolled back
--RollBack Transaction
END Catch
GO
-- row not updated
SELECT
'Last Name After Failed Update due to Trigger Error' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12[/font]
or this code with implicit transaction:
[font="Courier New"]-- row before update
SELECT
'Last Name Before Update' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
GO
BEGIN Try
UPDATE [AdventureWorks].[Person].[Contact]
SET [LastName] = 'Changed'
WHERE
ContactID = 12
-- this will never be hit due to error in trigger
SELECT 'After Commit'
END Try
BEGIN Catch
SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE
END Catch
GO
-- row not updated
SELECT
'Last Name After Failed Update due to Trigger Error' AS TYPE,
LastName
FROM
person.contact
WHERE
ContactID = 12
[/font]
The end result is the same. The LastName for ContactID 12 is not updated.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 8:06 am
upperbognor (10/14/2008)
Jeff I have a question about the need to check for existance when nothing is being done on the else statement.
Does this not create added overhead because if it doesn't exist it's filtered by the where clause of your select statement.
You are correct that I should have omitted the IF in the example code to provide a better trigger since the Where clause, which was incorrect as well (I used 12 instead of 10), does filter out the unwanted rows. The example was designed to show that the trigger using a variable to handle an IF will not work as anticipated (the prior trigger code) and to show the correct way to handle and IF condition in a trigger. The code within the IF was not the "important" part of the example. Perhaps there were other business rules that would have been enforced in that IF that required the IF. In this case that was not true.
Thanks for the catch.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 8:15 am
Andy Lennon (10/14/2008)
I had a faulty trigger implementation a while back that I couldn't figure out. You're article let me see the problem.Thanks Jack
Awesome. That's why I write so it's rewarding to hear it helped someone.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 10:28 am
There's one part of this article that I particularly appreciated. I had often read about certain tasks not not belonging in a trigger. But I could not figure out how one could automate and ensure the action happened if it wasn't coded at the database level. Now I know the idea of setting up the staging table in the trigger and later scheduling a job to do tasks off of the staging table. I rarely use triggers and when I do, it is usually just for basic auditing or occasional data validation stuff. So, I don't have an immediate use for this idea. But I will remember the idea and appreciate you writing about it.
Thanks, - JJ
October 14, 2008 at 12:12 pm
Very well written article.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
October 14, 2008 at 12:40 pm
Awesome article Jack!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 14, 2008 at 1:04 pm
JJ, Timothy, and Barry,
Thanks for the kind words. I just hope the article can be helpful especially to beginners.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply