在触发器中使用插入和删除的表

发布于 2024-09-11 14:41:29 字数 522 浏览 8 评论 0原文

我想编写触发器来处理插入和删除的表。我已经编写了用于插入的触发器：

CREATE TRIGGER FILL_TABLE
ON Person FOR INSERT
AS
DECLARE @ID int
SELECT @ID = p.ID
FROM Person AS p 
    INNER JOIN inserted AS i ON p.ID = i.ID 
DECLARE @uName char(30);
SELECT @uName = SYSTEM_USER
INSERT tblOperationLog 
 Values 
 ( @uName, 'user has inserted a row with ID = ' + CONVERT(nvarchar, @ID) + '', 
    'Insert', CURRENT_TIMESTAMP, GETDATE() )

我想编写触发器并使用已删除的表，就像插入的表一样。但我不知道怎么做。我想检索已删除行的 ID 以填充 tblOperationLog 的第二列，但我不能。我也应该使用内部连接吗？

原文

I want to write triggers to work with the inserted and deleted tables. I have written the trigger for inserting :

CREATE TRIGGER FILL_TABLE
ON Person FOR INSERT
AS
DECLARE @ID int
SELECT @ID = p.ID
FROM Person AS p 
    INNER JOIN inserted AS i ON p.ID = i.ID 
DECLARE @uName char(30);
SELECT @uName = SYSTEM_USER
INSERT tblOperationLog 
 Values 
 ( @uName, 'user has inserted a row with ID = ' + CONVERT(nvarchar, @ID) + '', 
    'Insert', CURRENT_TIMESTAMP, GETDATE() )

I want to write the trigger and use the deleted table just like the inserted one. but I don't know how. I want to retrieve the ID of the deleted rows to fill the second column of the tblOperationLog but I can't. Should I use the inner join in it too?

分享到QQ

分享到微博

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

发布评论

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

魂牵梦绕锁你心扉 2024-09-18 14:41:29

仅当仅插入一行时，您的触发器才会起作用。由于 inserted 可用作表，因此您可以从该表插入并使用所有记录。我想你正在寻找这样的东西：

CREATE TRIGGER FILL_TABLE 
ON Person FOR INSERT, DELETE
AS 

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has inserted a row with ID = ' + ID, 'Insert', 
    CURRENT_TIMESTAMP, getdate()
  FROM inserted

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has deleted a row with ID = ' + ID, 'Delete', 
    CURRENT_TIMESTAMP, getdate()
  FROM deleted

Your trigger will only work if only one row has been inserted. As inserted is available as a table, you can insert from that table and use all records. I think you're looking for something like this:

CREATE TRIGGER FILL_TABLE 
ON Person FOR INSERT, DELETE
AS 

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has inserted a row with ID = ' + ID, 'Insert', 
    CURRENT_TIMESTAMP, getdate()
  FROM inserted

  INSERT tblOperationLog  
  SELECT  SYSTEM_USER,'user has deleted a row with ID = ' + ID, 'Delete', 
    CURRENT_TIMESTAMP, getdate()
  FROM deleted

回复收藏 0 原文