SQLServer 触发器入门

目录

触发器

14第十四章触发器

阅读目录

概念:

  • 1.触发器
    • 1.1.DDL触发器
    • 1.2.DML触发器
    • 1.3.创建触发器
      • 1.3.1.创建DML触发器
      • 1.3.2.创建DDL触发器
      • 1.3.3.嵌套触发器
      • 1.3.4.递归触发器
    • 1.4.管理触发器

        触发器简介:

 

  • 一:触发器的优点
  • 二:触发器的作用
  • 三:触发器的分类
  • 四:触发器的工作原理
  • 五:创建触发器
  • 六:管理触发器 

  触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。

1.触发器

触发器是一种特殊的存储过程,与表紧密关联。

 
       

触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( 
insert,delete,
update)时就会激活它执行,触发器经常用于加强数据的完整性约束和业务规则等。在我看来触发器实际上就是一个事件,就像C#中,点击一个按钮会触发相应的操作。

DML –>
AFTER / FOR   UPDATE , INSERT , DELETE  — 用来级联删除

概念:

触发器和存储过程的区别:

1.1.DDL触发器

当服务器或数据库中发生数据定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。如果要执行以下操作,可以使用DDL触发器:

  • 防止对数据库架构进行更改
  • 希望数据库中发生某些情况以响应数据库架构中的更改
  • 要记录数据库架构中的更改或事件

        触发器的分类:

       
 –> INSTEAD OF  在 时间之前触发,相当于 bef

  触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。

  触发器与存储过程的区别是运行方式的不同,触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户,应用程序或者触发器来显示地调用并执行。

1.2.DML触发器

当数据库服务器中发生数据操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,如果检测到错误,则整个事务回滚。DML触发器在一下方面非常有用:

  • 可实现数据库相关表之间的级联更改
  • 可以防止恶意或错误的DML语句事件,并强制执行比CHECK约束更为复杂的其他限制
  • 可以评估数据修改前后表的状态,并根据该差异采取措施

一个表中的多个同类DML触发器,允许用多个不同的操作来响应同一个修改语句
SQL Server
2008
为每个触发器创建了2个特殊的表:INSERTED表和DELETED表。这是两个逻辑表,由系统来创建和维护,用户不能对他们进行修改。它们存放在内存中,而不是在数据库中,并且结构与被DML触发器作用的表的结构相同。
INSERTED表中存放了由执行INSERTUPDATE语句而插入的所有行,在执行INSERTUPDATE语句时,新的行将同时被插入到触发器作用的表和INSERTED表中。INSERTED表中的行是触发器作用的表中行的副本。
DELETED表中存放了由执行DELETEUPDATE语句而删除的所有行,在执行DELETEUPDATE语句时,被删除的行将由触发器作用的表中被移动到DELETED表,两个表中不会有重复行。

        (1)DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。
        (2)DDL(数据定义语言
Data Definition
Language)触发器:是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。
        (3)登陆触发器:是指当用户登录SQL
SERVER实例建立会话时触发。

INSERTED,
DELETED 两张表要好好利用。

触发器和存储过程的区别:

一:触发器的优点

 1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

 2.触发器可以通过数据库中的相关表进行层叠修改。

 3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。

1.3.创建触发器

       
其中DML触发器最为常用,根据DML触发器触发的方式不同又分为以下两种情况:

在创建 DML
触发器时,不能使用下列语句:

  触发器与存储过程的区别是运行方式的不同,触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户,应用程序或者触发器来显示地调用并执行。

二:触发器的作用

 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要作用主要有以下接个方面:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中所有相关的表,自动触发其它与之相关的操作
  3. 跟踪变化,撤销或回滚违法操作,防止非法修改数据
  4. 返回自定义的错误消息,约束无法返回信息,而触发器可以
  5. 触发器可以调用更多的存储过程

1.3.1.创建DML触发器

     
(1)AFTER触发器:它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。它主要是用于记录变更后的处理或检查,一旦发生错误,可以用Rollback
Transaction语句来回滚本次扣件,不过不能对视图定义AFTER触发器。      
(2)INSTEAD
OF触发器:它在执行INSERT、UPDATE、DELETE语句操作之前执行触发器本身所定义的操作。而INSTEAD
OF触发器是可以定义在视图上的。

CREATE /
ALTER /DROP DATABASE

回到顶部

三:触发器的分类

 SqlServer包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。

1.DML(数据操作语言,Data Manipulation Language)触发器

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

  1. insert触发器:向表中插入数据时被触发;
  2. delete触发器:从表中删除数据时被触发;
  3. update触发器:修改表中数据时被触发。

当遇到下列情形时,应考虑使用DML触发器:

  1. 通过数据库中的相关表实现级联更改
  2. 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他限制。
  3. 评估数据修改前后表的状态,并根据该差异才去措施。

2.DDL(数据定义语言,Data Definition Language)触发器

 DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用,使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3.登录触发器

    登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败,将不激发登录触发器。

1.3.1.1.INSERT触发器

示例1:创建一个触发器Automatic_division,当在Student表中插入一条学生信息时,触发器根据入学分数(stu_enter_score)对学生进行自动分班,并在class_student表中插入一条记录。
分班要求:
|Stu_enter_score |Class_id |Class_name|
|——————-|——————|————–|
|stu_enter_score>=700| 01| 创新A班|
|650<=Stu_enter_score<700| 02| 重点B班|
|600<=Stu_enter_score<650| 03| 提高C班|
|550<=Stu_enter_score<600| 04| 普通D班|
|500<=Stu_enter_score<550| 05| 普通E班|
|Stu_enter_score<500| 06| 普通F班|
执行下列语句

CREATE TRIGGER automatic_division
ON student--新建一个检测student表的触发器,命名automatic_division
FOR INSERT--检测到INSERT操作时触发器工作
AS
DECLARE @score INT,@stu_no VARCHAR(8),@class_id CHAR(2)
--声明三个变量
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY--声明一个指向inserted表的局部游标stu_cursor
FOR SELECT stu_no,stu_enter_score FROM inserted
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标指向inserted表的第一个数据并把游标指向的stu_no和stu_enter_score值分别赋值给@stu_no和@score
WHILE @@FETCH_STATUS=0--开始循环
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
--判断结束
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源
GO

验证代码是否正确
student表中插入数据,并查看class_student表中的数据是否正确

INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180001','邹莉莉','女','389'),
('20180002','万兴','男','701'),
('20180003','孙伟','男','652'),
('20180004','温佳静','女','676'),
('20180005','姜立夫','男','542')

Class_student表中的数据如图所示
图片 1
游标示例2:对student表中还未分班的学生进行分班
Student表中的数据如图所示
图片 2
其中stu_no20180001~20180005的学生已经在示例1中分班,剩下的学生全都未分班。
执行下列语句

ALTER TABLE student
ADD stu_division_state bit--为student表新建一列记录是否已分班,true表示已分班
GO
DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中
GO
DECLARE @stu_no VARCHAR(8),@score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state IS NULL
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
Student表的数据
图片 3
Class_student表的数据
图片 4
至此Student表中所有学生都已分班
为了以后方便,可以将游标示例2中的代码稍作修改封装成一个用户自定义存储过程
存储过程示例3
修改后的代码如下

