sql server2012触发器 sql server触发器实例

SQL SERVER触发器详细讲解带有实例

• 触发器

• 基本架构：
• 触发器的简介：
• 触发器的作用：
• 触发器的优点：
• 慎用触发器：
• Instead of 和 After触发器
• 触发器的执行过程
• 基本语句如下：

• 实例
• SQL触发器中的deleted表和inserted表

触发器

触发器：即当发生某一事件时，如果满足给定条件，则执行相应的动作。

基本架构：

触发器的简介：

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。
触发器可以查询其他表，而且可以包含复杂的sql语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。

此外触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息等。

------来源于百度百科

触发器的作用：

可在写入数据表前，强制检验或转换数据。
触发器发生错误时，异动的结果会被撤销。
部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
可依照特定的情况，替换异动的指令 (INSTEAD OF)。

触发器的优点：

触发器可通过数据库中的相关表实现级联更改，不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制用比CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

慎用触发器：

触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。
SQL Server为每个触发器都创建了两个专用表：Inserted表和Deleted表。
这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后，与该触发器相关的这两个表也被删除。
　　Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
　　Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。

Instead of 和 After触发器

SQL Server2000提供了两种触发器：Instead of 和After 触发器。这两种触发器的差别在于他们被激活的不同：
　　Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外，Instead of 触发器也可以用于视图，用来扩展视图可以支持的更新操作。
　　After触发器在一个Insert,Update或Deleted语句之后执行，进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。
　　一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器，一个表的每个修改动作都可以有多个After触发器。

触发器的执行过程

如果一个Insert﹑update或者delete语句违反了约束，那幺After触发器不会执行，因为对约束的检查是在After触发器被激活之前发生的。所以After触发器不能超越约束。
　　Instead of 触发器可以取代激活它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行，所以它可以对约束进行一些预处理。

基本语句如下：

create trigger trigger_name

　　on {table_name | view_name}

　　{for | After | Instead of }

　　[ insert, update,delete ]

　　as

　　sql_statement

删除触发器：

drop trigger trigger_name

查看数据库中已有触发器：
　– 查看数据库已有触发器

use jxcSoftware

　　go

　　select * from sysobjects where xtype='TR'

– 查看单个触发器

exec sp_helptext '触发器名'

修改触发器：
基本语句如下：

alter trigger trigger_name

　　on {table_name | view_name}

　　{for | After | Instead of }

　　[ insert, update,delete ]

　　as

　　sql_statement

注：（不同数据库支持不同的类型触发器，有些还支持before类型触发器，像SQL server 就不支持before触发器）

SQL Server 支持两种类型的触发器AFTER 触发器和INSTEAD OF 触发器，其中AFTER 触发器要求只有执行某一操作ISERT， UPDATE ，DELETE 之后触发器才被触发。
1）INSTEAD OF 触发器表示并不执行其所定义的操作INSERT，UPDATE ，DELETE，而仅是执行触发器本身，既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器。

2）after 触发器（也叫“FOR”触发器）则会在触发 insert、update 或是delete 动作之后执行。触发事件分为三类：UPDATE、DELETE和INSERT。

另外，定义触发器时，系统都都会自动生成两张表INSERTED和DELETED，我们是可以直接用的，如下：
DML 触发器有两个特殊的表：插入表（instered）和删除表（deleted），这两张表是逻辑表。这两个表是建立在数据库服务器的内存中，而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后，这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是已删除的数据。

实例

1.在表Student中建立删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student
中的元组学号为s1，则表SC中SNO为s1的元组也要删除；建立完触发器后用企业管理器删除Student中学号
为30的元组，看看表SC中SNO为30的选课记录是否也一起删除；
 
create trigger t_std2 on student
instead of  delete
as
begin
  declare @id char(5)
  select @id=sno from deleted
  delete from sc where SNo =@id
  delete from student where SNo=@id
  end
  go
 
delete from Student where SNo='00002'

2.	在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，
实现规定年龄24岁以上（包括24岁）的学生才能选修职业规划这门课程，如果年龄小于24岁，则输出’
年龄小于24，不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00001’,’005’,null)和
(‘00005’,’005’,null)看看结果；**/
create trigger t_sc on sc
for insert
as
begin
  declare @id char(5)
  select @id=sno from inserted
  if((select cno from inserted)='005' and (select sage from student where SNo= @id )<24)
  begin
  print '年龄小于24，不能选修该门课程 '
  rollback transaction
  end
  else
   print 'nice!'
   end
