如果我正在使用一个触发器来触发我的付款表的每次更新或删除,如何确定已发生的触发器类型(如果我需要进一步解释,因为触发器功能由于更新记录或删除记录而调用。)因为我需要将该信息存储在另一个表格中。
Instruction: when you insert data into table, only 'inserted' has the new inserted rows; when you delete data from table, only 'deleted' has the deleted rows; when you update table, the 'inserted' saves the new rows, the 'deleted' saves the old rows.
I think this sample can give you a hint. (SQL Server 2012)
Sample:
Sample Table Definition:
create table Customer (
ID int identity(1,1) not null,
FirstName varchar(30),
LastName varchar(30))
Trigger:
CREATE TRIGGER TrackAction
ON Customer
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @DELETEDCOUNT INT
DECLARE @INSERTEDCOUNT INT
SELECT @DELETEDCOUNT = COUNT(<em>) FROM deleted
SELECT @INSERTEDCOUNT = COUNT(</em>) FROM inserted
IF(@DELETEDCOUNT&@INSERTEDCOUNT > 0 )
PRINT 'Trigger by update action'
ELSE IF (@DELETEDCOUNT > 0 )
PRINT 'Trigger by delete action'
ELSE IF (@INSERTEDCOUNT > 0 )
PRINT 'Trigger by insert action'
END
Test code:
insert into Customer
values ('Bob','Ryan')
update customer
set FirstName = 'Bob Jr.'
where FirstName = 'Bob'
delete customer
where FirstName = 'Bob Jr.'
Test Result:MS SQL Server?
不需要。
在SQL中有一些单个命令可以同时导致更新、插入和删除(例如MERGE)。触发器将被调用一次,并且[deleted]、[inserted]和[updated]伪表都将被填充。谈论哪一个发生了是没有意义的。
COUNT(*) FROM INSERTED
和 COUNT(*) FROM DELETED
,你可以通过查看消息输出(至少在 SSMS 中)来查看触发器的触发顺序以及触发它的是插入、更新还是删除操作。插入操作将在 DELETED 中具有 COUNT(*)=0
,删除操作将在 INSERTED 中具有 COUNT(*)=0
,而更新操作将在 INSERTED 和 DELETED 中都具有 COUNT(*)>0
。