CREATE PROCEDURE student_division
AS
BEGIN
UPDATE student
SET stu_division_state=0--先将student表中所有学生的分班情况都标成未分班

DECLARE stu_class_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM class_student
--新建游标stu_class_cursor指向class_student表的所有数据
OPEN stu_class_cursor--打开游标
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_class_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE student
SET stu_division_state=1
WHERE stu_no=@stu_no--利用游标找出student表中已分班的学生并标记分班状态
FETCH NEXT FROM stu_class_cursor INTO @stu_no
END
CLOSE stu_class_cursor--关闭游标
DEALLOCATE stu_class_cursor--释放游标资源
---所有学生是否分班已经全部记录在stu_division_state中

DECLARE @score INT,@class_id CHAR(2)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no,stu_enter_score FROM student WHERE stu_division_state=0
--新建student表的游标stu_cursor,指向所有未分班学生
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor INTO @stu_no,@score
WHILE @@FETCH_STATUS=0--循环开始
BEGIN
BEGIN--先对@score的数值范围做判断,以确定该学生的班级编号
IF @score>=700
SET @class_id='01'
ELSE IF @score<700 AND @score>=650
SET @class_id='02'
ELSE IF @score<650 AND @score>=600
SET @class_id='03'
ELSE IF @score<600 AND @score>=550
SET @class_id='04'
ELSE IF @score<550 AND @score>=500
SET @class_id='05'
ELSE
SET @class_id='06'
END
INSERT INTO class_student(class_id,stu_no)
VALUES(@class_id,@stu_no)--将数据插入到class_student表中
UPDATE student--将student表的stu_division_state改成已分班
SET stu_division_state=1
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--将游标移向inserted表的下一个数据,重复这个循环
END--循环结束
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO

注:和游标示例2的代码相比,SQLServer 触发器入门。示例3的代码添加了将所有学生分班状态标记为0的过程,去掉了添加stu_division_state列的过程,但对原来已有的学生的分班状态赋值这个步骤并未删去,而是进行重复校验。并且删除了两段代码中的GO和第二段用于给学生分班的代码中对@stu_no变量的重复声明。

student表插入数据并运行student_division的存储过程

注:对student表插入数据前应先禁用示例1的触发器automatic_division

执行下列语句

ALTER TABLE student DISABLE TRIGGER automatic_division
--禁用automatic_division触发器
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score,stu_division_state)
VALUES('20180006','王洋','男','724',NULL),
('20180007','易阳','男','713',NULL),
('20180008','孙浩','男','584',NULL),
('20180009','张秋燕','女','420','False'),
('20180010','胡燕','女','527','True')

Student表的数据如图所示,红框内就是我刚刚插入还未分班的数据,其中2018000920180010这两个学生的分班状态被我误标成FalseTrue
图片 5
执行存储过程

EXEC dbo.student_division

结果如图所示
Student表的数据(分班状态都为true了)
图片 6
Class_student表的数据
图片 7

        INSERTED和DELETED

LOAD DATABASE
/ LOAD LOG / RECONFIGURE

一:触发器的优点

 1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。

 2.触发器可以通过数据库中的相关表进行层叠修改。

 3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是,触发器可以引用其他表中的列。

回到顶部

四:触发器的工作原理

触发器触发时:

  1. 系统自动在内存中创建deleted表或inserted表;
  2. 只读,不允许修改,触发器执行完成后,自动删除。

inserted表:

  1. 临时保存了插入或更新后的记录行;
  2. 可以从inserted表中检查插入的数据是否满足业务需求;
  3. 如果不满足,则向用户发送报告错误消息,并回滚插入操作。 

deleted表:

  1. 临时保存了删除或更新前的记录行;
  2. 可以从deleted表中检查被删除的数据是否满足业务需求;
  3. 如果不满足,则向用户报告错误消息,并回滚插入操作。

inserted表和deleted表对照: 

修改操作记录 inserted表 deleted表
增加(insert)记录 存放新增的记录 …………
删除(deleted)记录 ………….. 存放被删除的记录
修改(update)记录 存放更新后的记录 存放更新前的记录

 

 

 

图片 8

1.3.1.2.DELETE触发器

当针对目标数据库运行DELETE语句时就会激活DELETE触发器。用户直接运行DELETE语句和使用DELETE触发器又有所不同,当激活DELETE触发器后,从受触发器影响的表中删除的行会被放置在一个特殊的临时表——DELETED表中。DELETED表还允许引用由初始化DELETE语句产生的日志数据。
DELETE触发器被激活时,需要考虑以下几点

  • 当某行被添加到DELETED表中时就不存在于数据库表,因此数据库表和DELETED表不可能存在相同行。
  • 系统自动创建DELETED表时,空间从内存中分配。DELETED表被存储在高速缓存中。
  • DELETE操作定义的触发器并不执行TRUNCATE
    TABLE
    语句,原因在于日志不记录TRUNCATE TABLE语句。

示例4:为student表定义一个DELETE触发器,当删除一条学生信息时,class_student表中该学生的分班信息也会被删除
执行下面的语句

CREATE TRIGGER delete_student
ON student
FOR DELETE
AS
DECLARE @stu_no VARCHAR(8)
DECLARE stu_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor
FETCH NEXT FROM stu_cursor INTO @stu_no
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM class_student
WHERE stu_no=@stu_no
FETCH NEXT FROM stu_cursor INTO @stu_no
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

测试delete_student触发器的正确性
Student表的数据如图所示
图片 9
Class_student表的数据如图所示
图片 10
执行下列语句

DELETE FROM student
WHERE stu_enter_score<=351
--在student表中删除入学成绩小于分的学生

student表来看,只有入学编号为2018001120180012的学生成绩被删除。该操作激活了delete_student触发器
Class_student表的数据如图所示
图片 11
入学编号为2018001120180012的学生分班信息已经从class_student表中自动删除。

        在SQL SERVER
2008中,DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中,我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后,它们也就会被自动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的记录。比如你插入一条数据,那么就会把这条记录插入到INSERTED表:DELETED表用于存放你在操作 
insert、update、delete语句前,你创建触发器表中数据库。比如你原来的表中有三条数据,那么他也有三条数据。也就是说,我们可以使用这两个临时的驻留内存的表,测试某些数据修改的效果及设置触发器操作的条件。

RESTORE
DATABASE  / RESTORE LOG

二:触发器的作用

 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要作用主要有以下接个方面:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中所有相关的表,自动触发其它与之相关的操作
  3. 跟踪变化,撤销或回滚违法操作,防止非法修改数据
  4. 返回自定义的错误消息,约束无法返回信息,而触发器可以
  5. 触发器可以调用更多的存储过程

回到顶部

五:创建触发器

 创建触发器的语法: 

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

 准备测试数据:

--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)