insert into course values('005','职业规划','4','0014')
insert into sc values('00001','005',null)
insert into sc values('00005','005',null)
select * from sc
go

3.在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，
如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能修改’，
修改失败，用SQL语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果；
 
createtrigger t2_sc on sc
after update
as
if(update(score))
begin
  declare @score1 numeric(3,1),@score2numeric(3,1)
  select @score1=score from inserted
  select @score2=score from deleted
  if(@score1>@score2 )
   print 'nice! '
   else
    update sc
    set sc.Score=@score2 from sc,deleted
    where sc.SNo=deleted.SNo andsc.CNo=deleted.CNo
    print '失败'
    end
update sc
    setScore=70 where SNo='00001' and CNo='001'

4.	在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和工资，
则输出’更新了年龄和工资’，如果更新了年龄没有更新工资，则输出’更新了年龄’，
如果更新了工资而没有更新年龄，则输出’更新了工资’，创建完后使用SQL语句把
tno为001的年龄加1，把tno为002的工资加1，把tno为003的年龄和工资都加1，看看结果；
create trigger t_teacher on teacher
after update
as
begin
  declare @age int,@sal float
  select @age=age from deleted
  select @sal=sal from deleted
  if(@age <> (select age from inserted )and @sal <>(select sal from inserted))
  print '更新了年龄和工资 '
  else if(@age <> (select age from inserted )and @sal =(select sal from inserted))
  print '更新了工资 '
  else if(@age = (select age from inserted )and @sal <>(select sal from inserted))
   print '更新了年龄 '
   end
 
   update Teacher
   set age=age+1 where Tno='0001'

5.	在不删除触发器的前提下，使3创建的触发器无效；
alter table teacher disable trigger t_teacher

6.	创建一个名为tri_Delete_C的触发器，要求首先判断数据库中是否已经存在名为
tri_Delete_C的触发器，如果存在，首先删除，再创建，触发器要求删除一门课程时
候，首先判断该课程有否有人选，如果有人选，则不能删除,并通过测试数据验证触
发器的执行情况。

if(exists (select * from  sysobjects where xtype='tr' and name='tri_Delete_C'))
begin
  drop trigger tri_Delete_C
  print '已删除'
  end
  go
  create trigger tri_Delete_C on course
   for delete
    as	
	begin
	if exists (select * from sc inner join deleted on sc.CNo=deleted.CNo)
	begin
	  print '不能删除'
	  rollback transaction
	end
	end
	go
	alter table sc drop constraint FK__sc__CNo__08B54D69
	delete course where CNo='001'
	alter table sc add constraint FK__sc__CNo__08B54D69 foreign key(cno) references course(cno)

SQL触发器中的deleted表和inserted表

在触发器语句中用两个特殊的表一个是deleted表和inserted。它们是通过触发器操作自动创建驻留在内存中的临时表。

描述：

Deleted表用于存储 DELETE和 UPDATE语句所影响的行的副本。在执行DELETE或 UPDATE语句时，行从触发器表中删除，并传输到 deleted表中。Deleted表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

综上可以总结一下：

触发器的操作	deleted表和inserted表的数据变化
插入操作（Insert）	Inserted表有数据，Deleted表无数据
删除操作（Delete）	Inserted表无数据，Deleted表有数据
更新操作（Update）	Inserted表有数据（新数据），Deleted表有数据（旧数据）

实践：

举个例子如果现在有两张表，一个是课程表T_course,还有一个学生表T_student。课程表中的课程和学生是一对多的关系。如下图中的关系。

从图中可以看出课程表T_course和学生表T_student有主外键的关系，也就是说当我删除课程表中的某个课程时，必须先要删除上这门课的所有学生。这就可以利用触发器来实现：其中就要通过deleted表来查询要删除的课程号。

ALTER TRIGGER [dbo].[tri_courseDelete]
   ON  [dbo].[T_course]
   instead of  DELETE
AS 
BEGIN
	declare @courseId varchar(50) --定义课程号变量。
	--在deleted表中查询要删除的课程号
	select @courseId=courseId from deleted
	--删除上这门课的学生
	delete T_student where courseId=@courseId
	--删除这门课
	delete T_cousrse where courseId=@courseId
END