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.

Answer 1

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