1.创建insert触发器

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中,更新stuCount
--因为是后触发器,所以先插入数据后,才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;    
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);            
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);                
select stuCount 学生总人数 from student_sum;

执行上面的语句后,结果如下图所示:

图片 12

 既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的,所以学生总数表应该禁止用户向其中插入数据

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录,操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end 

--触发触发器insert_forbidden
insert student_sum (stuCount) values(5);

结果如下:

图片 13

 2.创建delete触发器

  用户执行delete操作,就会激活delete触发器,从而控制用户能够从数据库中删除数据记录,触发delete触发器后,用户删除的记录会被添加到deleted表中,原来表的相应记录被删除,所以在deleted表中查看删除的记录。

--创建delete触发器
create trigger trig_delete
on student 
after delete
as
begin
    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age
    from deleted
end;

--执行一一条delete语句触发trig_delete触发器
delete from student where stu_id=1;

结果如下:

图片 14

 3.创建UPDATE触发器

  update触发器是当用户在指定表上执行update语句时被调用被调用,这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作:更新前的记录存储在deleted表中,更新后的记录存储在inserted表中。

--创建update触发器
create trigger trig_update
on student
after update
as
begin
    declare @stuCount int;
    select @stuCount=count(*) from student;
    update student_sum set stuCount =@stuCount;
    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted
    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted
end

--创建完成,执行一条update语句触发trig_update触发器
update student set stu_name='张飞' where stu_id=2;

图片 15

 4.创建替代触发器

  与前面介绍的三种after触发器不同,SqlServer服务器在执行after触发器的sql代码后,先建立临时的inserted表和deleted表,然后执行代码中对数据库操作,最后才激活触发器中的代码。而对于替代(instead
of**
)触发器,SqlServer服务器在执行触发instead
of
触发器的代码时,先建立
临时的inserted表和deleted表,然后直接触发instead
of触发器,而拒绝执行用户输入的DML操作语句。**

--创建instead of 触发器 
create trigger trig_insteadOf
on student 
instead of insert
as 
begin
    declare @stuAge int;
    select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
    select '插入年龄错误' as '失败原因'
end

创建完成,执行一条insert语句触发触发器trig_insteadOf

图片 16

5.嵌套触发器介绍

 如果一个触发器在执行操作时调用了另外一个触发器,而这个触发器又接着调用了下一个触发器,那么就形成了嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

 

  嵌套触发器不一定要形成一个环,它可以 T1->T2->T3…这样一直触发下去,最多允许嵌套 32 层。如果嵌套的次数超过限制,那么该触发器将被终止,并回滚整个事务,使用嵌套触发器需要注意以下几点:

  • 默认情况下,嵌套触发器配置选项是开启的。
  • 在同一个触发器事务中,一个嵌套触发器不能被触发两次。
  • 由于触发器是一个事务,如果在一系列嵌套触发器的任意层次中发生错误,则整个事物都将取消,而且所有数据回滚。

嵌套是用来保持整个数据库的完整性的重要功能,但有时可能需要禁用嵌套,如果禁用了嵌套,那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下,需要禁用嵌套触发器:

  • 嵌套触发要求复杂而有理论的设计,级联修改可能会修改用户不想涉及的数据。
  • 在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器,尽管这时数据库提供很强的保护功能,但如果以特定的顺序更新表,就会产生问题。

使用下列语句禁用嵌套和再次启用嵌套:

--禁用嵌套
exce sp_configure 'nested triggers',0;
--启用嵌套
exce sp_configure 'nested triggers',1;

6.递归触发器

  触发器的递归是指一个触发器从其内部再一次激活该触发器,例如update操作激活的触发器内部还有一条数据表的更新语句,那么这个更新语句就有可能激活这个触发器本身,当然,这种递归的触发器内部还会有判断语句,只有一定情况下才会执行那个T_SQL语句,否则就成为无线调用的死循环了。

SqlServer中的递归触发器包括两种:直接递归和间接递归。

  • 直接递归:触发器被触发后并执行一个操作,而该操作又使用一个触发器再次被触发。
  • 间接递归:触发器被触发并执行一个操作,而该操作又使另一个表中的某个触发器被触发,第二个触发器使原始表得到更新,从而再次触发第一个触发器。

默认情况下,递归触发器选项是禁用的。递归触发器最多只能递归16层,如果递归中的第16个触发器激活了第17个触发器,则结果与发布的rollback命令一样,所有数据都将回滚。 

我们举例解释如下,假如有表1、表2名称分别为 T1、T2,在 T1、T2 上分别有触发器 G1、G2。

  • 间接递归:对 T1 操作从而触发 G1,G1 对 T2 操作从而触发 G2,G2 对 T1 操作从而再次触发 G1…
  • 直接递归:对 T1 操作从而触发 G1,G1 对 T1 操作从而再次触发 G1… 

设置直接递归:

默认情况下是禁止直接递归的,要设置为允许有两种方法:

  • T-SQL:exec sp_dboption ‘dbName’, ‘recursive triggers’, true;
  • EM:数据库上点右键->属性->选项。 

1.3.1.3.UPDATE触发器

当针对目标数据库运行UPDATE语句时就会激活UPDATE触发器。对UPDATE触发器来说,临时表INSERTEDDELETED依然有用。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来确定是否更新了多行和如何执行触发器动作。
Student表的数据如图所示
图片 17
Class_student表的数据如图所示
图片 18
示例5:当student表中的stu_no字段更新时,同步更新class_student表中的stu_no字段
执行下列语句新建触发器update_stu_no_single

CREATE TRIGGER update_stu_no_single
ON student
FOR UPDATE
AS
IF UPDATE(stu_no)
BEGIN
UPDATE class_student
SET stu_no=(SELECT stu_no FROM inserted)
WHERE stu_no=(SELECT stu_no FROM deleted)
END
GO

验证update_stu_no_single触发器是否正确,在Student表中执行下列语句,将student表中stu_no为“20180101”的学生的stu_no改成00000000

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

执行成功后,update_stu_no_single触发器被激活,class_student表的数据如图所示
图片 19

注:update_stu_no_single触发器只能对单行记录的UPDATE操作起效,如果批量UPDATE
stu_no
,执行语句时会提示子查询返回的值不止1个。下面的示例6将提供批量UPDATE
stu_no
的触发器

示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也同步批量更新
首先将student表和class_student表的数据修改成原来的样子,并且删除update_stu_no_single触发器
Student表的数据如图所示
图片 20
Class_student表的数据如图所示
图片 21
执行下列语句新建触发器update_stu_no_batch

CREATE TRIGGER update_stu_no_batch
ON student
FOR UPDATE
AS
DECLARE @stu_no_insert VARCHAR(8),@stu_no_delete VARCHAR(8)
DECLARE stu_cursor_insert CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM inserted
OPEN stu_cursor_insert
DECLARE stu_cursor_delete CURSOR LOCAL FORWARD_ONLY
FOR SELECT stu_no FROM deleted
OPEN stu_cursor_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE class_student
SET stu_no=@stu_no_insert
WHERE stu_no=@stu_no_delete
FETCH NEXT FROM stu_cursor_insert INTO @stu_no_insert
FETCH NEXT FROM stu_cursor_delete INTO @stu_no_delete
END
CLOSE stu_cursor_insert
CLOSE stu_cursor_delete
DEALLOCATE stu_cursor_insert
DEALLOCATE stu_cursor_delete
GO

