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]
GO
CREATE 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_insert
go
--创建insert触发器
create trigger trigger_insert
ON Table_1 for insert
as
insert 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_update
ON Table_1 for update
as
if update(id)
begin
update Table_2 set Table_2.name = inserted.name from Table_2,inserted where inserted.id=Table_2.id
end
go
--创建delete触发器
create trigger trigger_delete
ON Table_1 for delete
as
begin
delete Table_2 from Table_2,deleted where deleted.id=Table_2.id
end
go
测试触发器
当向数据库表Tabel1中插入、更新和删除数据时,将触发相应的触发器,对Table2表进行实时同步更新