存储过程和触发器

发布于 2024-10-11 11:49:08 字数 611 浏览 2 评论 0原文

我有一个任务——创建更新触发器，该触发器适用于真实的表数据更改（而不仅仅是使用相同的值进行更新）。为此，我创建了复制表，然后开始将更新的行与旧复制的行进行比较。当触发器完成时，有必要实现副本：

UPDATE CopyTable SET
    id = s.id,
    -- many, many fields
FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED)
                 AND CopyTable.id = s.id;

我不喜欢再在触发器中包含这些丑陋的代码，因此我将其提取到存储过程中：

CREATE PROCEDURE UpdateCopy AS
BEGIN
UPDATE CopyTable SET
    id = s.id,
    -- many, many fields
    FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED)
    AND CopyTable.id = s.id;
END

结果是 -- 无效的对象名称“INSERTED”。我该如何解决这个问题？

问候，

原文

I had a task -- to create update trigger, that works on real table data change (not just update with the same values). For that purpose I had created copy table then began to compare updated rows with the old copied ones. When trigger completes, it's neccessary to actualize the copy:

UPDATE CopyTable SET
    id = s.id,
    -- many, many fields
FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED)
                 AND CopyTable.id = s.id;

I don't like to have this ugly code in the trigger anymore, so I have extracted it to a stored procedure:

CREATE PROCEDURE UpdateCopy AS
BEGIN
UPDATE CopyTable SET
    id = s.id,
    -- many, many fields
    FROM MainTable s WHERE s.id IN (SELECT [id] FROM INSERTED)
    AND CopyTable.id = s.id;
END

The result is -- Invalid object name 'INSERTED'. How can I workaround this?

Regards,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最单纯的乌龟 2024-10-18 11:49:08

将代码留在触发器中。 INSERTED 是一个仅在触发代码中可用的伪表。不要不要尝试传递此伪表值，它可能包含大量条目。

这就是 T-SQL，一种声明性数据访问语言。它不是普通的过程编程语言。像“代码重用”这样的常识并不适用于 SQL，它只会导致性能问题。将代码留在触发器中，它所属的位置。为了便于重构，可以通过一些代码生成工具生成触发器，这样您就可以轻松重构触发器。

回复收藏 0 原文

倾｀听者〃 2024-10-18 11:49:08

问题是 INSERTED 仅在触发器期间可用

-- 触发更改以构建 id 列表

DECLARE @idStack VARCHAR(max)
SET @idStack=','
SELECT @idStack=@idStack+ltrim(str(id))+',' FROM INSERTED

-- 触发更改以调用存储过程

EXEC updateCopy(@idStack)

-- 要采取的过程逗号分隔的 id 列表

CREATE PROCEDURE UpdateCopy(@IDLIST VARCHAR(max)) AS
BEGIN
UPDATE CopyTable SET
    id = s.id,
    -- many, many fields

    FROM MainTable s WHERE charindex(','+ltrim(str(s.id))+',',@idList) > 0
    AND CopyTable.id = s.id;
END

性能不会很好，但它应该允许您做您想做的事情。

刚刚即时输入，但应该运行正常

The problem is that INSERTED is only available during the trigger

-- Trigger changes to build list of id's

DECLARE @idStack VARCHAR(max)
SET @idStack=','
SELECT @idStack=@idStack+ltrim(str(id))+',' FROM INSERTED

-- Trigger changes to call stored proc

EXEC updateCopy(@idStack)

-- Procedure to take a comma separated list of id's

CREATE PROCEDURE UpdateCopy(@IDLIST VARCHAR(max)) AS
BEGIN
UPDATE CopyTable SET
    id = s.id,
    -- many, many fields

    FROM MainTable s WHERE charindex(','+ltrim(str(s.id))+',',@idList) > 0
    AND CopyTable.id = s.id;
END

Performance will not be great, but it should allow you to do what you want.

Just typed in on the fly, but should run OK

回复收藏 0 原文