在触发器中使用插入和删除的表 [英] using inserted and deleted tables in the triggers
问题描述
我想编写触发器来处理插入和删除的表。我已经编写了插入触发器:
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() )
我想编写触发器并就像插入的表一样使用删除的表。但我不知道如何我想检索已删除行的ID,以填充tblOperationLog的第二列,但不能。我也应该在其中使用内部联接吗?
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?
推荐答案
仅当插入一行时,您的触发器才会起作用。由于已插入
可作为表使用,因此您可以从该表中插入并使用所有记录。我认为您正在寻找这样的东西:
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
这篇关于在触发器中使用插入和删除的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!