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;