1

Gotcha alert re Need help tuning an on update trigger to honor a where clause.

SQL Server 2008. Main table has, say, 12345 rows. My intent is, on update I want to copy only the records being updated to a history table when an update is done. On updating with a where clause, I am finding that the trigger is transfering the entire table instead of just the records affected as limited by the where clause.

The History table in Production does not have the same data structure as Development, so I copied/pasted the Prod structure from Select. Not a SQL Server guru, I did not notice that I had missed from insert from my Dev trigger and left the actual table name from the Select in the trigger on Prod:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger [dbo].[myTable_LogUpdate] 
ON [dbo].[myTable]
FOR UPDATE
AS
    INSERT INTO myTable_Hist ([ID], [FLD_1], ..., [FLD_N], [HIST_DT])
        SELECT 
            [ID], [FLD_1], ..., [FLD_N], GETDATE() AS HIST_DT
        FROM
            [dbo].[myTable] -- <<< this should be FROM inserted
GO

EXEC sp_settriggerorder @triggername=N'[dbo].[myTable_LogUpdate]', @order=N'Last', @stmttype=N'UPDATE'
GO

When I run an update with a where clause to update, say, 5 records:

USE [myDb]

update myTable 
set FLD_7 = 2 
where FLD_1 = 10 and FLD_2 = 20

In messages I get 2 responses:

(12345 rows affected)
(5 rows affected)

and running a count(*) before and after of the history table shows that all 12345 rows have been inserted into the myTable_LogUpdate table (which is now gargantuan and desperately in need of clean-up).

Update: I had to delete the original Prod trigger because the drive had run out of disk space. The trigger code in my original post was from Dev so it was showing the correct code instead of the actual troubled trigger code I had been running on Prod. There was no way for any SO guru to answer the problem I had because the OP was not the bug.

I have updated the OP to reflect what the problem code was, with a notation where the correction needed to be. I have recreated the history table, fixed the trigger to be from inserted and it works properly now.

Thank you to Sean, whose comments led me to this revelation.

gordon
  • 1,152
  • 1
  • 12
  • 18
  • I am guessing you must have more than 1 update trigger on this table? Can you share the other trigger? – Sean Lange Oct 24 '16 at 18:31
  • Nope. It's the only trigger. The `(5 rows affected)` is the DB response message for the actual update I'm trying to do in the main table. The `(12345 rows affected)` is the DB response message for its transaction transferring all existing rows of the main table to the history table. – gordon Oct 25 '16 at 14:17
  • If you have only 1 trigger what is the point of setting the trigger order? And in the messages the order the messages received is important. The first one is the update statement, the second one would be the trigger. Something isn't right for sure but it is impossible to tell. If you can share your table structure and a valid trigger along with data that can reproduce this we can help. – Sean Lange Oct 25 '16 at 14:20
  • I was given the trigger from a colleague. I just jiggered it until it seemed to work O_o. So you're saying that since there's only one trigger I can/should remove `@order=N'Last',`? The order of the responses in the OP is what the DB returns. The trigger is to capture the rows to be updated before they are updated, which is why it comes first. (And thank you marc_s for the cleanup!) – gordon Oct 25 '16 at 14:38
  • Sean, your comment induced me to look more closely. Is the `FROM inserted` what is causing the DB to transfer all rows when an update is done? I do not need the trigger to capture original inserts, only a pre-update snapshot of the rows to be updated. – gordon Oct 25 '16 at 14:41
  • 1
    Not sure what you mean by your last comment but the inserted virtual table contains ALL the rows that were updated. The inserted table contains the new values and the deleted table contains the old values. From your last comment I think you want to look at the deleted table instead of inserted. :) – Sean Lange Oct 25 '16 at 15:00
  • Sean thank you for your responses. I found that my development trigger indeed has `from inserted` while in production somehow instead of inserted it was `from [dbo].[myTable]`!!!! Not sure there is a big enough rock to hide under about this. – gordon Oct 26 '16 at 16:42

0 Answers0