触发器

最新推荐文章于 2022-01-18 11:07:24 发布
strom
最新推荐文章于 2022-01-18 11:07:24 发布
阅读量1.3k
点赞数
本文链接：https://blog.youkuaiyun.com/wangyulinyy/article/details/9663761
版权
SQL触发器实例

16.2.3 INSERT触发器

实例1 创建INSERT触发器

为STUDENT表创建触发器S_insert，当向STUDENT表中插入数据时，要求学号必须以“97”开头，且课程号CNO必须在COURSE表中，否则取消插入操作。实例代码如下。

CREATE TRIGGER S_insert

ON STUDENT

FOR INSERT AS

DECLARE @S_no VARCHAR(4), @S_cno INT

SELECT @S_no= SNO, @S_cno=CNO

FROM INSERTED

IF (LEFT(@S_no,2)!='97')

BEGIN

ROLLBACK TRANSACTION

RAISERROR('输入的学号：%s不是97级的学生，请确认后重新录入！',16,1, @S_no)

END

IF(@S_cno NOT IN (SELECT CNO FROM COURSE))

BEGIN

ROLLBACK TRANSACTION

RAISERROR('输入的课程号：%d在COURSE表中不存在，请确认后重新录入！',16,1, @S_cno)

END

当通过如下语句向STUDENT表中插入数据时：

INSERT INTO STUDENT VALUES('9602','王永','机械工程','男',2,76,'必修')

由于插入数据的学号为“9602”，并不是以“97”开头，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。

运行结果如下。

输入的学号：9602不是97级的学生，请确认后重新录入！

当通过如下语句向STUDENT表中插入数据时：

INSERT INTO STUDENT VALUES('9702','王永','机械工程','男',12,76,'必修')

由于课程号12在COURSE表中不存在，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：

输入的课程号：12在COURSE表中不存在，请确认后重新录入！

可以在一个表上创建多个触发器，数据库把一个表中所有触发器都看作同一事务的一部分。因此只要其中一个触发器执行了ROLLBACK TRAN SACTION语句，那么所有的操作（与该INSERT语句有关）都将被取消。

16.2.4 DELETE触发器

实例2 创建DELETE触发器

为STUDENT表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删除记录的课程为本系教师所开设时，取消删除操作。实例代码：

CREATE TRIGGER S_delete

ON STUDENT

FOR DELETE AS

DECLARE @rowcount int

SELECT @rowcount=@@ROWCOUNT

IF @rowcount>1

BEGIN

ROLLBACK TRANSACTION

RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,

@rowcount)

END

DECLARE @S_dname VARCHAR(16), @S_cno INT

SELECT @S_dname=DNAME, @S_cno=CNO

FROM DELETED

IF(@S_cno IN (SELECT CNO FROM TEACHER WHERE DNAME=@S_dname))

BEGIN

ROLLBACK TRANSACTION

RAISERROR('删除记录的课程为本系即%s系教师所开设，不允许删除！',16,1,

@S_dname)

END

当通过如下语句从STUDENT表中删除数据时：

DELETE STUDENT WHERE SNO=’9706’

运行结果如下。

当前要删除的记录数为4,一次只允许删除一行记录！

当用户从STUDENT表执行DELETE语句时，DBMS就激活触发器S_delete，该触发器首先判断要删除记录的行数，只要多于一条，就取消删除操作。当删除的记录只有一行时，触发器继续判断删除的记录的课程是否为本系教师所开设，如果是，就取消删除操作，否则就完成删除操作。如下面的删除代码：

DELETE STUDENT WHERE SNO=’9705’ AND CNO=8

运行结果如下。

删除记录的课程为本系即电子工程 系教师所开设，不允许删除！

说明 在SQL Server中执行TRUNCATE TABLE语句，从表中删除所有的行时，不会触发DELETE触发器。

16.2.5 UPDATE触发器

特定的表上执行UPDATE语句时，会触发UPDATE触发器。UPDATE操作包括两个部分：先将需要更新的内容从表中删除，然后插入新值。因此UPDATE触发器同时涉及到删除表和插入表。

实例3 创建UPDATE触发器

为COURSE表创建UPDATE触发器C_update，当COURSE表的CNO和CNAME列（第1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务。当其余的列（第3、4、5列）被更新时，触发器将更新前后的数据写入C_UpInfo表中。实例代码如下。

1．创建跟踪信息表C_UpInfo

CREATE TABLE C_UpInfo

(

Oper_Time smalldatetime,

Date_Type CHAR(3),

CNO int ,

CNAME char(30),

CTIME int,

SCOUNT int,

CTEST smalldatetime,

)

2．创建UPDATE触发器C_update

CREATE TRIGGER C_update

ON COURSE

FOR UPDATE AS

IF（COLUMNS_UPDATED()&3）>0

BEGIN

ROLLBACK TRANSACTION

RAISERROR('COURSE表的CNO列和CNAME列中的数据不允许被更新！',16,1)