验证update_stu_no_batch触发器的准确性,对student表执行下列语句,实现批量修改操作

UPDATE student
SET stu_no='00000000'
WHERE stu_no LIKE '201801%'
GO

Student表的数据如图所示
图片 22
Class_student表的数据如图所示
图片 23
我们再来验证update_stu_no_batch触发器对更新单行stu_no数据是否有效。将student表class_student表的数据改回原来的样子,然后执行下列语句

UPDATE student
SET stu_no='00000000'
WHERE stu_no='20180101'

Class_student表的数据如图所示
图片 24

注:在将表数据改成原来的样子时,直接在编辑前200行中操作或者用T-SQL语句操作,对student表数据操作,不成功的话要考虑受键和约束的影响,对class_student表数据操作,不成功的话要考虑受触发器影响。

        触发器的优缺点:

自动事务处理模式下,还是在隐式或显示事务处理模式下,只要在
触发器中发出 BEGIN TRANSACTION
语句,实际上就开始了一个嵌套事务,当触发器中使用 ROLLBACK TRANSACTION
语句回滚嵌套事务时,触发器本身发出的所有的 BEGIN TRANSACTION
语句回滚嵌套事务时,触发器本身发出的额所有 BEGIN TRANSACTION
语句豆浆被忽略, ROLLBACK 将回滚到最外部的 BEGIN TRANSACTION 。而在 这
最外部的 之前的 事务都已经提交的就不会收到影响,

三:触发器的分类

 SqlServer包括三种常规类型的触发器:DML触发器、DDL触发器和登录触发器。

1.DML(数据操作语言,Data Manipulation Language)触发器

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

  1. insert触发器:向表中插入数据时被触发;
  2. delete触发器:从表中删除数据时被触发;
  3. update触发器:修改表中数据时被触发。

当遇到下列情形时,应考虑使用DML触发器:

  1. 通过数据库中的相关表实现级联更改
  2. 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他限制。
  3. 评估数据修改前后表的状态,并根据该差异才去措施。

2.DDL(数据定义语言,Data Definition Language)触发器

 DDL触发器是当服务器或者数据库中发生数据定义语言(主要是以create,drop,alter开头的语句)事件时被激活使用,使用DDL触发器可以防止对数据架构进行的某些更改或记录数据中的更改或事件操作。

3.登录触发器

    登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败,将不激发登录触发器。

回到顶部

六:管理触发器 

1.查看触发器

(1).查看数据库中所有的触发器

--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

sysobjects 保存着数据库的对象,其中 xtype 为 TR 的记录即为触发器对象。在 name 一列,我们可以看到触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名
go
exec sp_helptext '触发器名称'

 将会以表的样式显示触发器内容。 

 除了触发器外,sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的属性

  sp_helptrigger 有两个参数:第一个参数为表名;第二个为触发器类型,为 char(6) 类型,可以是 INSERT、UPDATE、DELETE,如果省略则显示指定表中所有类型触发器的属性。

use 数据库名
go
exec sp_helptrigger tableName

2.禁用启用触发器

  禁用:alter table 表名 disable trigger 触发器名称
  启用:alter table 表名 enable trigger 触发器名称

  如果有多个触发器,则各个触发器名称之间用英文逗号隔开。

  如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器。

3修改触发器

--修改触发器语法
ALTER TRIGGER  trigger_name 
     ON  table_name 
     [ WITH ENCRYPTION ] 
     FOR {[DELETE][,][INSERT][,][UPDATE]}
     AS
       sql_statement;

4.删除触发器

 --语法格式:
      DROP  TRIGGER   { trigger } [ ,...n ]
参数:
 trigger: 要删除的触发器名称
 n:表示可以删除多个触发器的占位符       

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器可以指定执行触发器,而不是执行触发SQL语句,从而屏蔽原来的SQL语句,而转向执行触发器内部的语句。每个表或者视图只能有1个INSTEAD
OF
触发器。INSTEAD
OF
触发器的特点是,能够使作为触发条件的SQL语句不执行。
Membership表的数据如图所示
图片 25
Call_slip表的数据如图所示
图片 26
示例7:对LibraryManagement数据库里的membership表写一个防删除触发器,尚有借书未还的读者无法被删除
执行下列语句创建member_delete_single触发器

