January 30, 2015 at 7:32 am
Hello,
We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening. We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." Any ideas? We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
IF EXISTS (SELECT TransactionID from inserted)
BEGIN
SELECT @trxId = i.TransactionID from inserted i
INSERT INTO TrxToFinanceQueue (TransactionID) VALUES (@trxId)
PRINT N'Update caused insert of ' + CONVERT(varchar(200), @trxId)
END
ELSE BEGIN
--TransactionID not available. Querying DB instead using internal PK instead of GUID
SELECT @trxId = t.TransactionID
FROM FPS_Transaction AS t
JOIN inserted AS i ON t.TransactionNum = i.TransactionNum
INSERT INTO TrxToFinanceQueue (TransactionID) VALUES (@trxId)
PRINT N'Inserted ' + CONVERT(varchar(200), @trxId)
END
END TRY
BEGIN CATCH
--[omitted: log error to logging table]
END CATCH
January 30, 2015 at 7:54 am
Chad Caswell (1/30/2015)
Hello,We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening. We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." Any ideas? We also have a version of this trigger that deals with INSERTS - it works flawlessly.
ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON
IF EXISTS (SELECT TransactionID from inserted)
BEGIN
SELECT @trxId = i.TransactionID from inserted i
INSERT INTO TrxToFinanceQueue (TransactionID) VALUES (@trxId)
PRINT N'Update caused insert of ' + CONVERT(varchar(200), @trxId)
END
ELSE BEGIN
--TransactionID not available. Querying DB instead using internal PK instead of GUID
SELECT @trxId = t.TransactionID
FROM FPS_Transaction AS t
JOIN inserted AS i ON t.TransactionNum = i.TransactionNum
INSERT INTO TrxToFinanceQueue (TransactionID) VALUES (@trxId)
PRINT N'Inserted ' + CONVERT(varchar(200), @trxId)
END
END TRY
BEGIN CATCH
--[omitted: log error to logging table]
END CATCH
How do you know this trigger isn't firing?
BTW, your trigger has a MAJOR flaw. It assumes that only 1 row will ever be updated. In sql server triggers fire once per operation, not once per row. You need to create set based logic in triggers. I suspect your insert trigger is the same. Whenever you see scalar variables in a trigger it is a big red flag that it needs to be reworked.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 7:56 am
Sean beat me to it again!
It would be a good idea to post the error message text. And to fix the fact that only single-row updates are handled.
January 30, 2015 at 8:42 am
Thank you both for your help! I can't believe we missed this. Problem solved and we are up and running once again. For future reference, this page (http://www.mssqltips.com/sqlservertutorial/2911/working-with-triggers/) provided a simple example of what you both described.
Cheers!
January 30, 2015 at 8:46 am
Chad Caswell (1/30/2015)
Thank you both for your help! I can't believe we missed this. Problem solved and we are up and running once again. For future reference, this page (http://www.mssqltips.com/sqlservertutorial/2911/working-with-triggers/) provided a simple example of what you both described.Cheers!
Glad that helped. Make sure you look at your insert trigger to. ALL triggers need to be set based or you will run into problems at some point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2015 at 9:25 am
I had a client GO OUT OF BUSINESS because of this exact flaw (triggers that don't handle more than one row)!!! And I TOLD THEM IT WOULD HAPPEN before it did!! Stupid gits didn't listen to repeated pleas, and 14 months after my initial performance review they folded.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2015 at 10:26 am
TheSQLGuru (1/30/2015)
I had a client GO OUT OF BUSINESS because of this exact flaw (triggers that don't handle more than one row)!!! And I TOLD THEM IT WOULD HAPPEN before it did!! Stupid gits didn't listen to repeated pleas, and 14 months after my initial performance review they folded.
I remember that story and think it frequently when I see these types of triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply