SQL SERVER 学习笔记（触发器）

什么是触发器

触发器是一种由事件驱动的特殊存储过程，是独立存储在数据库里的对象，就像数据表一样。与存储过程的区别是：存储过程是通过其他程序来调用执行的；而触发器是通过事件来触发的，当某个事件发生是，触发器就自动的被触发运行。

一．触发器的种类

    SQL SERVER 2005版本支持4种触发器：INSERT、DELETE、UPDATE和INSTEAD OF触发器。

二．触发器执行的环境

触发器执行的环境既是执行触发器所需要的条件。有三个条件：触发器本身的定义、触发器所定义的目标表和两个测试表（Insert表和Delete表），也称临时表。

Deleted表用来存储执行DELETE和UPDATE语句时提取出来的数据，当执行此两语句时，相关的行数据从Trigger表中移动到Delete表中。

Insertd表用来存放执行INSERT和UPDATE语句时提取出来的数据。

假设有个SCHOOL数据库中student表：

USE  SCHOOL

GO

CREATE  TABLE  student

(

Sno   char(8)  PRIMARY  KEY,  

Sname   char(5),

Tname   char(10),

Grade   int

)

GO

--插入数据

INSERT  INTO  student  VALUES('1001','李浩','英语',89)

INSERT  INTO  student  VALUES('1001 ','李浩 ','数字电子 ',90 )

INSERT  INTO  student  VALUES('1002 ','汪林 ','计算机导论 ',85 )

INSERT  INTO  student  VALUES('1009 ','郝梦 ','大学物理 ',80 )

INSERT  INTO  student  VALUES('1021 ','郭晓 ','C++程序设计 ',81 )

INSERT  INTO  student  VALUES('1013 ','黄玲 ','计C#程序设计 ',93 )

