SQL插入触发器示例

[CL_csdn_]

create trigger [dbo].[Tig_dfMES_Insert]
on [dbo].[dfMES] after insert --插入类型
as
declare @vis varchar(8)
declare @newvid varchar(20)
--如果插入VID码为空或者NULL并且VIN码不为空。
if(((select VID码 from inserted)='' or (select VID码 from inserted) is NULL) and (select VIN码 from inserted)!='' and (select VIN码 from inserted) is not NULL)
begin
SET @vis= (select right(VIN码,8) from inserted) --得到后8位VIS
set @newvid = (select TOP 1 VID码 from dbo.dfmes_bak WHERE VIN码 like '%'+@vis)--获取VID码
UPDATE dfMES SET VID码=@newvid WHERE VID码=(SELECT VID码 FROM inserted)--更新语句
end
else
begin
insert into dbo.dfmes_bak select * from inserted--插入表数据
end