【sqlserver触发器】当数据库的更新满足某个条件时就执行某些语句

因为erp系统存在某些缺陷，向数据库插入某些数据时存在某些数据有字段为空的情况，今天学习了一下用触发器来解决这些问题：

--要更新先删除原来的
Drop TRIGGER [dbo].[Feedback_Insert]
go
Create TRIGGER [dbo].[Feedback_Insert] ON [dbo].[XCX_feedback]
AFTER INSERT
AS
BEGIN
	DECLARE @IcomID bigint,@cSta nvarchar,@cName nvarchar;
	select @IcomID=iCompanyID,@cSta=cState,@cName=cCustomerName From inserted
	if(@IcomID is null)
		begin
			commit transaction
			update XCX_feedback set cState='未解决' where cState is NULL
			update XCX_feedback set iCompanyID=b.iCompanyAutoID  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.iCompanyID is null
			update XCX_feedback set cCustomerName=b.cCustomerName  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.cCustomerName is null
			end
	else if(@cSta is null)
		begin
			commit transaction
			update XCX_feedback set cState='未解决' where cState is NULL
			update XCX_feedback set iCompanyID=b.iCompanyAutoID  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.iCompanyID is null
			update XCX_feedback set cCustomerName=b.cCustomerName  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.cCustomerName is null
			end
	else if(@cName is null)
		begin
			commit transaction
			update XCX_feedback set cState='未解决' where cState is NULL
			update XCX_feedback set iCompanyID=b.iCompanyAutoID  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.iCompanyID is null
			update XCX_feedback set cCustomerName=b.cCustomerName  from XCX_feedback a inner join Customer b  on a.cCustomerGUID=b.GUID where a.cCustomerName is null
			end
	else
		begin
			commit transaction
			end
end

这是调试过后的

因为开始已经创建了这个触发器然后又有修改，所以直接在第一句加上了drop语句

然后就是创建触发器：create TRIGGER 触发器名称 ON 表名 AFTER 操作（INSERT,UPTATE,DELETE）

然后是定义用作条件判断的字段，本地数据库的话可以用exec sp_columns 表名来查表结构，就可以知道字段的类型

然后就是判断条件并做出动作，因为用了触发器以后，数据会在临时表inserted中备份，用于呗回滚和提交，因为不用做回滚操作，所以直接提交，然后执行需要执行的语句，不满足这三个条件则直接提交

UPDATE的触发器

drop TRIGGER [dbo].[BillBusInsert]
go
create TRIGGER [dbo].[BillBusInsert] ON [dbo].[BillBusMain]
AFTER UPDATE AS
BEGIN
	DECLARE @ISCState int, @UpFinish bit;
	SELECT @ISCState=iScheduleSate FROM inserted
	SELECT @UpFinish=bUploadFinish FROM deleted
	if(@ISCState='6' and @UpFinish='0')
		begin
			commit transaction
			update BillBusMain set bUploadFinish=1 ,iScheduleSate=2 ,bInputFinish=0 where bUploadFinish=0 and  iScheduleSate=6
		end
	else if(@ISCState='7' and @UpFinish='0')
		begin
			commit transaction
			update BillBusMain set bUploadFinish=1 ,iScheduleSate=2 ,bInputFinish=0 where bUploadFinish=0 and  iScheduleSate=7
		end
	else if(@ISCState=6 and @UpFinish=0)
		begin
			commit transaction
			update BillBusMain set bUploadFinish=1 ,iScheduleSate=2 ,bInputFinish=0 where bUploadFinish=0 and  iScheduleSate=6
		end
	else if(@ISCState=7 and @UpFinish=0)
		begin
			commit transaction
			update BillBusMain set bUploadFinish=1 ,iScheduleSate=2 ,bInputFinish=0 where bUploadFinish=0 and  iScheduleSate=7
		end
	else
		begin
			commit transaction
		end
end

含以上都差不多，因为update是先删除后插入，所以临时表inserted是新数据表，deleted是老数据表