如何在存储过程中使用插入的已删除表？ [英] How use inserted\deleted table in stored procedure?

问题描述

我为多个表创建触发器。触发器具有相同的逻辑。我将要使用一个通用的存储过程。
但我不知道如何使用插入和已删除表。

I creating triggers for several tables. The triggers have same logic. I will want to use a common stored procedure. But I don't know how work with inserted and deleted table.

示例：

SET @FiledId = (SELECT FiledId FROM inserted)
begin tran
   update table with (serializable) set DateVersion = GETDATE()
   where FiledId = @FiledId

   if @@rowcount = 0
   begin
      insert table (FiledId) values (@FiledId)
   end
commit tran

推荐答案

表值参数，用于存储从中插入/删除的值触发，并将其传递给proc。例如，如果您在proc中需要的只是UNIQUE FileID's ：

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

然后从触发器中传递插入/删除的ID，例如：

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;

这篇关于如何在存储过程中使用插入的已删除表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！