sql数据库触发器实时备份

sql触发器实现日志表数据实时备份

创建两个结构一样的表

CREATE TABLE Log(
[id] varchar NOT NULL,
[name] varchar NULL,
[dept] varchar NULL,
[company] varchar NULL
) ON [PRIMARY]
GO
CREATE TABLE Logback(
[id] varchar NOT NULL,
[name] varchar NULL,
[dept] varchar NULL,
[company] varchar NULL
) ON [PRIMARY]
GO

创建触发器

–如果存在同名触发器则删除
if exists(select * from sys.triggers where name= ‘trigger_insert’)
drop trigger trigger_insert
go

–创建insert触发器
create trigger trigger_insert
ON Log for insert
as
insert into Logback
select * from Log
where not exists(select 1 from Logback
where Log.id = Logback.id)
go

–创建update触发器
create trigger trigger_update
ON Log for update
as
if update(id)
begin
update Logback set Logback.name = inserted.name from Logback,inserted where inserted.id=Logback.id
end
go

–创建delete触发器
create trigger trigger_delete
ON Log for delete
as
begin
delete Logback from Logback,deleted where deleted.id=Logback.id
end
go

转载：链接: https://blog.csdn.net/TheFallen/article/details/96986126.