触发器

概述

触发器是一种特殊类型的存储过程。触发器重要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名被直接调用。
可以用于SQLServer约束，默认值和规则的完整性检查。

分类：数据操作语言触发器、数据定义语言触发器

• 数据操作语言（Data Manipulation Language ，DML）触发器

  DML触发器是一些附加在特定表或视图上的操作代码，当数据库服务器中发生数据操作语言事件时执行这些操作。
  SQLServer中DML触发器有三种：INSERT触发器、UPDATE触发器、DELETE触发器。

• 数据定义语言（Data Definition Language ，DDL）触发器

  DDL触发器是当服务器或者数据库中发生数据定义语言事件时被激活调用，使用DDL可以防止对数据库架构进行的默写更改或记录数据库架构中的更改或事件。

创建DML触发器

INSERT触发器

【例1】在stu_info表上创建一个名称为Insert_Student的触发器，在用户向stu_info表中插入数据时触发

CREATE TRIGGER Insert_Student
ON stu_info
AFTER INSERT
AS
BEGIN
  IF OBJECT_ID(N'stu_Sum',N'U') IS NULL         --判断stu_Sum表是否存在
    CREATE TABLE stu_Sum(number INT DEFAULT 0)  --创建存储学生人数的stu_Sum表
  DECLARE @stuNumber INT
  SELECT @stuNumber = COUNT(*) FROM stu_info
  IF NOT EXISTS (SELECT * FROM stu_Sum)         --判断表中是否有记录
    INSERT INTO stu_Sum VALUES(0)
  UPDATE stu_Sum SET number = @stuNumber        --把更新后总的学生人数插入到stu_Sum表中
END
 
-- 当创建完触发器之后，向stu_info表中插入记录，触发触发器的执行。执行下面的语句。
SELECT COUNT(*) stu_info表中总人数 FROM  stu_info
INSERT INTO stu_info (s_id,s_name,s_score,s_sex) VALUES(20,'白雪',87,'女')
SELECT COUNT(*) stu_info表中总人数 FROM  stu_info
SELECT number AS stu_Sum表中总人数 FROM stu_Sum

【例2】创建触发器，当用户向stu_Sum表中插入数据时，禁止操作

CREATE TRIGGER Insert_forbidden
ON stu_Sum
AFTER INSERT
AS
BEGIN
  RAISERROR('不允许直接向该表插入记录，操作被禁止',1,1)
  ROLLBACK TRANSACTION
END
 
INSERT INTO stu_Sum VALUES(5)

DELETE触发器

【例3】创建DELETE触发器，用户对stu_info表执行删除操作后触发，并返回删除的记录信息

CREATE TRIGGER Delete_Student
ON stu_info
AFTER DELETE
AS
BEGIN
  SELECT s_id AS 已删除学生编号,s_name,s_score,s_sex,s_age  
  FROM DELETED
END
 
--创建完成，执行一条DELETE语句触发该触发器，输入语句如下。
DELETE FROM stu_info WHERE s_id=6;

UPDATE触发器

【例4】创建UPDATE触发器，用户对stu_info表执行更新操作后触发，并返回更新的记录信息

CREATE TRIGGER Update_Student
ON stu_info
AFTER UPDATE
AS
BEGIN
  DECLARE @stuCount INT;
  SELECT @stuCount = COUNT(*) FROM stu_info;
  UPDATE  stu_Sum SET number = @stuCount;
  SELECT s_id AS 更新前学生编号 ,s_name AS 更新前学生姓名 FROM DELETED
  SELECT s_id AS 更新后学生编号 ,s_name AS 更新后学生姓名  FROM INSERTED
END
 
-- 创建完成，执行一条UPDATE语句触发该触发器，输入语句如下。
UPDATE stu_info SET s_name='张懿' WHERE s_id=1;

替代触发器

