SQL Server一个触发器实现insert update delete 示例代码
代码可以直接拿来使用
-- =============================================
-- Author:微奇奇
-- Create date: 2017.10.11
-- Description:xx系统API数据同步队列
-- =============================================
Create TRIGGER [dbo].[TR_ZZV_TABLE]
ON [dbo].[ZZV_TABLE]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
if exists(select 1 from inserted) and exists(select 1 from deleted)--update记录更新操作
begin
if(update(fstatus) or update(fname))--Column update 判断只在某些列变动后触发
begin
INSERT INTO [ZZV_Sync]([faddtime],[fstatus])
select getdate(),fstatus from inserted where ftype=-10
end
end
else if exists(select 1 from inserted) and not exists(select 1 from deleted) --insert 新增操作
begin
INSERT INTO [ZZV_Sync]([faddtime],[fstatus])
select getdate(),fstatus from inserted where ftype=-10
end
else if not exists(select 1 from inserted) and exists(select 1 from deleted) --delete
begin
INSERT INTO [ZZV_Sync]([faddtime],[fstatus])
select getdate(),fstatus from inserted where ftype=-10
end
END