本文介绍了如何在存储过程中使用插入删除表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
我为几个表创建了触发器.触发器具有相同的逻辑.我想使用一个通用的存储过程.但我不知道如何使用 inserted 和 deleted 表.
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 中只需要唯一的 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;
这篇关于如何在存储过程中使用插入删除表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！