1，对于表的更新，插入，删除可以定义触发器。通常对于更新/插入/删除，对于某一情况，只能定义一个触发器，也就是说，对于更新可以有一个触发器，插入一个触发器，删除一个触发器，总共最多三个。
2，实际上在修改记录时，以前的内容保存在deleted表中，新的内容保存在inserted表中。
3，更新时，定义如下
create trigger tri_bugs_updatestatus on bugs instead of update
as
4，之后就是触发器内容了，如果有变量，先定义：
DECLARE @mybugid int,@myuserid int
5，对于使用if else分支，每个分支需要begin end来表示范围。例如：
if exists(select 1 from inserted where bg_status=7 and [Finished Date] is not null //inserted表示将更新的值，取出判断 and 。。。。 ） begin update bugs set //更新实际表 bg_short_desc=inserted.bg_short_desc, 。。。。。。 [Finished Date]=inserted.[Finished Date] from inserted where bugs.bg_id = inserted.bg_id select @mybugid=myinserted.bg_id,@myuserid=myinserted.bg_last_updated_user from inserted as myinserted //将数据放到变量里 delete from bugmsg where bugid <> 1 and userid = @myuserid insert into bugmsg(bugid,bugmessage,userid) values(@mybugid,'Issue updated',@myuserid) //将变量插入到另外一个表
end
6，回滚：ROLLBACK TRANSACTION
这个例子：
Create trigger tri_bugs_updatestatus on bugs instead of update as DECLARE @mybugid int,@myuserid int if exists(select 1 from deleted where bg_status=7) begin ROLLBACK TRANSACTION select @mybugid=myinserted.bg_id,@myuserid=myinserted.bg_last_updated_user from inserted as myinserted delete from bugmsg where bugid <> 1 and userid = @myuserid insert into bugmsg(bugid,bugmessage,userid) values(@mybugid,'Issue not updated because issue locked when its Status is Closed',@myuserid) end else if exists(select 1 from inserted where bg_status=7 and [Finished Date] is not null and ((bg_category in (6,7) and [Application Name] ='Axapta' and Modules != 'None') or (bg_category in (6,7) and [Application Name] ='SharePoint' and Modules = 'None') or (bg_category in (6,7) and [Application Name] ='MicroSoft Office' and Modules = 'None') or (bg_category not in (6,7) and [Application Name] ='None' and Modules = 'None'))) begin update bugs set bg_short_desc=inserted.bg_short_desc, bg_reported_user=inserted.bg_reported_user, bg_reported_date=inserted.bg_reported_date, bg_status=inserted.bg_status, bg_priority=inserted.bg_priority, bg_org=inserted.bg_org, bg_category=inserted.bg_category, bg_project=inserted.bg_project, bg_assigned_to_user=inserted.bg_assigned_to_user, bg_last_updated_user=inserted.bg_last_updated_user, bg_last_updated_date=inserted.bg_last_updated_date, bg_user_defined_attribute=inserted.bg_user_defined_attribute, [Application Name]=inserted.[Application Name], Weighting=inserted.Weighting, Modules=inserted.Modules, [Preferred Engineer]=inserted.[Preferred Engineer], Owner=inserted.Owner, Detail=inserted.Detail, Solution=inserted.Solution, [Finished Date]=inserted.[Finished Date] from inserted where bugs.bg_id = inserted.bg_id select @mybugid=myinserted.bg_id,@myuserid=myinserted.bg_last_updated_user from inserted as myinserted delete from bugmsg where bugid <> 1 and userid = @myuserid insert into bugmsg(bugid,bugmessage,userid) values(@mybugid,'Issue updated',@myuserid)
end