INSERT  INTO  student  VALUES('1013 ','黄玲 ',计算机网络 ',79)

GO

三．触发器的创建以及使用

1.INSERT触发器

此触发器是针对在进行插入数据后的一些操作，包括添加、删除、更新、查询。

添加

CREATE TRIGGER  insert_stu

ON  student

AFTER  INSERT

AS

INSERT  INTO  student  VALUES('1015','李晓莉','**理论','85')

PRINT  ' 数据插入成功 '

删除

CREATE  TRIGGER delete_stu

ON  student

AFTER  INSERT

AS

DELETE student

FROM student

WHERE  Sno='1002'

PRINT  ' 数据删除成功 '

更新

CREATE  TRIGGER  update_stu

ON  student

AFTER  INSERT

AS

UPDATE  student

SET  Grade=Grade+10

WHERE  Sno='1009'

查询

CREATE  TRIGGER select_stu

ON  student

AFTER  INSERT

AS

SELECT * FROM  student

上面这四个触发器语句，在执行一个插入语句后会自动触发，即自动执行，他们都有一个共同的特点：没限制条件。

下面来创建一个带限制条件的触发器语句，当想学生信息表中插入数据时，要求插入学生必须是院系好为481208，否则插入不成功：

CREATE  TRIGGER  insert_Return

ON  studentINFO

FOR  INSERT

AS

DECLARE  @stp_no char(8)

SELECT  @sto_no=stoNo

FROM   studentINFO

IF(@stp_no!='481208')

BEGIN

ROLLBACK TRANSACTION

RAISEERROR('插入学生的院系编号必须是'481208'，请输入'，16，1，@stp_no)

END

当插入学生的院系编号不是481208时插入不成功。插入不符合条件，触发器就会执行ROLLBACK TRANSACTION语句，取消所有操作。

2.UPDATE 触发器

使用此触发器，可以更新数据库里的数据。UPDATE 触发器首先将原始数据行移动到逻辑Deleted表中，然后把一新行插入到Inserted表中，最后再将这行查到触发器中。也可以对数据库表进行增、删、改、查（又名珍、珠、奶、茶）。

插入

CREATE  TRIGGER  insert_stu

ON  student

AS

INSERT  INTO  student  VALUES('1007','王平','***概论','88')

PRINT  ' 数据插入成功 '

删除

CREATE  TRIGGER delete_stu

ON  student

AS

DELETE  student

FROM  student

WHERE  Sno='1009'

修改

CREATE  TRIGGER update_stu

ON  student

AS

UPDATE   student

SET   Grade=Grade+2

WHERE  Sname='汪林'

查询

    CREATE  TRIGGER select_stu

ON  student

AS

SELECT  TOP(5)  FROM   student

这些触发器执行成功后就会自动存储，要使触发器发生作用，只有在创建它之后就执行UPDATE语句。不能存储相同名字的触发器，系统会报错，你的操作将不能成功。

下面我们来创建一个复杂的UPDATE触发器，其实是运用了一些T-SQL知识。新创建一个触发器Copy_UpdateInfo，当你更新sno时，触发器提示你“信息不能更新”，并回滚事务；如果更新Cname或者Grade时，触发器会将被更新的学生信息写入到一个新表里，首先得创建这个表：

CREATE  TABLE  Temp_studentInfo

(

sno  CHAR(8),

sname  CHAR(5),

cname   CHAR(8),

grade  int

)

下面创建触发器Copy_UpdateInfo：

CREATE  TRIGGER  Copy_UpdateInfo

ON  student

FOR  UPDATE

AS

IF(COLUMNS_UPDATE() &1) > 0

BEGIN

    ROLLBACK  TRANSANCTION

    RAISERROR( ' 学生学号不能更新! ' , 16 , 1)

END

IF(COLUMNS_UPDATE()  &14) > 0

BEGIN

    INSERT  INTO  Temp_studentInfo

                          (sno,

                           sname,

                           cname,

                           grade  )

                           SELECT stu.Sno , 

                                   stu.Sname,

                                   stu.Cname,

                                   stu.Grade

                           FROM  student  AS  stu

END

当我们试着更新学号时，DBMS显示“学生学号不能更新”的提示，即更新失败，触发器执行ROLLBACK  TRANSANCTION回滚操作。当更新课程名或者成绩时，如下：

UPDATE  student

SET  Cname='汇编语言'

WHERE  Sno='1009'

GO

执行成功后，再执行T-SQL代码：SELECT  *  FROM  Temp_studentInfo  ，我们将会得到更新过的学生信息。

3.DELETE触发器 

DELETE触发器将删除的行数据插入到Delete表中，并决定Delete表中的行数据是否要执行触发操作。也可用此触发器对数据库表进行增、删、改、查（又名珍、珠、奶、茶）。

插入

CREATE  TRIGGER  deleteInsert_stu

ON  student

AFTER  DELETE

AS

INSERT  INTO  student

VALUES( ' 10010 ' , ' 李明 ' , ' 网络工程 ' , 88 )

删除

CREATE  TRIGGER  delete_stu

ON  student

FOR  DELETE

AS

DECLARE  @rowcount  int

SELECT  @rowcount =@@ROWCOUNT

IF  @rowcount > 1

   BEGIN

      ROLLBACK  TRANSANCTION

      RAISERROR( ' 你要删除的数据记录为%d条，一次只能删除一行数据，删除失败！' , 16 , 1 , @rowcount )

END

修改

CREATE  TRIGGER  deleteUpdate_stu

ON  student

AFTER  DELETE

AS

UPDATE  student

SET  Grade=Grade+10

WHERER  Grade 

查询

USE  SCHOOL

GO

DECLARE  @p  AS  int

SET  @p=10

SELECT  TOP ( @p ) *

FROM  student

GO

针对上面四个触发器，每执行一个UPDATE语句都会触发其中之一，每个触发器要在建立在执行语句之前创建，语句执行成功后立即将此触发器删除。

4.INSTEAD  OF 触发器

INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如，通常不能在一个基于连接多表的视图上进行DELETE操作。然而，可以编写一个INSTEAD OF DELETE触发器来实现删除，这样就可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中。相似地，在INSTEAD OFUPDATE触发器或者INSTEAD OF INSERT触发器中，你可以访问inserted表中的新行。

下面通过例子来说明INSTEAD OF触发器的用法：

--创建一个学生信息表

CREATE  TABLE  TempStudents

(

Sno  char(5),

Sname  char(10),

Sex  char(2),

Sclass  char(20)

)

--创建一个学生成绩表

CREATE  TABLE  TempAchies

(

Sno  char(5),

Suname  char(10),

Suachieve  float,

Smark  char(50)

)

--创建一个视图

CREATE  VIEW  TempStu_View

AS

SELECT  TempStudents.Sno , TempStudents.Sname , TempAchies.Suname , TempAchies.Suachieve

FROM  TempAchies  INNER  JOIN  TempStudents

ON  TempAchies.Sno = TempStudents.Sno

基于多连接的视图不允许向其插入数据，当我们输入下面的语句：

INSERT  INTO  TempStu_View

VALUES(' 95010 ' , ' 刘栋 ' , ' 计算机导论 ' , ' 91 ' )

执行成功后，DBMS显示“视图TempStu_View不可更新，因为修改会影响多个表”的提示。

--创建INSTEAD OF触发器，向视图中出入数据

CREATE  TRIGGER  View_Insert

ON  TempStu_View

INSTEAD  OF   INSERT

AS

BEGIN

   BEGIN

       INSERT  INTO  TempStudents(Sno , Sname)

       SELECT  Sno , Sname

       FROM  INSERTED

   END

   BEGIN

       INSERT  INTO  TempAchies (Sno , Suname , Suachieve)

       SELECT  Sno , Suname , Suachieve

       FROM  INSERTED

    END

END

执行成功既触发器创建成功，现在我们可以向视图TempStu_View里面插入数据了，执行下面的代码：

INSERT  INTO  TempStu_View

VALUES(' 95010 ' , ' 刘栋 ' , ' 计算机导论 ' , ' 91 ' )

插入成功后，再查看视图中的信息：

SELECT  *  FROM  TempStu_View

你将会看到你插入的数据。

 

5.触发器的修改与删除

修改

我们将修改上面创建的delete_stu触发器

ALTER  TRIGGER  delete_stu

ON  student

AFTER  INSERT

AS

DELETE  student

FROM  student

WHERE  Cname= '  黄玲 '

PRINT  ' 数据删除成功 '

删除

语法格式为 ：DROP  TRIGGER  trigger_name

下面我们删除delete_stu触发器

DROP  TRIGGER  delete_stu

代码执行后，触发器delete_stu被删除成功

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23804712/viewspace-665053/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23804712/viewspace-665053/