類似于這樣: USE test GO --IF object_id('tr') IS NOT NULL -- DROP TABLE tr --Go CREATE TRIGGER tr ON A FOR INSERT,UPDATE,DELETE ASIF EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED) UPDATE B SET cardno=i.cardno ,name=i.name FROM B INNER JOIN INSERTED AS i ON B.id=i.id
ELSE IF EXISTS(SELECT 1 FROM INSERTED) BEGIN
SET IDENTITY_INSERT B ON INSERT INTO B(id,cardno,name) SELECT id,cardno,name FROM INSERTED SET IDENTITY_INSERT B OFF
ENDELSE DELETE B FROM B INNER JOIN DELETED AS d ON b.id=d.id
GO
沒有簡單的了,除非你要分3個觸發器來分別觸發3種動作.USE test GO --IF object_id('tr') IS NOT NULL -- DROP TABLE tr --Go CREATE TRIGGER tr ON A FOR INSERT,UPDATE,DELETE AS-- 判断Inserted表(记录表A插入的数据)是否有数据 -- ,再判断Deleted表(记录表A删除的数据)是否有数据 -- ,如果两个都有数据就表示这个是属于修改动作 -- (注意:修改动作是先删除该数据然后再插入新的数据) IF EXISTS(SELECT 1 FROM INSERTED) AND EXISTS(SELECT 1 FROM DELETED)
-- 匹配表B和Inserted表,更新表B数据 UPDATE B SET cardno=i.cardno ,name=i.name FROM B INNER JOIN INSERTED AS i ON B.id=i.id-- 判断Inserted表(记录当前每次插入的数据)是否有数据 -- 如果Inserted表有数据就表示这个是属于插入动作 -- 此时不可能存在Inserted有数据,Deleted又有数据 ELSE IF EXISTS(SELECT 1 FROM INSERTED) BEGIN
SET IDENTITY_INSERT B ON -- 因为表B的id字段是自增列,需要开启 Identity_Insert 才能在id字段插入我们想插入的值 INSERT INTO B(id,cardno,name) -- 插入表A已插入的数据 SELECT id,cardno,name FROM INSERTED SET IDENTITY_INSERT B OFF -- 关闭表B的 Identity_Insert
END-- 如果前面两个判断不成立,那么就只剩下删除的动作 ELSE DELETE B -- 删除表A已删除的数据 FROM B INNER JOIN DELETED AS d ON b.id=d.id
USE test
GO
--IF object_id('tr') IS NOT NULL
-- DROP TABLE tr
--Go
CREATE TRIGGER tr
ON A
FOR INSERT,UPDATE,DELETE
ASIF EXISTS(SELECT 1 FROM INSERTED)
AND EXISTS(SELECT 1 FROM DELETED)
UPDATE B
SET cardno=i.cardno
,name=i.name
FROM B
INNER JOIN INSERTED AS i ON B.id=i.id
ELSE IF EXISTS(SELECT 1 FROM INSERTED)
BEGIN
SET IDENTITY_INSERT B ON INSERT INTO B(id,cardno,name)
SELECT
id,cardno,name
FROM INSERTED SET IDENTITY_INSERT B OFF
ENDELSE
DELETE B
FROM B
INNER JOIN DELETED AS d ON b.id=d.id
GO
GO
--IF object_id('tr') IS NOT NULL
-- DROP TABLE tr
--Go
CREATE TRIGGER tr
ON A
FOR INSERT,UPDATE,DELETE
AS-- 判断Inserted表(记录表A插入的数据)是否有数据
-- ,再判断Deleted表(记录表A删除的数据)是否有数据
-- ,如果两个都有数据就表示这个是属于修改动作
-- (注意:修改动作是先删除该数据然后再插入新的数据)
IF EXISTS(SELECT 1 FROM INSERTED)
AND EXISTS(SELECT 1 FROM DELETED)
-- 匹配表B和Inserted表,更新表B数据
UPDATE B
SET cardno=i.cardno
,name=i.name
FROM B
INNER JOIN INSERTED AS i ON B.id=i.id-- 判断Inserted表(记录当前每次插入的数据)是否有数据
-- 如果Inserted表有数据就表示这个是属于插入动作
-- 此时不可能存在Inserted有数据,Deleted又有数据
ELSE IF EXISTS(SELECT 1 FROM INSERTED)
BEGIN
SET IDENTITY_INSERT B ON -- 因为表B的id字段是自增列,需要开启 Identity_Insert 才能在id字段插入我们想插入的值 INSERT INTO B(id,cardno,name) -- 插入表A已插入的数据
SELECT
id,cardno,name
FROM INSERTED SET IDENTITY_INSERT B OFF -- 关闭表B的 Identity_Insert
END-- 如果前面两个判断不成立,那么就只剩下删除的动作
ELSE
DELETE B -- 删除表A已删除的数据
FROM B
INNER JOIN DELETED AS d ON b.id=d.id
GO
不复杂,你要先搞懂inserted和deleted表