CREATE TRIGGER member_delete_single
ON membership
INSTEAD OF DELETE
AS
BEGIN
IF NOT EXISTS(SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=(SELECT member_id FROM deleted)
ELSE
BEGIN
SELECT '该用户尚有图书未还,无法删除'
SELECT * FROM call_slip WHERE member_id=(SELECT member_id FROM deleted) AND borrow_state='未归还'
END
END
GO

验证触发器的正确性,执行下列语句

DELETE FROM membership
WHERE member_id='20060128'

结果如图所示
图片 27
该触发器只针对DELETE一条数据有效
示例8:对LibraryManagement数据库里的membership表写一个防批量删除触发器,尚有借书未还的读者无法被删除
Membership表的数据如图所示
图片 28
Call_slip表的数据如图所示
图片 29
执行下列语句新建触发器(将示例7中的member_delete_single触发器先删除)

CREATE TRIGGER member_delete_batch
ON membership
INSTEAD OF DELETE
AS
BEGIN
DECLARE member_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT member_id FROM deleted
OPEN member_cursor
DECLARE @member_id VARCHAR(8)
FETCH NEXT FROM member_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF NOT EXISTS(SELECT* FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')
DELETE FROM membership WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'无法删除'
END
FETCH NEXT FROM member_cursor INTO @member_id
END
CLOSE member_cursor
DEALLOCATE member_cursor
END
GO

结果如图所示
图片 30
Membership表的数据如图所示
图片 31
示例9:对LibraryManagement数据库里的call_slip表写一个防超借触发器,一个读者的未还图书最多只能有5本,超出不能再借(这里还是针对批量处理数据创建触发器)
Call_slip表的数据如图所示
图片 32
执行下列语句创建provent_overborrowing_batch触发器

CREATE TRIGGER provent_overborrowing_batch
ON call_slip
INSTEAD OF INSERT
AS
BEGIN
DECLARE @member_id VARCHAR(8)
DECLARE borrow_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT member_id FROM inserted
OPEN borrow_cursor
FETCH NEXT FROM borrow_cursor INTO @member_id
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
IF (SELECT COUNT(*) FROM call_slip WHERE member_id=@member_id AND borrow_state='未归还')<5
INSERT INTO call_slip SELECT * FROM inserted WHERE member_id=@member_id
ELSE
PRINT '用户'+@member_id+'已借阅且未还的图书超过5本,无法再借'
END
FETCH NEXT FROM borrow_cursor INTO @member_id
END
END
GO

执行下列语句测试provent_overborrowing_batch触发器的正确性,其中member_id为“20060128”的用户借书未还超过5本,应该是无法再借的。

--测试数据
INSERT INTO call_slip(book_id,member_id,loan_period,borrow_state)
VALUES('20130002','20060128','30','未归还'),
('20130001','20060128','20','未归还'),
('20130003','20060128','30','未归还'),
('20130004','20062919','30','未归还'),
('20130005','20150821','45','未归还')

结果如图所示
图片 33
Call_slip表的数据如图所示,红框里是新插入的数据
图片 34

     
  触发器可通过数据库中的相关表实现级联更改,可以强制比用CHECK约束定义的约束更为复杂的约束。与
CHECK
约束不同,触发器可以引用其它表中的列,例如触发器可以使用另一个表中的
SELECT
比较插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE
或 DELETE)允许采取多个不同的对策以响应同一个修改语句。

而这个 回滚操作也会终止 批处理中 对 该语句后面语句的执行。

四:触发器的工作原理

触发器触发时:

  1. 系统自动在内存中创建deleted表或inserted表;
  2. 只读,不允许修改,触发器执行完成后,自动删除。

inserted表:

  1. 临时保存了插入或更新后的记录行;
  2. 可以从inserted表中检查插入的数据是否满足业务需求;
  3. 如果不满足,则向用户发送报告错误消息,并回滚插入操作。 

deleted表:

  1. 临时保存了删除或更新前的记录行;
  2. 可以从deleted表中检查被删除的数据是否满足业务需求;
  3. 如果不满足,则向用户报告错误消息,并回滚插入操作。

inserted表和deleted表对照: 

修改操作记录 inserted表 deleted表
增加(insert)记录 存放新增的记录 …………
删除(deleted)记录 ………….. 存放被删除的记录
修改(update)记录 存放更新后的记录 存放更新前的记录

 

 

 

图片 8

回到顶部

1.3.2.创建DDL触发器

DDL触发器只为了响应CREATEDROPALTER事件而激活,它的作用域是整个数据库或者服务器,而不是作用域某张表或试图。它可以有效控制哪位用户可以修改数据库结构以及如何修改。
示例10:创建一个DDL触发器,控制上班时间(8:00-18:00)不能对LibraryManagement数据库表和试图结构进行新建,修改和删除操作。
执行下列语句创建触发器deny_DDL_table

CREATE TRIGGER deny_DDL_table
ON DATABASE
WITH ENCRYPTION
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
AS
DECLARE @eventdata XML
SET @eventdata=EVENTDATA()
IF(DATEPART(HOUR,GETDATE()) BETWEEN 8 AND 17)
BEGIN
SELECT '触发器deny_DDL_table已禁止工作时间8:00-18:00对LibraryManagement数据库的CREATE,ALTER,DROP操作'
SELECT @eventdata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') AS EventType,--事件类型
@eventdata.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') AS PostTime,--时间触发的时间
@eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') AS DatabaseName,--数据库名字
@eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') AS ObjectName,--操作的对象名称
@eventdata.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') AS ObjectType,--操作的对象类型
@eventdata.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText--操作命令文本
ROLLBACK---对操作进行回滚,也可以不回滚
END
GO

执行以下代码以测试DDL触发器deny_DDL_table的正确性

USE LibraryManagement
CREATE TABLE test(
t_id VARCHAR(2),
t_name VARCHAR(20)
)

结果如图所示
图片 36
图片 37

注:EVENTDATA()可在触发器内部使用,返回有关数据库和服务器事件的信息,以XML格式返回。只有直接在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会返回数据。如果EVENTDATA由其他例程调用(即使这些例程由DDL或登录触发器进行调用),将返回
NULL

       
与此同时,虽然触发器功能强大,轻松可靠地实现许多复杂的功能,为什么又要慎用?过多触发器会造成数据库及应用程序的维护困难,同时对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程序。

因此,若要在 触发器中进行部分回滚,应当使用 SAVE TRANSACTION
语句设置一个事务保存点,这样就不会回滚到 外部的 事务中去了。

五:创建触发器

 创建触发器的语法: 

图片 38😉

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

图片 39😉

 准备测试数据:

图片 40😉

--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)

图片 41😉

1.创建insert触发器

图片 42😉

--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end

图片 43😉

图片 44😉

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中,更新stuCount
--因为是后触发器,所以先插入数据后,才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;    
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);            
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);                
select stuCount 学生总人数 from student_sum;

图片 45😉

执行上面的语句后,结果如下图所示:

图片 12

 既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的,所以学生总数表应该禁止用户向其中插入数据

图片 47😉

--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录,操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end 

图片 48😉

--触发触发器insert_forbidden
insert student_sum (stuCount) values(5);

结果如下:

图片 13

 2.创建delete触发器

  用户执行delete操作,就会激活delete触发器,从而控制用户能够从数据库中删除数据记录,触发delete触发器后,用户删除的记录会被添加到deleted表中,原来表的相应记录被删除,所以在deleted表中查看删除的记录。

图片 50😉

--创建delete触发器
create trigger trig_delete
on student 
after delete
as
begin
    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age
    from deleted
end;

图片 51😉

--执行一一条delete语句触发trig_delete触发器
delete from student where stu_id=1;

结果如下:

图片 14

 3.创建UPDATE触发器

  update触发器是当用户在指定表上执行update语句时被调用被调用,这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作:更新前的记录存储在deleted表中,更新后的记录存储在inserted表中。

图片 53😉

--创建update触发器
create trigger trig_update
on student
after update
as
begin
    declare @stuCount int;
    select @stuCount=count(*) from student;
    update student_sum set stuCount =@stuCount;
    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted
    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted
end

图片 54😉

--创建完成,执行一条update语句触发trig_update触发器
update student set stu_name='张飞' where stu_id=2;

图片 15

 4.创建替代触发器

  与前面介绍的三种after触发器不同,SqlServer服务器在执行after触发器的sql代码后,先建立临时的inserted表和deleted表,然后执行代码中对数据库操作,最后才激活触发器中的代码。而对于替代(instead
of**
)触发器,SqlServer服务器在执行触发instead
of
触发器的代码时,先建立
临时的inserted表和deleted表,然后直接触发instead
of触发器,而拒绝执行用户输入的DML操作语句。**

图片 56😉

--创建instead of 触发器 
create trigger trig_insteadOf
on student 
instead of insert
as 
begin
    declare @stuAge int;
    select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
    select '插入年龄错误' as '失败原因'
end

图片 57😉

创建完成,执行一条insert语句触发触发器trig_insteadOf

图片 16

5.嵌套触发器介绍

 如果一个触发器在执行操作时调用了另外一个触发器,而这个触发器又接着调用了下一个触发器,那么就形成了嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用系统存储过程sp_configure禁用和重新启用嵌套触发器。

 

  嵌套触发器不一定要形成一个环,它可以 T1->T2->T3…这样一直触发下去,最多允许嵌套 32 层。如果嵌套的次数超过限制,那么该触发器将被终止,并回滚整个事务,使用嵌套触发器需要注意以下几点:

  • 默认情况下,嵌套触发器配置选项是开启的。
  • 在同一个触发器事务中,一个嵌套触发器不能被触发两次。
  • 由于触发器是一个事务,如果在一系列嵌套触发器的任意层次中发生错误,则整个事物都将取消,而且所有数据回滚。

