sql server 触发器实时同步数据库表数据
创建两个相同结构的数据库表
CREATE TABLE [dbo].[Table_1]([id] [varchar](50) NOT NULL,[name] [varchar](50) NULL,[dept] [varchar](50) NULL,[company] [varchar](50) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table_2]([id] [varchar](50) NOT NULL,[name] [varchar](50) NULL,[dept] [varchar](50) NULL,[company] [varchar](50) NULL) ON [PRIMARY]GO
创建触发器
向数据库表Tabel1增加触发器,当该表中数据发生变化则触发相应的触发器,分别是insert、update、delete
检查触发器是否已存在
--如果存在同名触发器则删除if exists(select * from sys.triggers where name= 'trigger_insert')drop trigger trigger_insertgo
--创建insert触发器create trigger trigger_insertON Table_1 for insertasinsert into Table_2 select * from [Table_1]where not exists(select 1 from [Table_2]where [Table_1].id = [Table_2].id)go
--创建update触发器create trigger trigger_updateON Table_1 for updateasif update(id)begin update Table_2 set Table_2.name = inserted.name from Table_2,inserted where inserted.id=Table_2.idendgo
--创建delete触发器create trigger trigger_deleteON Table_1 for deleteasbegin delete Table_2 from Table_2,deleted where deleted.id=Table_2.idendgo
测试触发器
当向数据库表Tabel1中插入、更新和删除数据时,将触发相应的触发器,对Table2表进行实时同步更新