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

I am trying to figure out 开发者_如何学Gowhich 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.

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

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

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