语句格式

--创建触发器
create trigger trigger_name
　　on {table_name | view_name}
　　{for | After | Instead of }
　　[ insert, update,delete ]
　　as
　　sql_statement

--修改触发器
alter trigger trigger_name
　　on {table_name | view_name}
　　{for | After | Instead of }
　　[ insert, update,delete ]
　　as
　　sql_statement

--删除触发器
drop trigger trigger_name

实例

--查询系统中已有的触发器
select * from sysobjects where xtype='TR'

--查看触发器语句
exec sp_helptext 'cfq1'

--创建DML触发器更新
create trigger cfq1 
on CBO_ItemMaster
for update
as
print '触发器更新测试'

--修改DML触发器
alter trigger cfq1 
on CBO_ItemMaster
for update
as
update CBO_ItemMaster set DescFlexField_PrivateDescSeg1='触发器更新' where id=1002204020110444

--删除DML触发器
drop trigger cfq1 

--更新语句激活触发器
update CBO_ItemMaster set SPECS='测试规格',DescFlexField_PrivateDescSeg1='手动更新' where id=1002204020110444

--查询触发器结果
select code,SPECS,DescFlexField_PrivateDescSeg1,* from CBO_ItemMaster where id=1002204020110444

--创建插入触发器，把最新创建的料品料号写入私有段1
create trigger cfq2
on CBO_ItemMaster
for insert
as
declare @code char(20)=(select top 1 code from CBO_ItemMaster order by CreatedOn desc)
update CBO_ItemMaster set DescFlexField_PrivateDescSeg1=@code where Code=@code

--前台插入料品后触发，查询语句看结果
select CreatedBy,ID,code,DescFlexField_PrivateDescSeg1,* from CBO_ItemMaster order by CreatedOn desc

--删除料品
DELETE FROM CBO_ItemMaster WHERE code='cs0824'