//

sql server 触发器 往一个表中插入数据同时向另一张表中插入数据

Sql Server触发器，往一个表中插入数据 分类:

表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'

?

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

?

?

?