END

IF（COLUMNS_UPDATED()&28）>0

BEGIN

INSERT INTO C_UpInfo(Oper_Time, Date_Type,CNO,CNAME,CTIME,SCOUNT,

CTEST)

SELECT GETDATE(),’OLD’,del.CNO,del.CNAME, del.CTIME, del.SCOUNT,

del.CTEST

FROM DELETED AS del

INSERT INTO C_UpInfo(Oper_Time, Date_Type,CNO,CNAME,CTIME,SCOUNT,

CTEST)

SELECT GETDATE(),’NEW’,ins.CNO,ins.CNAME, ins.CTIME, ins.SCOUNT,

ins.CTEST

FROM INSERTED AS ins

END

当通过下面的代码更新COURSE表的CNAME列的值：

UPDATE COURSE

SET CNAME = '线性代数基础'

WHERE CNO = 4

运行结果如下。

COURSE表的CNO列和CNAME列中的数据不允许被更新！

此时查看CNO＝4的课程名称：

SELECT CNAME FROM COURSE WHERE CNO=4

运行结果如图16.2所示。

图16.2 CNO＝4的课程名称

可见，CNAME列的值并没有被更新。而通过下面代码更新CNO和CNAME列以外的信息时：

UPDATE COURSE

SET CTIME=32,SCOUNT=90, CTEST='2006-9-15'

WHERE CNO = 4

此时查看CNO = 4的课程信息：

SELECT * FROM COURSE WHERE CNO=4

运行结果如图16.3所示：

图16.3 CNO = 4的课程信息

可见，该记录已经被更新。

查看跟踪信息表C_UpInfo中的数据：

SELECT * FROM C_UpInfo

运行结果如图16.4所示。

图16.4 表C_UpInfo中的数据

该表详细记录了COURSE表的更新时间以及更新前后的数据。

例子：
create table cdb_members (
credits int,
username varchar(255)
)

create table dede_members (
money int,
username varchar(255)
)

create trigger cdb_members_update_trigger
after update
on cdb_members
for each row
begin
update dede_members set money = NEW.credits
where dede_members.username = OLD.username;

 