嵌套是用来保持整个数据库的完整性的重要功能,但有时可能需要禁用嵌套,如果禁用了嵌套,那么修改一个触发器的实现不会再触发该表上的任何触发器。在下述情况下,需要禁用嵌套触发器:

  • 嵌套触发要求复杂而有理论的设计,级联修改可能会修改用户不想涉及的数据。
  • 在一系列嵌套触发器中的任意点的时间修改操作都会触发一些触发器,尽管这时数据库提供很强的保护功能,但如果以特定的顺序更新表,就会产生问题。

使用下列语句禁用嵌套和再次启用嵌套:

--禁用嵌套
exce sp_configure 'nested triggers',0;
--启用嵌套
exce sp_configure 'nested triggers',1;

6.递归触发器

  触发器的递归是指一个触发器从其内部再一次激活该触发器,例如update操作激活的触发器内部还有一条数据表的更新语句,那么这个更新语句就有可能激活这个触发器本身,当然,这种递归的触发器内部还会有判断语句,只有一定情况下才会执行那个T_SQL语句,否则就成为无线调用的死循环了。

SqlServer中的递归触发器包括两种:直接递归和间接递归。

  • 直接递归:触发器被触发后并执行一个操作,而该操作又使用一个触发器再次被触发。
  • 间接递归:触发器被触发并执行一个操作,而该操作又使另一个表中的某个触发器被触发,第二个触发器使原始表得到更新,从而再次触发第一个触发器。

默认情况下,递归触发器选项是禁用的。递归触发器最多只能递归16层,如果递归中的第16个触发器激活了第17个触发器,则结果与发布的rollback命令一样,所有数据都将回滚。 

我们举例解释如下,假如有表1、表2名称分别为 T1、T2,在 T1、T2 上分别有触发器 G1、G2。

  • 间接递归:对 T1 操作从而触发 G1,G1 对 T2 操作从而触发 G2,G2 对 T1 操作从而再次触发 G1…
  • 直接递归:对 T1 操作从而触发 G1,G1 对 T1 操作从而再次触发 G1… 

设置直接递归:

默认情况下是禁止直接递归的,要设置为允许有两种方法:

  • T-SQL:exec sp_dboption ‘dbName’, ‘recursive triggers’, true;
  • EM:数据库上点右键->属性->选项。 

回到顶部

1.3.3.嵌套触发器

语法

注意:  在 触发器中 书写 COMMIT TRANSACTION 的 语句,如果之前有 BEGIN
TRANSACTION 语句,会被认为是

六:管理触发器 

1.查看触发器

(1).查看数据库中所有的触发器

--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

sysobjects 保存着数据库的对象,其中 xtype 为 TR 的记录即为触发器对象。在 name 一列,我们可以看到触发器名称。

(2).sp_helptext 查看触发器内容

use 数据库名
go
exec sp_helptext '触发器名称'

 将会以表的样式显示触发器内容。 

 除了触发器外,sp_helptext 还可以显示 规则、默认值、未加密的存储过程、用户定义函数、视图的文本。

(3).sp_helptrigger 用于查看触发器的属性

  sp_helptrigger 有两个参数:第一个参数为表名;第二个为触发器类型,为 char(6) 类型,可以是 INSERT、UPDATE、DELETE,如果省略则显示指定表中所有类型触发器的属性。

use 数据库名
go
exec sp_helptrigger tableName

2.禁用启用触发器

  禁用:alter table 表名 disable trigger 触发器名称
  启用:alter table 表名 enable trigger 触发器名称

  如果有多个触发器,则各个触发器名称之间用英文逗号隔开。

  如果把“触发器名称”换成“ALL”,则表示禁用或启用该表的全部触发器。

3修改触发器

图片 59😉

--修改触发器语法
ALTER TRIGGER  trigger_name 
     ON  table_name 
     [ WITH ENCRYPTION ] 
     FOR {[DELETE][,][INSERT][,][UPDATE]}
     AS
       sql_statement;

图片 60😉

4.删除触发器

 --语法格式:
      DROP  TRIGGER   { trigger } [ ,...n ]
参数:
 trigger: 要删除的触发器名称
 n:表示可以删除多个触发器的占位符       

原文转自:

1.3.3.1.嵌套触发器

如果一个触发器在执行操作时引发了另一个触发器,而这个触发器又引发了下一个触发器,那么这些触发器就是嵌套触发器。嵌套触发器在安装时就被启用,但是可以使用sp_configure存储过程禁用和重新启用嵌套。
DML触发器和DDL触发器最多可以嵌套32层,可以通过nested
triggers
来配置是否可以嵌套AFTER触发器,但是不管此设置如何都可以嵌套INSTEAD
OF
触发器。如果嵌套触发器进入了无限循环,该触发器将被终止,并且回滚整个事务。嵌套触发器具有多种用途,比如保存前一个触发器所影响的行的副本。
使用嵌套触发器时应该注意以下几点:

  • 默认情况下,嵌套触发器配置选项开启。
  • 在同一个触发器事务中,一个触发器不会被触发两次,触发器不会调用他自己来响应触发器中对同一个表的第二次更新
  • 由于触发器是一个事务,一旦嵌套中任何一层的触发器出现错误,将回滚整个事务。

示例11:有teacher_course表(教师所教课程表),course表(课程表)和course_selection表(学生选课表),写一个嵌套触发器,实现课程取消后,删除教师所教课程表中关于该课程的记录,而教师所教课程表中该课程的记录被取消,导致该课程的学生选课记录也做相应取消。
执行下列语句

--创建course表上的触发器,删除course表中的课程,teacher_course表中的记录做对应删除
CREATE TRIGGER course_delete_batch
ON course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT course_id FROM deleted
OPEN course_cursor
FETCH NEXT FROM course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE course_id=@course_id
FETCH NEXT FROM course_cursor INTO @course_id
END
GO
--创建teacher_course表上的触发器,删除教师课程表的记录,学生选课表的记录也做对应删除
CREATE TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
END
GO

course_delete_batch和**
teacher_course_delete_batch就形成了一个嵌套触发器,下面来验证嵌套触发器的正确性。 Course表中的数据如图所示
图片 61
Teacher_course表中的数据如图所示
图片 62
Course_selection**表中的数据如图所示
图片 63
以课程0013为例,执行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数据如图所示
图片 64
Teacher_course表的数据如图所示
图片 65
Course_selection表的数据如图所示
图片 66
所有关于0013课程的数据都被删除。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,我额外加入了一个判断,当teacher_course表中还有老师在教授这门课程时,所有关于这门课程的学生选课信息都不予删除。这样做在嵌套触发器里是多余的,删除一门课程,必然会删除teacher_course表中所有与这门课程有关的记录,也必然删除course_selection表中所有与这门课程有关的记录,但是,这样做可以保证该触发器能够独立于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能用于其他嵌套触发器中,看示例12

