语句格式
--创建触发器
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'
