本文介绍了一个针对Table1的AFTER触发器，它在insert、delete、update操作后自动记录变化，包括新插入、更新和删除的数据，并根据操作类型将最新或唯一信息存入Table2，确保数据一致性。
CREATE TRIGGER [Triger_Table1] ON [Table1]
--After触发器，对表进行insert、delete、update后触发
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
BEGINTRYBEGIN TRAN;DECLARE @maxID NVARCHAR(50) ,@inserted INT,@deleted INT,@ChangeType NVARCHAR(20);SELECT @inserted = COUNT(1)FROMInserted;SELECT @deleted = COUNT(1)FROMDeleted;--判断对表Table1的操作类型
IF @inserted > 0
AND @deleted = 0
BEGIN
SET @ChangeType = 'INSERT';END;ELSE
IF @inserted > 0
AND @deleted > 0
BEGIN
SET @ChangeType = 'UPDATE';END;ELSE
IF @inserted = 0
AND @deleted > 0
BEGIN
SET @ChangeType = 'DELETE';END;IF @ChangeType = 'DELETE'
BEGIN
SELECT @maxID =IDFROMDeleted;--如果对Table1同一条数据，进行多次操作，则Table2只保存最新数据
IF EXISTS ( SELECT COUNT(1)FROMTable2WHERE ID = @maxID)BEGIN
DELETE FROMTable2WHERE ID = @maxID;END;INSERT INTOTable2
(
ID,
Name,
Sex,
Address,
Age,
Birthday,
ChangeType
)SELECTID,
Name,
Sex,
Address,
Age,
Birthday,@ChangeType ASChangeTypeFROMDeleted;END;ELSE
BEGIN
SELECT @maxID =IDFROMInserted;IF EXISTS ( SELECT COUNT(1)FROMTable2WHERE ID = @maxID)BEGIN
DELETE FROMTable2WHERE ID = @maxID;END;INSERT INTOTable2
(
ID,
Name,
Sex,
Address,
Age,
Birthday,
ChangeType
)SELECTID,
Name,
Sex,
Address,
Age,
Birthday,@ChangeType ASChangeTypeFROMInsertedEND;COMMIT TRAN;ENDTRYBEGINCATCHIF XACT_STATE() = -1
ROLLBACK TRAN;ENDCATCH;END;
501
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
