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'

set ANSI_NULLS ON<br />set QUOTED_IDENTIFIER ON<br />GO<br />ALTER TRIGGER [dbo].[Delta_User2_User1]<br />ON [dbo].[User1]<br />FOR INSERT, UPDATE, DELETE<br />AS<br />IF EXISTS (SELECT A.UserName FROM inserted A, deleted B WHERE A.UserName = B.UserName)<br />BEGIN<br />IF EXISTS (SELECT Delta2_User1.UserName FROM Delta2_User1, inserted WHERE Delta2_User1.UserName = inserted.UserName )<br />DELETE Delta2_User1 FROM Delta2_User1, inserted WHERE Delta2_User1.UserName = inserted.UserName;<br />INSERT INTO Delta2_User1 SELECT ID, UserName, Email, Address, 'Modify' FROM inserted;<br />END </p><p>ELSE<br />BEGIN<br />IF EXISTS (SELECT UserName FROM inserted)<br />BEGIN<br />INSERT INTO Delta2_User1 SELECT ID, UserName, Email, Address, 'Insert' FROM inserted;<br />END<br />ELSE<br />BEGIN<br />UPDATE Delta2_User1 set status = 'Delete' FROM Delta2_User1, deleted where Delta2_User1.UserName = deleted.UserName;<br />END<br />END