How Use Inserted\Deleted Table in Stored Procedure

How use inserted\deleted table in stored procedure?

You can use a table valued parameter to store the inserted / deleted values from triggers, and pass it across to the proc. e.g., if all you need in your proc is the UNIQUE FileID's:

CREATE TYPE FileIds AS TABLE
(
    FileId INT
);

-- Create the proc to use the type as a TVP
CREATE PROC commonProc(@FileIds AS FileIds READONLY)
    AS
    BEGIN
        UPDATE at
            SET at.DateVersion = CURRENT_TIMESTAMP
        FROM ATable at
            JOIN @FileIds fi
            ON at.FileID = fi.FileID;
    END

And then pass the inserted / deleted ids from the trigger, e.g.:

CREATE TRIGGER MyTrigger ON SomeTable FOR INSERT
AS
    BEGIN
        DECLARE @FileIds FileIDs;
        INSERT INTO @FileIds(FileID)
            SELECT DISTINCT FileID FROM INSERTED;
        EXEC commonProc @FileIds;
    END;

How to access inserted and deleted logical tables with a stored procedure

You can use the inserted and deleted tables only in triggers or in the output clause of DML statements.

In an insert statement, the output clause can reference the inserted table:

DECLARE @Ids AS TABLE (id int);

INSERT INTO dbo.Table (ID) 
OUTPUT Inserted.ID INTO @Ids(id)
VALUES (1), (2), (3);

In an update statement, you can reference both the inserted table and the deleted table:

DECLARE @Ids AS TABLE (oldId int, newId int);

UPDATE dbo.Table 
SET ID = 1
OUTPUT Deleted.ID, Inserted.ID INTO @Ids(oldId, newId);

And in a delete statement, you can reference the deleted table:

DECLARE @Ids AS TABLE (id int);

DELETE dbo.Table 
OUTPUT Inserted.ID INTO @Ids(id)
FROM dbo.Table 
WHERE ID IN (1, 2, 3);

A Merge statement is unique since you have access to both the source table and the inserted/deleted tables in it's output clause, as demonstrated on this post.

Trigger AFTER INSERT, UPDATE, DELETE to call stored procedure with table name and primary key

Not the best solution, but just a direct answer on the question:

SELECT @Key = COALESCE(deleted.id_num,inserted.id_num);

Also not the best way (if not the worst) (do not try this at home), but at least it will help with multiple values:

DECLARE @Key INT;
DECLARE triggerCursor CURSOR LOCAL FAST_FORWARD READ_ONLY 
    FOR SELECT COALESCE(i.id_num,d.id_num) AS [id_num]
        FROM inserted i
        FULL JOIN deleted d ON d.id_num = i.id_num
        WHERE (
                COALESCE(i.fname,'')<>COALESCE(d.fname,'')
             OR COALESCE(i.minit,'')<>COALESCE(d.minit,'')
             OR COALESCE(i.lname,'')<>COALESCE(d.lname,'')
        )
;

OPEN triggerCursor;
FETCH NEXT FROM triggerCursor INTO @Key;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC @ErrCode = dbo.SyncQueueItem 'new_employees', @key;
    FETCH NEXT FROM triggerCursor INTO @Key;
END

CLOSE triggerCursor;
DEALLOCATE triggerCursor;

Better way to use trigger based "value-change-tracker":

INSERT INTO [YourTableHistoryName] (id_num, fname, minit, lname, WhenHappened)
SELECT COALESCE(i.id_num,d.id_num) AS [id_num]
    ,i.fname,i.minit,i.lname,CURRENT_TIMESTAMP AS [WhenHeppened]
FROM inserted i
FULL JOIN deleted d ON d.id_num = i.id_num
WHERE ( COALESCE(i.fname,'')<>COALESCE(d.fname,'')
    OR COALESCE(i.minit,'')<>COALESCE(d.minit,'')
    OR COALESCE(i.lname,'')<>COALESCE(d.lname,'')
)
;

The best (in my opinion) way to track changes is to use Temporal tables (SQL Server 2016+)

Trigger: How does the inserted table work? How to access its rows?

You can not. From the Use the inserted and deleted Tables article on microsoft.com, you can read:

The inserted table stores copies of the affected rows during INSERT and UPDATE statements.

That means that the inserted table will only contain rows for the current INSERT or UPDATE statement.

If you do want to see all rows for several such INSERT or UPDATE statements, you will have to store these rows in a table you created yourself.

---

Related Topics