SQL Triggers - Deleted or Updated? or maybe something else?

Question

I am trying to figure out which i need to use here: deleted, inserted or updated.

basically.

I need to write some data to the history table, when the main table is updated, and only if the status changes from something to either pending or active.

This is what I have now:

ALTER TRIGGER [dbo].[trg_SourceHistory]  ON [dbo].[tblSource]
FOR UPDATE AS  
    DECLARE @statusOldValue char(1) 
    DECLARE @statusNewValue char(1)

    SELECT @statusOldValue = statusCode FROM deleted 
    SELECT @statusNewValue= statusCode FROM updated

    IF (@statusOldValue <> @statusNewValue) AND 
       (@statusOldValue = 'P' or @statusOldValue = 'A')
    BEGIN TRY
       INSERT * INTO tblHistoryTable)  
           select * from [DELETED]

so I want the new data to stay in the main table, the the history table to be updated with what is being overwritten... right now it just copies the same info over. so after update, both my tables have the same data.

marc_s · Answer 1 · 2011-07-25T20:29:27.943

There are only the Inserted and Deleted pseudo tables - there's no Updated.

For an UPDATE, Inserted contains the new values (after the update) while Deleted contains the old values before the update.

Also be aware that the triggers is fired once per batch - not once for each row. So both pseudo tables will potentially contain multiple rows! Don't just assume a single row and assign this to a variable - this

SELECT @statusOldValue = statusCode FROM deleted 
SELECT @statusNewValue= statusCode FROM updated

will fail if you have multiple rows ! You need to write your triggers in such a fashion that they work with multiple rows in Inserted and Deleted !

Update: yes - there IS a much better way to write this:

ALTER TRIGGER [dbo].[trg_SourceHistory]  ON [dbo].[tblSource]
FOR UPDATE 
AS  
   INSERT INTO dbo.tblHistoryTable(Col1, Col2, Col3, ...., ColN)
      SELECT Col1, COl2, Col3, ..... ColN
        FROM Deleted d
        INNER JOIN Inserted i ON i.PrimaryKey = d.PrimaryKey
        WHERE i.statusCode <> d.statusCode
          AND d.statusCode IN ('A', 'P')

Basically:

explicitly specify the columns you want to insert - both in the INSERT statement as well as the SELECT statement retrieving the data to insert - to avoid any nasty surprises
create an INNER JOIN between Inserted and Deleted pseudo-tables to get all rows that were updated
specify all other conditions (different status codes etc.) in the WHERE clause of the SELECT

This solution works for batches of rows being updated - it won't fail on a multi-row update....

yes yes a million times yes, a badly written trigger can create horrible data integrity problems. — HLGEM, Jul 25 '11 at 19:47
@xrum: provided the much better way to write this - see my update in my response — marc_s, Jul 25 '11 at 20:29

MatBailie · Accepted Answer · 2011-07-25T21:29:06.043

5

You need to use both the inserted and deleted tables together to check for records that:
1. Already existed (to check it's not an insert)
2. Still exists (to check it's not a delete)
3. The Status field changed

You also need to make sure you do that in a set based approach, as per marc_s's answer, triggers are not single record processes.

INSERT INTO
  tblHistoryTable
SELECT
  deleted.*
FROM
  inserted
INNER JOIN
  deleted
    ON inserted.PrimaryKey = deleted.PrimaryKey
WHERE
  inserted.StatusCode <> deleted.StatusCode
  AND (inserted.StatusCode = 'P' OR inserted.StatusCode = 'A')

inserted = the new values
deleted = the old values

edited Jul 25 '11 at 21:29

answered Jul 25 '11 at 18:47

MatBailie

83,401
18
103
137

I think it's been some time since I was last nitpicking at an answer of yours. So forgive me, please, but I couldn't go past this one. First, it's `deleted.StatusCode`, not `inserted.StatusCode`, that was supposed to be `in ('P', 'A')`. And second, your explanation about what `inserted` and `deleted` are should be either put into a comment or taken outside the code snippet. There. (But basically I agree with the script.) – Andriy M Jul 25 '11 at 19:19
The OPs code snippet does refer to the *old* status being 'P' or 'A', but textual description includes `only if the status changes from something to either pending or active`. As the code contains other issues, I based my answer on the text. I've editted for your other point :) – MatBailie Jul 25 '11 at 21:28
1

I know you won't admit it but I'm sure you've answered this question on purpose, to lure a nitpicker like me into nitpicking at your answer, only to fool them afterwards. A clever trap. ;) – Andriy M Jul 25 '11 at 22:24
1

I can not lie, my lure was subtle, but not subtle enough. I shall raise my game, I shall lure you in and catch you, but without you ever knowing it happened ;) – MatBailie Jul 26 '11 at 10:21

score 1 · Answer 3 · answered Jul 25 '11 at 18:29

1

There is no updated table, you are looking for inserted.

answered Jul 25 '11 at 18:29

Andreas Ågren

3,879
24
33

SQL Triggers - Deleted or Updated? or maybe something else?

3 Answers3