示例12:有teacher表(教师信息表),teacher_course(教师所教课程表),和course_selection表(学生选课记录表),写一个嵌套触发器,实现当一个教师离职时,在删除该教师所教课程信息,如果没有教师教这门课程,再删除该课程选课记录。
其中teacher_course表的触发器teacher_course_delete_batch已经在示例11中写完,只需创建teacher表的teacher_delete_batch触发器即可
执行下列代码

CREATE TRIGGER teacher_delete_batch
ON teacher
FOR DELETE
AS
DECLARE @teacher_id CHAR(4)
DECLARE teacher_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT teacher_id FROM deleted
OPEN teacher_cursor
FETCH NEXT FROM teacher_cursor INTO @teacher_id
WHILE @@FETCH_STATUS=0
BEGIN
DELETE FROM teacher_course WHERE teacher_id=@teacher_id
FETCH NEXT FROM teacher_cursor INTO @teacher_id
END
GO

测试嵌套触发器的正确性
Teacher表的数据如图所示
图片 67
Teacher_course表的数据如图所示
图片 68
Course_selection表的数据如图所示
图片 69
以删除0012号教师路易为例,0012号教师教授0013号课程,且teacher_course表中并无其他教师教授0013号课程,按照逻辑要删除teacher_course表中0012号教师的所教课程记录和course_selection表中所有0013号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数据如图所示
图片 70
Teacher_course表的数据如图所示
图片 71
Course_selection表的数据如图所示
图片 72
测试结果正确
参照上面的数据,继续测试另一种情况,以删除0011号教师卢含笑为例,0011号教师教授0012号课程,在teacher_course表中还有其他教师教授该课程,因此嵌套触发器会删除teacher_course表中关于0011号教师教授课程记录,但不会删除course_selection表中关于0012号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 73
Teacher表的数据如图所示
图片 74
Teacher_course表的数据如图所示
图片 75
Course_selection表的数据如图所示
图片 76

        建立触发器

仅提交该嵌套事务,如果 在 commit 之后仍然有 ROLLBACK TRANSACTION
那么仍然会回滚到最外部的 事务。

1.3.3.2.查看触发器嵌套的层数

可以使用@@NESTLEVEL全局变量来查看当前触发器嵌套的层数
示例13:在示例11teacher_course_delete_batch触发器中利用@@NESTLEVEL全局变量查看当前触发器嵌套的层数
执行下列语句修改teacher_course_delete_batch触发器

ALTER TRIGGER teacher_course_delete_batch
ON teacher_course
FOR DELETE
AS
DECLARE @course_id CHAR(4)
DECLARE teacher_course_cursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT DISTINCT course_id FROM deleted
OPEN teacher_course_cursor
FETCH NEXT FROM teacher_course_cursor INTO @course_id
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT COUNT(*) FROM teacher_course WHERE course_id=@course_id)=0
DELETE FROM course_selection WHERE course_id=@course_id
ELSE
PRINT 'course_id为'+@course_id+'的课程依然正常开课,该课程的学生选课情况不予删除'
FETCH NEXT FROM teacher_course_cursor INTO @course_id
SELECT @@NESTLEVEL AS NESTLEVEL
END
GO

测试teacher_course_delete_batch触发器(数据就不看了,未影响触发器原来的功能)
执行下列语句

DELETE FROM teacher_course WHERE teacher_id='0009'
--直接在teacher_course表中删除,激活teacher_course_delete_batch触发器

结果如图所示
图片 77
执行下列语句

DELETE FROM teacher WHERE teacher_id='0009'
--在teacher表中删除,触发teacher_delete_batch触发器,进而触发teacher_course_delete_batch触发器

结果如图所示
图片 78

1 CREATE TRIGGER 触发器名称
2 ON 表名
3 { FOR | AFTER | INSTEAD OF } 
4 { [ INSERT ] [ , ] [ DELETE ] [ , ] 
5    [UPDATE ] }
6 AS 
7   SQL 语句 [ ... n ] 

if (update(name)) 

1.3.3.3.禁用和启用嵌套触发器

EXEC sp_configure 'nested triggers',0;
GO
--禁用嵌套触发器
EXEC sp_configure 'nested triggers',1;
GO
--启用嵌套触发器

        **删除触发器:**

 

1.3.4.递归触发器

 

用来判断 更新的是哪列, COLUMNS_UPDATED() 测试多个列,
但这个列 是 按字节  加起来算的,这个函数返回一个

1.3.4.1.递归触发器

触发器被激活,更改了表中数据,这种更改又激活了它自己,这种触发器被称为递归触发器。数据库创建时默认递归触发器禁用。但可以使用ALTER
DATABASE
选项来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用状态,如果嵌套触发器禁用,不管递归触发器的配置是什么都将被禁用。而在递归触发器中,inserted表和deleted表都只包含被上一次触发器影响的行数据。
递归触发器有以下两种不同类型(这边没有合适的应用示例可举,先不举例了)

1 DROP TRIGGER 触发器名 [ , ... n ]

或多个从左至右排序的字节。 P346 是一个很经典的应用。

1.3.4.2.直接递归

直接递归触发器是指整个递归过程只有它本身一个触发器的参与。自己激活了自己。

 

指定 FIRST
触发器 和 LAST 触发器

1.3.4.3.间接递归

间接递归触发器是指整个递归过程有多个触发器参与,例如A激活B,B激活C,C激活A。可以看成是递归和嵌套的结合。
使用递归触发器时需要注意以下几点:
递归触发器很复杂,需要经过有条理的设计和全面测试
在任意点的数据修改都会激活递归触发器。只能按触发器被激活的特定顺序更新表。
所有触发器一起构成一个大事务,任意触发器的任意位置上的ROLLBACK语句都将取消所有数据的输入,所有数据均被擦除。
触发器最多只能递归16层,一旦有第17个触发器参与进来,结果与ROLLBACK命令一样,所有数据都将被擦除

          修改触发器:

FIRST 和 LAST
触发器之间的执行并没有先后顺序:

1.3.4.4.启用递归触发器

可以使用SQL Server 2008的管理器工具来启用递归触发器。
图片 79

 

sp_settriggerorder 

1.4.管理触发器

禁用和启用触发器
执行下列语句禁用和启用触发器

ALTER TABLE student DISABLE TRIGGER update_stu_no_single
--禁用update_stu_no_single触发器
GO
ALTER TABLE student ENABLE TRIGGER update_stu_no_single
--启用update_stu_no_single触发器
GO

执行下列语句禁用和启用数据库级别触发器

DISABLE TRIGGER deny_DDL_table ON DATABASE
--禁用数据库级别触发器deny_DDL_table
GO
ENABLE TRIGGER deny_DDL_table ON DATABASE
--启用数据库级别触发器deny_DDL_table
GO
1 ALTER TRIGGER 触发器名称
2 ON 表名
3 { FOR | AFTER | INSTEAD OF } 
4 { [ INSERT ] [ , ] [ DELETE ] [ , ] 
5    [UPDATE ] }
6 AS 
7   SQL 语句 [ ... n ] 

