Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Introduction to DML Triggers Expand / Collapse
Author
Message
Posted Monday, October 13, 2008 11:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #585204
Posted Tuesday, October 14, 2008 12:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,191, Visits: 1,368
Good informative article... Well explained...


Post #585222
Posted Tuesday, October 14, 2008 1:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:21 PM
Points: 1,149, Visits: 871
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.
Post #585232
Posted Tuesday, October 14, 2008 6:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 230, Visits: 692
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.
Post #585384
Posted Tuesday, October 14, 2008 7:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386, Visits: 823
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
Post #585427
Posted Tuesday, October 14, 2008 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #585443
Posted Tuesday, October 14, 2008 7:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 230, Visits: 692
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.
Post #585471
Posted Tuesday, October 14, 2008 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 7:42 AM
Points: 95, Visits: 785
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.
Post #585473
Posted Tuesday, October 14, 2008 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #585478
Posted Tuesday, October 14, 2008 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #585484
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse