SQL触发器学习

ehxz

免费乐捐，感谢支持

语句格式

1. --创建触发器
   
2. create trigger trigger_name
   
3. 　　on {table_name | view_name}
   
4. 　　{for | After | Instead of }
   
5. 　　[ insert, update,delete ]
   
6. 　　as
   
7. 　　sql_statement
   
8. 
   
9. --修改触发器
   
10. alter trigger trigger_name
    
11. 　　on {table_name | view_name}
    
12. 　　{for | After | Instead of }
    
13. 　　[ insert, update,delete ]
    
14. 　　as
    
15. 　　sql_statement
    
16. 
    
17. --删除触发器
    
18. drop trigger trigger_name

复制代码

实例

1. 
   
2. --查询系统中已有的触发器
   
3. select * from sysobjects where xtype='TR'
   
4. 
   
5. --查看触发器语句
   
6. exec sp_helptext 'cfq1'
   
7. 
   
8. --创建DML触发器更新
   
9. create trigger cfq1
   
10. on CBO_ItemMaster
    
11. for update
    
12. as
    
13. print '触发器更新测试'
    
14. 
    
15. --修改DML触发器
    
16. alter trigger cfq1
    
17. on CBO_ItemMaster
    
18. for update
    
19. as
    
20. update CBO_ItemMaster set DescFlexField_PrivateDescSeg1='触发器更新' where id=1002204020110444
    
21. 
    
22. --删除DML触发器
    
23. drop trigger cfq1
    
24. 
    
25. --更新语句激活触发器
    
26. update CBO_ItemMaster set SPECS='测试规格',DescFlexField_PrivateDescSeg1='手动更新' where id=1002204020110444
    
27. 
    
28. --查询触发器结果
    
29. select code,SPECS,DescFlexField_PrivateDescSeg1,* from CBO_ItemMaster where id=1002204020110444
    
30. 
    
31. --创建插入触发器，把最新创建的料品料号写入私有段1
    
32. create trigger cfq2
    
33. on CBO_ItemMaster
    
34. for insert
    
35. as
    
36. declare @code char(20)=(select top 1 code from CBO_ItemMaster order by CreatedOn desc)
    
37. update CBO_ItemMaster set DescFlexField_PrivateDescSeg1=@code where Code=@code
    
38. 
    
39. --前台插入料品后触发，查询语句看结果
    
40. select CreatedBy,ID,code,DescFlexField_PrivateDescSeg1,* from CBO_ItemMaster order by CreatedOn desc
    
41. 
    
42. --删除料品
    
43. DELETE FROM CBO_ItemMaster WHERE code='cs0824'

复制代码