【例5】创建INSTEAD OF触发器，当用户插入到stu_info表中的学生记录中的成绩大于100分时，拒绝插入，同时提示“插入成绩错误”的信息。

CREATE TRIGGER InsteadOfIinsert_Student
ON stu_info
INSTEAD OF INSERT
AS
BEGIN
DECLARE @stuScore INT;
SELECT @stuScore = (SELECT s_score FROM inserted)
If @stuScore > 100
     SELECT '插入成绩错误' AS 失败原因
END
 
-- 创建完成，执行一条INSERT语句触发该触发器，输入语句如下。
INSERT INTO stu_info (s_id,s_name,s_score,s_sex)
VALUES(21,'周鸿',110,'男');
SELECT * FROM stu_info;

创建DDL触发器

创建服务器作用域的DDL触发器，需要指定ALL SERVER参数。

【例6】创建数据库作用域的DDL触发器，拒绝用户对数据库中表的删除和修改操作

CREATE TRIGGER DenyDelete_Student
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
BEGIN
  PRINT '用户没有权限执行删除操作！'
  ROLLBACK TRANSACTION
END
 
DROP TABLE stu_info

【例7】创建数据库作用域的DDL触发器，拒绝用户对数据库中表的删除和修改操作

CREATE TRIGGER DenyCreate_AllServer
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE
AS
BEGIN
PRINT '用户没有权限创建或修改服务器上的数据库！'
ROLLBACK TRANSACTION
END
 
-- 执行下面的语句来测试触发器的执行过程。
CREATE DATABASE test_db

管理触发器

查看触发器

【例8】使用sp_helptext查看Insert_student触发器的信息

sp_helptext Insert_student

修改触发器

和创建的语法格式几乎相同

【例9】修改Insert_student触发器，将INSERT触发器修改为DELETE触发器

ALTER TRIGGER Insert_Student
ON stu_info
AFTER DELETE
AS
BEGIN
  IF OBJECT_ID(N'stu_Sum',N'U') IS NULL            --判断stu_Sum表是否存在 
     CREATE TABLE stu_Sum(number INT DEFAULT 0);   --创建存储学生人数的stu_Sum表 
  DECLARE @stuNumber INT;
  SELECT @stuNumber = COUNT(*) FROM stu_info;
  IF NOT EXISTS (SELECT * FROM stu_Sum)
     INSERT INTO stu_Sum VALUES(0);
  UPDATE stu_Sum SET number = @stuNumber;  --把更新后总的学生人数插入到stu_Sum表中 
END

删除触发器

当触发器不再需要时，可以将其删除，删除不会影响其操作的数据表，而当某个表被删除时，该表上的触发器也同时被删除。

【例10】使用DROP TRIGGE语句删除Insert_student触发器

DROP TRIGGER Insert_student;

【例11】删除服务器作用域的触发器DenyCreate_AllServer

DROP TRIGGER DenyCreate_AllServer ON ALL Server;

启用和禁用触发器

【例12】禁止使用Update_Student触发器

ALTER TABLE stu_info
DISABLE TRIGGER Update_student
 
-- DISABLE TRIGGER Update_student ON stu_info

【例13】禁止使用数据库作用域的触发器DenyDelete_student

DISABLE  TRIGGER DenyDelete_student ON DATABASE;

【例14】启用Update_Student触发器

ALTER TABLE stu_info
ENABLE TRIGGER Update_student
 
-- ENABLE TRIGGER Update_student ON stu_info

【例15】启用数据库作用域的触发器DenyDelete_student

ENABLE TRIGGER DenyDelete_student ON DATABASE;

问题

使用触发器需要注意的问题是什么？

在使用触发器时，对相同的表、事件只能创建一个触发器。

比如对表account创建了一个after insert触发器，那么如果对表account再次创建一个after insert触发器，将会报错。此时只可以在表account上创建after insert或者insterad of update类型的触发器。

灵活地运用触发器将为操作省去很多麻烦。