AFTER UPDATE,在更新以后操作,详见联机说明
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
如果你的业务逻辑处理起来比较复杂,可以在触发器中用游标遍历inserted/deleted的方式逐个处理。
select @RequireID_old=RequireID,@RequireCode_old=RequireCode,@BUCode_old=BUCode,@DeptID_old=DeptID,@PositionSerialCode_old=PositionSerialCode,@PostCode_old=PostCode,@GradeCodeMin_old=GradeCodeMin,@GradeCodeMax_old=GradeCodeMax,@RequireNumber_old=RequireNumber, @IsInProject_old=IsInProject, @ApproveCompleteTime_old=ApproveCompleteTime from deleted你這麼寫,批量更新的時候,就只能得到一條紀錄的值,那麼“觸發器只影響了一條數據”。如果你的邏輯複雜的話,用游標逐條讀出inserted和inserted的數據,然後一條條做處理。
oracle里有行级触发器,sqlserver里没有,只好使用游标遍历幻表
delete时,deleted有行,inserted没有行也可以分别建insert和delete触发器
create trigger ti_表 on 表
for insert
as
...create trigger td_表 on 表
for delete
as
...
---------------------------------------------------------------
判断RequireCode是否被更改
where exists(select 1 from inserted i,deleted d where i.RequireID=d.RequireID and isnull(i.RequireCode,'')<>isnull(d.RequireCode,''))