1.  
  2. ---触发器 一种特殊的存储过程,触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用  
  3. --触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器  
  4. --SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。  
  5.  
  6. --DML 触发器分为    
  7.  
  8.   --1、 after触发器（之后触发)   
  9.  
  10.   --      a、 insert触发器   
  11.  
  12.   --      b、 update触发器   
  13.  
  14.   --      c、 delete触发器   
  15.  
  16.     --PS：其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上  
  17.  
  18.   --  2、 instead of 触发器 （之前触发）   
  19.  
  20. --PS:而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义  
  21.  
  22.   --触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。  
  23.   --有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。  
  24.   --这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。  
  25.   --Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。  
  26.  
  27.  
  28. --create trigger trg_name  
  29. --on table_name  
  30. --with encryption --加密触发器  
  31. --   for update......  
  32. --as   
  33. --  Transact-SQL  
  34.  
  35. ---创建Insert插入类型触发器  
  36. if(OBJECT_ID ('trg_insert','tr')is not null)  
  37.     drop trigger trg_insert   
  38. go  
  39. create trigger trg_insert  
  40. on student2  
  41.     for insert --插入触发 (向student2插入记录，会插入相同的name 到student 表中)  
  42. as   
  43.     --定义变量  
  44.     declare @id int,@name varchar(20),@sex int;  
  45.     select @idid=id,@namename=name from inserted;--从inserted 表中取出插入的记录  
  46.     set @name=@name+CONVERT(varchar,@id);  
  47.     insert into student values(@name,1,'ddd',GETDATE (),GETDATE (),GETDATE ());  
  48.     print 'student添加学生成功'  
  49. go  
  50.  
  51. --测试  
  52. insert into student2 values('insertName',1,'insertClass')  
  53. select * from student2  
  54. select * from student  
  55.  
  56.  
  57. ---- 创建删除类型触发器  
  58. if(OBJECT_ID ('tgr_delete','tr')is not null)  
  59.     drop trigger tgr_delete   
  60. go    
  61.     create trigger [dbo].[tgr_delete]  
  62.     on [dbo].[student2]  
  63.         for delete --将删除student2的记录，存入到另张表中  
  64.     as   
  65.         print '备份数据中'  
  66.         if(object_id('student2BackUp','U')is not null )  
  67.          insert into student2BackUp select name,sex ,class from deleted --从deleted表中取出删除的表  
  68.         else   
  69.          select * into student2BackUp from deleted  
  70.         print '备份数据成功'  
  71.  
  72. go  
  73.  
  74. --测试  
  75. delete student2 where id=3 
  76. select * from student2  
  77. select * from student2BackUp   
  78.  
  79.  
  80. --创建update类型的触发器  
  81.  
  82. if(OBJECT_ID ('tgr_update','tr')is not null)  
  83.     drop trigger tgr_update  
  84.        
  85. go  
  86.  
  87.     create trigger [dbo].[tgr_update]  
  88.     on [dbo].[student2] ---将student2中更新的记录级联更新到student表中（若student表中存在对应的记录）  
  89.         for update  
  90.     as  
  91.         declare @oldName varchar(20),@newName varchar(20)  
  92.         --更新前的数据  
  93.         select @oldName =name from deleted--从deleted表中取删除的老名字  
  94.         print @oldName  
  95.         if(exists(select * from student where name like '%'+@oldName+'%'))  
  96.         begin   
  97.             select @newName=name from inserted --从inserted表中取出更新后的新名字  
  98.             update student set name=REPLACE (name,@oldName ,@newName )  
  99.             where name like '%'+@oldName +'%'  
  100.             print '级联修改数据成功'  
  101.         end  
  102.         else  
  103.             print '无需修改student表'  
  104.  
  105. GO  
  106.  
  107. --测试  
  108. update student2 set name='updateName' where id=4 
  109. select * from student2   
  110. select * from student   
  111.       
  112.       
  113. ---创建instead of 触发器  
  114.  
  115. if(OBJECT_ID ('tgr_insteadOf','tr')is not null)  
  116. drop trigger tgr_insteadOf  
  117.       
  118. go    
  119.       
  120.     create trigger [dbo].[tgr_insteadOf]  
  121.     on [dbo].[student2]  
  122.         instead of delete --update ,insert  
  123.     as   
  124.         declare @name varchar(20)  
  125.     select @namename=name from deleted  
  126.  
  127.     delete student where name=@name  
  128.     print 'name'+@name+'删除成功！'  
  129. go   
  130.  
  131.  
  132.  
  133.  
  134. --测试   
  135. select * from student2  
  136. select *  from student   
  137. delete student2 where id=19 
  138. select *  from student2   
  139. select *  from student --其实只删除了student表中的记录  
  140.      

 

 

sql触发器使用及实例_数据库

 

  1.  
  2. --创建updatecolumn类型触发器  
  3.  
  4. if(OBJECT_ID ('tgr_update_column','tr')is not null)  
  5. drop trigger tgr_update_column   
  6.  
  7. go  
  8.  
  9. create trigger [dbo].[tgr_update_column]  
  10. on [dbo].[student2]   
  11.     for update  
  12. as   
  13.     if(update(sex))  
  14.     begin   
  15.         raisError('性别不能随意设置',16,11)  
  16.         rollback tran  
  17.     end   
  18.  
  19. GO    
  20.  
  21. --测试  
  22. update student2 set sex=1 where sex is null  

sql触发器使用及实例_职场_02

 

  1. ---删除触发器  
  2. drop trigger trigger_name  
  3.  
  4. --查看数据库中已有触发器  
  5. select *  from sysobjects where xtype='tr' 
  6.  
  7. --查看单个触发器  (定义的文本)  
  8. exec sp_helptext 'trg_insert' --‘触发器名’  
  9.  
  10. --修改触发器  
  11.     alter trigger trigger_name  
  12.            on {table_name | view_name}  
  13.            {for | After | Instead of }  
  14.            [ insert, update,delete ]  
  15.            as  
  16.            sql_statement 

 PS: 一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器﹐一个表的每个修改动作都可以有多个After触发器。