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
