SQL Server 2005 触发器

发布于 2024-08-24 14:24:38 字数 408 浏览 2 评论 0原文

我在表上创建了一个用于更新的触发器。如果发生任何更新，我想将旧值存储在单独的表中。我试图从“插入”表中获取旧值，但更新后该表未填充旧值。

这是示例代码：

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs] AFTER  UPDATE
AS 

 DECLARE @url varchar(50)

BEGIN

 SELECT @url =  i.url 
   FROM INSERTED i

 INSERT INTO dbo.Triggers_tbl
   (ID, URL) 
 VALUES
   (1000, @url)

END

我从插入的表中获取 @url 作为 null。

请告诉我触发器出了什么问题

原文

I created a trigger on a table for updates. If any update happens, I want to store the old value in separate table. I am trying to get the old value from "inserted" table, but this table is not populated with old values after update.

Here is the sample code:

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs] AFTER  UPDATE
AS 

 DECLARE @url varchar(50)

BEGIN

 SELECT @url =  i.url 
   FROM INSERTED i

 INSERT INTO dbo.Triggers_tbl
   (ID, URL) 
 VALUES
   (1000, @url)

END

I get @url as null from the inserted table.

Please let me know what is wrong with the trigger

分享到QQ

分享到微博

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

发布评论

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

似狗非友 2024-08-31 14:24:38

DELETED 表包含“旧”值，“INSERTED”表包含“新”值。

除此之外，如果您的更新影响多行，则 INSERTED 和 DELETED 表可能包含多行，因此您可能应该使用 INSERT SELECT 而不是变量来运行插入。

INSERT INTO dbo.Triggers_tbl(URL) SELECT d.url FROM DELETED d

The DELETED table contains the "old" values and the "INSERTED" table contains the "new" values.

To add to this, the INSERTED and DELETED tables may contain multiple rows if your update affects multiple rows and therefore you should probably run your insert with an INSERT SELECT rather than a variable.

INSERT INTO dbo.Triggers_tbl(URL) SELECT d.url FROM DELETED d

回复收藏 0 原文

落在眉间の轻吻 2024-08-31 14:24:38

“旧”值（在UPDATE之后）在Deleted伪表中可用：

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs]
   AFTER  UPDATE
AS 
   DECLARE @url varchar(50)

   SELECT @url =  d.url from deleted d

   INSERT INTO dbo.Triggers_tbl(ID,URL) VALUES(1000,@url)

正如HLGEM正确注释的那样，OP的代码假设将调用触发器每行分开 - 这是不正确的。

从这个角度来看，触发器代码确实应该处理正在更新的行集，因此它应该类似于：

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs]
   AFTER  UPDATE
AS 
   INSERT INTO dbo.Triggers_tbl(ID,URL) 
      SELECT 1000, d.url
      FROM Deleted d

或类似的内容。

The "old" values (after an UPDATE) are available in the Deleted pseudo-table:

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs]
   AFTER  UPDATE
AS 
   DECLARE @url varchar(50)

   SELECT @url =  d.url from deleted d

   INSERT INTO dbo.Triggers_tbl(ID,URL) VALUES(1000,@url)

As HLGEM correctly commented on, the OP's code is assuming the trigger will be called for each row separately - which is not correct.

In that light, the trigger code really ought to deal with a set of rows being updated, and thus it should be something like:

CREATE TRIGGER [dbo].[Logs_Update]
   ON  [dbo].[Logs]
   AFTER  UPDATE
AS 
   INSERT INTO dbo.Triggers_tbl(ID,URL) 
      SELECT 1000, d.url
      FROM Deleted d

or something like that.

回复收藏 0 原文