Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to DML Triggers


Introduction to DML Triggers

Author
Message
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15248 Visits: 14866
Comments posted to this topic are about the item Introduction to DML Triggers



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6797 Visits: 1407
Good informative article... Well explained...



Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1670 Visits: 1010
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.
Tobar
Tobar
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 758
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.
Andy Lennon
Andy Lennon
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1406 Visits: 826
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
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15248 Visits: 14866
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Tobar
Tobar
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 758
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.
upperbognor
upperbognor
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 937

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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15248 Visits: 14866
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:

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;



If you run this code with explicit transactions:

-- 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




or this code with implicit transaction:

-- 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




The end result is the same. The LastName for ContactID 12 is not updated.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15248 Visits: 14866
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search