sqlserver触发器的使用

在sqlserver2008中由于服务器网络设置需要，部署了两个结构相同的数据库，并要求对这两个数据库中其中任何一个库做任何更改均要同步到另一个库中

所以就在两个库分别创建了触发器，以便同步

------------------------------------------------------------------------------

如库1为hmi_Yanke，库2为hmi_Yanke2，两个库都存在表TDisplay

则在库1创建触发器如下

--创建[hmi_Yanke].[dbo].[TDisplay]表的触发器
create trigger [dbo].[trigger_TDisplay]
on [HMI_YanKe2].[dbo].[TDisplay]
AFTER INSERT,UPDATE,DELETE
as 
begin
declare @RowsD Int,@RowsI Int,@Dml Int
--确定是哪一种dml操作
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI=0  
Goto Exit_
If @RowsD=0 And @RowsI>0
    Set @Dml=1
Else If @RowsD>0 And @RowsI>0
    Set @Dml=2
Else If @RowsD>0 And @RowsI=0
    Set @Dml=3 
IF @DML=1
BEGIN
--插入
    IF NOT EXISTS(SELECT TOP 1 1 FROM [hmi_Yanke].[dbo].[TDisplay] c,inserted i where  c.FID=i.FID)
    begin 
    set IDENTITY_INSERT [hmi_Yanke].[dbo].[TDisplay] ON 
    insert into [hmi_Yanke].[dbo].[TDisplay]( [FID],FFieldCN,FFieldEN,FTable,FOrder,FType,FNullable,FLength,FWidth)
    select [FID],FFieldCN,FFieldEN,FTable,FOrder,[FType],FNullable,FLength,FWidth from inserted
    set IDENTITY_INSERT [hmi_Yanke].[dbo].[TDisplay] OFF
    end
END
IF @DML=2
begin 
    --修改
    IF NOT EXISTS
    (SELECT TOP 1 1 FROM [hmi_Yanke].[dbo].[TDisplay] c,INSERTED i 
        where c.[FID]=i.[FID] and c.FFieldCN=i.FFieldCN and c.FFieldEN=i.FFieldEN and c.FTable=i.FTable 
        and c.FOrder=i.FOrder and c.[FType]=i.[FType] and c.FNullable=i.FNullable and c.FLength=i.FLength 
        and c.FWidth=i.FWidth )
    UPDATE [hmi_Yanke].[dbo].[TDisplay] set FFieldCN=i.FFieldCN,FFieldEN=i.FFieldEN,FTable=i.FTable,FOrder=i.FOrder,[FType]=i.[FType],FNullable=i.FNullable,FLength=i.FLength,FWidth=i.FWidth
        FROM [hmi_Yanke].[dbo].[TDisplay] c,INSERTED i where c.[FID]=i.[FID]
    --select * from INSERTED
    --select * from deleted
end
IF @DML=3
    BEGIN
    --删除
        IF  EXISTS(SELECT TOP 1 1 FROM  [hmi_Yanke].[dbo].[TDisplay] c,deleted d where  c.[FID]=d.[FID]) 
        DELETE [hmi_Yanke].[dbo].[TDisplay] FROM [hmi_Yanke].[dbo].[TDisplay] c,deleted d 
        WHERE c.[FID]=d.[FID] 
    END
EXIT_: 
end

在库2创建触发器与上面相同，只需要将hmi_Yanke改为hmi_Yanke2即可

简单测试后得到正确结果

  INSERT INTO [HMI_YanKe].[dbo].[TDisplay] values('haha','haha','haha',1,'haha',1,1,1),('hehe','hehe','hehe',2,'hehe',2,2,2)
  INSERT INTO [HMI_YanKe2].[dbo].[TDisplay] values('xixi','xixi','xixi',1,'xixi',1,1,1),('keng','keng','keng',2,'keng',2,2,2)

  update [HMI_YanKe].[dbo].[TDisplay] set FFieldCN='vinjack' where FFieldCN='haha'
  update [HMI_YanKe2].[dbo].[TDisplay] set FFieldCN='vinjane' where FFieldCN='xixi'

  delete [HMI_YanKe].[dbo].[TDisplay] where FFieldCN in('vinjack','hehe')
  delete [HMI_YanKe2].[dbo].[TDisplay] where FFieldCN in('vinjane','keng')
  
  select *  FROM [HMI_YanKe].[dbo].[TDisplay]
  select *  FROM [HMI_YanKe2].[dbo].[TDisplay]