触发器实现两张表同步
//
sql server 触发器 往一个表中插入数据同时向另一张表中插入数据
Sql Server触发器，往一个表中插入数据 分类:
www.2cto.com
表User1中有字段ID, UserName, Email, Address。
表Delta2_User1中有字段ID, UserName, Email, Address, Status
当表User1插入一条记录，在Delta2_User1中也插入一条记录，并在字段Status中写入'Insert'
当表User1中更新一条记录，在Delta2_User1中删除原有的记录，并插入新的记录，在字段Status中写入'Modify'
当表User1中删除一条记录，在Delta2_User1中更新相应字段Status的值为'Delete'
www.2cto.com
ALTER TRIGGER [dbo].[Delta_User2_User1]
ON [dbo].[User1]
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS (SELECT A.UserName FROM inserted A, deleted B WHERE A.UserName = B.UserName)
BEGIN
IF EXISTS (SELECT Delta2_User1.UserName FROM Delta2_User1, inserted WHERE Delta2_User1.UserName = inserted.UserName )
DELETE Delta2_User1 FROM Delta2_User1, inserted WHERE Delta2_User1.UserName = inserted.UserName;
INSERT INTO Delta2_User1 SELECT ID, UserName, Email, Address, 'Modify' FROM inserted;
END
ELSE
BEGIN
IF EXISTS (SELECT UserName FROM inserted)
BEGIN
INSERT INTO Delta2_User1 SELECT ID, UserName, Email, Address, 'Insert' FROM inserted;
END
ELSE
BEGIN
UPDATE Delta2_User1 set status = 'Delete' FROM Delta2_User1, deleted where Delta2_User1.UserName = deleted.UserName;
END
END
//例子
CREATE TRIGGER partinfo_insert_trigger_tgl
ON fwerp.dbo.partinfo
FOR INSERT
as
INSERT INTO piend.dbo.PartInfo(FPartCode,FPartName,dpname,caddr,tel,chuanzhen,statu)
SELECT ins.FPartCode,ins.FPartName,ins.FPartName,
ins.FAddr,ins.FPhone,ins.Fax,0
FROM inserted ins