@triggername =’ud_trig/ins_trig/del_trig’, @order = ‘first/last’,
@stmttyp = ‘update / insert / delete’;

 

由于 INSTEAD OF 触发器一直在对基础表进行更新前激发,因此不能讲 INSTEAD
OF 触发器指定为 第一或 最后 一个触发器

          开启和禁用:

如果使用了 ALTER TRIGGER 语句 更改了 First 或 Last
触发器,则会删除它们的顺序值,必须使用 sp_settriggerorder 来重新设置。

 

可以通过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger ,
ExecIsFirstInsertTrigger,ExecIsFirstUPdate….等属性来确定触发器时 First
触发器,还是LAST 触发器。

1 disable trigger trigDB on database --禁用触发器
2 enable trigger trigDB on database --开启触发器

嵌套和递归触发器

 

无论是 DML 触发器还是DDL
触发器,如果出现了一个触发器执行启动另一个触发器的操作都属于嵌套触发器。32层

          提醒和保护:

可以通过nested triggers 服务器配置选项来空值是否可以嵌套AFTER 触发器。
INSTEAD OF 触发器嵌套不受此选项影响。参考下面的语句:

 

sp_configure ‘nested triggers’,1 — 设置 为 1 允许 after 触发器嵌套

1 print '删除了触发器***' 
2 raiserror('数据一致性验证',16,1)
3 rollback transaction 

GO

 

RECONFIGURE; –使用新环境值

示例

EXEC sp_configure ‘nested triggers’; –查看 nested triggers 选项设置

 

GO

    在S表创建UPDATE触发器:

递归 P349
 有个经典例子 由于有 update() 函数检测,作为递归终止条件。

1 Create trigger tri_Updates
2 on s
3 for update 
4 as 
5 print 'the table s was updated'

递归分为 

 

直接递归, 如 应用程序更新 T3 表,从而触发了 触发器 Trig3 , Trig3
再次更新表T3,从而再次出发了触发器Trig3

    禁止删除SC表中成绩不及格学生的记录:

间接递归。 即中间经过另外的表中转还是触发了第一张表的触发器:

1 CREATE TRIGGER tri_del_grade
2   ON SC FOR DELETE
3   AS
4     IF EXISTS(SELECT * FROM DELETED    
5           WHERE Grade < 60)
6       ROLLBACK 

应用程序更新了 表 T1, 从而触发了触发器Trig1 , Trig1
更新了表T2,从而出发了触发器 Trig2.Trig2转而更新了 表T1 ,
从而再次触发了 Trig1.

 

注意: 只有在设置 RECURSIVE_TRIGGERS
数据库选项为 ON 的情况下,才允许以递归方式调用AFTER
触发器:

   禁止将SC表中不及格学生的成绩改为及格:

ALTER DATABASE
AdventureWorks 

 1 create trigger tri_update_grade
 2 on sc for update
 3 as 
 4   if update(grade)
 5   if exists(select * from inserted,deleted
 6   where inserted.sno=deleted.sno and inserted.grade>=60 and deleted.grade<60)
 7   begin    
 8     print '不能将不及格的成绩改为及格'
 9   rollback
10   end

SET RECURSIVE_TRIGGERS
ON;

 

Instead of 触发器:

— instead of insert

CREATE TRIGGER Bef_Ins 

ON dbo.test11 

INSTEAD OF INSERT

AS

IF (exists(select * from dbo.Test11 where name = (select name from inserted)))

print ‘exists already!!!’

else

insert into dbo.test11

select name,gender from inserted

— instead of update 同理

instead of 给 insert 跟 update
都必须为不能为空的列指定值,但是在触发器中需要忽略掉这些值。

  1. 在INSTEAD OF 触发器中使用 TEXT, NTEXT 和 IMAGE 数据

数据修改可能会涉及 text/ ntext /image 列。 在基表中, 存储在 text/ ntext
或 image 列中 的 值是文本指针,它只想保存数据的 页  P353

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[INS_TEST]’))

DROP TRIGGER [dbo].[INS_TEST]

GO

CREATE TRIGGER ins_Stu

ON dbo.Students

AFTER INSERT

AS

SELECT * FROM INSERTED

insert into dbo.Students

values (4,’Frank’,88.88)

图片 80

CREATE TRIGGER del_Stu 

ON dbo.Students

FOR DELETE

AS

SELECT * FROM DELETED

DELETE FROM dbo.Students WHERE StudentName = ‘Frank’

图片 81

图片 82

CREATE TRIGGER update_Stu 

ON dbo.Students

FOR UPDATE

AS

SELECT * FROM INSERTED

SELECT * FROM DELETED

insert into dbo.Students

VALUES(4,’Frank’,88.88)

update dbo.Students set ClassID=5 where StudentName=’Frank’

DELETE FROM dbo.Students WHERE StudentName = ‘Frank’

————- DDL 触发器 —————————-

DDL 触发器是为相应一个或多个特定的数据定义语言语句的激发。并且 DDL
触发器只能在 SQL 语句完成之后才运行,无法作为 INSTEAD OF 触发器。

sys.server_triggers 目录视图查询服务器范围内的 DDL 触发器的信息。P354

修改,删除和禁用触发器

DROP TRIGGER MyTrigger

ALTER TRIGGER MyTRIGGER

ON PriTable

AFTER DELETE 

AS

    DELETE FROM DetailTable

    WHERE OrderID in (SELECT OrderID from Deleted);

–禁用 触发器  方法一

Disable trigger dbo.PriTrigger ON dbo.PriTable; — DDL
触发器的话不能包含架构名

–禁用 触发器  方法二

ALTER TABLE dbo.PriTable

    DISABLE TRIGGER PriTrigger;

–重新启用 触发器 方法一:

ENABLE TRIGGER dbo.PriTrigger ON dbo.PriTable

–启用触发器:

ALTER TABLE dbo.PriTable

        ENABLE TRIGGER PriTrigger;

注意要删除一个 DDL
触发器,需要制定触发器的作用域范围,否则将默认为要删除DML 触发器。

DROP TRIGGER MyTrigger

ON DATABASE;

GO

DROP TRIGGER mYoTHERtRIGGER

on all server;

禁用启用也同样要指定作用域范围:

DISABLE TRIGGER Safety

ON DATABASE;

ENABLE TRIGGER Safety 

ON DATABASE;

要修改一个 DDL 触发器,也应当使用 ALTER TRIGGER 语句。 例如:

ALTER TRIGGER Safety

ON DATABASE 

FOR CREATE_TABLE

AS

    PRINT N’CREATE TABLE 出错’;

    SELECT 

EVENTDATA().VALUE(‘(/EVENT_INSTANCE/TSQLCommand/cOMMANDtEXT)[1]’,’nvarchar(max)’);

ROLLBACK;

原文链接

本文由豆约翰博客备份专家远程一键发布

网站地图xml地图