CREATE   TRIGGER   trigger_name    

  ON   {   table   |   view   }    

  [   WITH   ENCRYPTION   ]    

  {  

          {   {   FOR   |   AFTER   |   INSTEAD   OF   }   {   [   INSERT   ]   [   ,   ]   [   UPDATE   ]   }  

                  [   WITH   APPEND   ]  

                  [   NOT   FOR   REPLICATION   ]  

                  AS  

                  [   {   IF   UPDATE   (   column   )  

                          [   {   AND   |   OR   }   UPDATE   (   column   )   ]  

                                  [   ...n   ]  

                  |   IF   (   COLUMNS_UPDATED   (   )   {   bitwise_operator   }   updated_bitmask   )  

                                  {   comparison_operator   }   column_bitmask   [   ...n   ]  

                  }   ]    

                  sql_statement   [   ...n   ]    

          }    

  }    

   

  参数  

  trigger_name  

   

  是触发器的名称。触发器名称必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。  

   

  Table   |   view  

   

  是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。  

   

  WITH   ENCRYPTION  

   

  加密   syscomments   表中包含   CREATE   TRIGGER   语句文本的条目。使用   WITH   ENCRYPTION   可防止将触发器作为   SQL   Server   复制的一部分发布。  

   

  AFTER  

   

  指定触发器只有在触发   SQL   语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。  

   

  如果仅指定   FOR   关键字，则   AFTER   是默认设置。  

   

  不能在视图上定义   AFTER   触发器。  

   

  INSTEAD   OF  

   

  指定执行触发器而不是执行触发   SQL   语句，从而替代触发语句的操作。  

   

  在表或视图上，每个   INSERT、UPDATE   或   DELETE   语句最多可以定义一个   INSTEAD   OF   触发器。然而，可以在每个具有   INSTEAD   OF   触发器的视图上定义视图。  

   

  INSTEAD   OF   触发器不能在   WITH   CHECK   OPTION   的可更新视图上定义。如果向指定了   WITH   CHECK   OPTION   选项的可更新视图添加   INSTEAD   OF   触发器，SQL   Server   将产生一个错误。用户必须用   ALTER   VIEW   删除该选项后才能定义   INSTEAD   OF   触发器。  

   

  {   [DELETE]   [,]   [INSERT]   [,]   [UPDATE]   }  

   

  是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。  

   

  对于   INSTEAD   OF   触发器，不允许在具有   ON   DELETE   级联操作引用关系的表上使用   DELETE   选项。同样，也不允许在具有   ON   UPDATE   级联操作引用关系的表上使用   UPDATE   选项。  

   

  WITH   APPEND  

   

  指定应该添加现有类型的其它触发器。只有当兼容级别是   65   或更低时，才需要使用该可选子句。如果兼容级别是   70   或更高，则不必使用   WITH   APPEND   子句添加现有类型的其它触发器（这是兼容级别设置为   70   或更高的   CREATE   TRIGGER   的默认行为）。有关更多信息，请参见   sp_dbcmptlevel。  

   

  WITH   APPEND   不能与   INSTEAD   OF   触发器一起使用，或者，如果显式声明   AFTER   触发器，也不能使用该子句。只有当出于向后兼容而指定   FOR   时（没有   INSTEAD   OF   或   AFTER），才能使用   WITH   APPEND。以后的版本将不支持   WITH   APPEND   和   FOR（将被解释为   AFTER）。  

   

  NOT   FOR   REPLICATION  

   

  表示当复制进程更改触发器所涉及的表时，不应执行该触发器。  

   

  AS  

   

  是触发器要执行的操作。  

   

  sql_statement  

   

  是触发器的条件和操作。触发器条件指定其它准则，以确定   DELETE、INSERT   或   UPDATE   语句是否导致执行触发器操作。  

   

  当尝试   DELETE、INSERT   或   UPDATE   操作时，Transact-SQL语句中指定的触发器操作将生效。  

   

  触发器可以包含任意数量和种类的   Transact-SQL   语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的   Transact-SQL   语句常常包含控制流语言。CREATE   TRIGGER   语句中使用几个特殊的表：    

   

  deleted   和   inserted   是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索   deleted   表中的所有值，请使用：    

  SELECT   *  

  FROM   deleted  

   

  如果兼容级别等于   70，那么在   DELETE、INSERT   或   UPDATE   触发器中，SQL   Server   将不允许引用   inserted   和   deleted   表中的   text、ntext   或   image   列。不能访问   inserted   和   deleted   表中的   text、ntext   和   image   值。若要在   INSERT   或   UPDATE   触发器中检索新值，请将   inserted   表与原始更新表联接。当兼容级别是   65   或更低时，对   inserted   或   deleted   表中允许空值的text、ntext   或   image   列，将返回空值；如果这些列不可为空，则返回零长度字符串。    

  当兼容级别是   80   或更高时，SQL   Server   允许在表或视图上通过   INSTEAD   OF   触发器更新   text、ntext   或   image   列。  

   

  n  

   

  是表示触发器中可以包含多条   Transact-SQL   语句的占位符。对于   IF   UPDATE   (column)   语句，可以通过重复   UPDATE   (column)   子句包含多列。  

   

  IF   UPDATE   (column)  

   

  测试在指定的列上进行的   INSERT   或   UPDATE   操作，不能用于   DELETE   操作。可以指定多列。因为在   ON   子句中指定了表名，所以在   IF   UPDATE   子句中的列名前不要包含表名。若要测试在多个列上进行的   INSERT   或   UPDATE   操作，请在第一个操作后指定单独的   UPDATE(column)   子句。在   INSERT   操作中   IF   UPDATE   将返回   TRUE   值，因为这些列插入了显式值或隐性   (NULL)   值。  

   

   

   

  说明     IF   UPDATE   (column)   子句的功能等同于   IF、IF...ELSE   或   WHILE   语句，并且可以使用   BEGIN...END   语句块。有关更多信息，请参见控制流语言。    

   

   

  可以在触发器主体中的任意位置使用   UPDATE   (column)。  

   

  column  

   

  是要测试   INSERT   或   UPDATE   操作的列名。该列可以是   SQL   Server   支持的任何数据类型。但是，计算列不能用于该环境中。有关更多信息，请参见数据类型。    

   

  IF   (COLUMNS_UPDATED())    

   

  测试是否插入或更新了提及的列，仅用于   INSERT   或   UPDATE   触发器中。COLUMNS_UPDATED   返回   varbinary   位模式，表示插入或更新了表中的哪些列。  

   

  COLUMNS_UPDATED   函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含   8   列以上，则   COLUMNS_UPDATED   返回多个字节，最左边的为最不重要的字节。在   INSERT   操作中   COLUMNS_UPDATED   将对所有列返回   TRUE   值，因为这些列插入了显式值或隐性   (NULL)   值。  

   

  可以在触发器主体中的任意位置使用   COLUMNS_UPDATED。  

   

  bitwise_operator  

   

  是用于比较运算的位运算符。  

   

  updated_bitmask  

   

  是整型位掩码，表示实际更新或插入的列。例如，表   t1   包含列   C1、C2、C3、C4   和   C5。假定表   t1   上有   UPDATE   触发器，若要检查列   C2、C3   和   C4   是否都有更新，指定值   14；若要检查是否只有列   C2   有更新，指定值   2。  

   

  comparison_operator  

   

  是比较运算符。使用等号   (=)   检查   updated_bitmask   中指定的所有列是否都实际进行了更新。使用大于号   (>)   检查   updated_bitmask   中指定的任一列或某些列是否已更新。  

   

  column_bitmask  

   

  是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。