SQL Server 触发器

作者:计算机专家

目录

触发器

概念:

  • 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_T本田UR-VIGGE奥迪Q7S 数据字典中查到。

1.触发器

触发器是一种奇特的存放进程,与表紧凑关系。

          触发器(trigger)是个新鲜的储存进程,它的实践不是由程序调用,亦不是手工业运营,而是由事件来触发,当对三个表实行操作(  insert,delete, update)时就能够激活它实行,触发器日常用来升高数据的完整性约束和业务法则等。以小编之见触发器实际上就是三个事变,就好像C#中,点击四个开关会触发相应的操作。

触发器和仓库储存进度的区分:

1.1.DDL触发器

当服务器或数据库中发出多少定义语言(DDL)事件时将被调用。如CREATE,ALTERDROP等操作。若是要实行以下操作,能够使用DDL触发器:

  • 防护对数据库架构举行转移
  • 希望数据库中产生一些情状以响应数据库架构中的改换
  • 要记录数据库架构中的更动或事件

        触发器的分类:

  触发器与存储进度的分别是运营形式的不等,触发器不能够实行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( 数据垄断(monopoly)语言 Data Manipulation Language)触发器:是指触发器在数据库中生出DML事件时将启用。DML事件即指在表或视图中期维修改数据的insert、update、delete语句。         (2)DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发出(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。         (3)登入触发器:是指当客商登陆SQL SEPAJEROVEHighlander实例创立会话时接触。

一:触发器的优点

 1.触发器是机动的。当对表中的数量做了别的修改现在立刻被激活。

 2.触发器能够经过数据库中的相关表张开层叠修改。

 3.触发器可以强制限制。那一个限制比用CHECK约束所定义的更目眩神摇。与CHECK约束分裂的是,触发器能够援引别的表中的列。

1.3.创造触发器

        个中DML触发器最为常用,依照DML触发器触发的方法分化又分为以下三种情况:

二:触发器的成效

 触发器的非常重要功能就是其能够达成由主键和外键所不可能担保的复杂性参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,进步比CHECK约束更头晕目眩的的数据完整性,并自定义错误音信。触发器的显要功能至关心保养要有以下接个地点:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中保有有关的表,自动触发别的与之有关的操作
  3. 钉住变化,裁撤或回滚违规操作,幸免违法修改数据
  4. 归来自定义的不当音信,约束无法回去音信,而触发器能够
  5. 触发器能够调用越来越多的存款和储蓄进程

1.3.1.创建DML触发器

      (1)AFTEPAJERO触发器:它是在推行INSERT、UPDATE、DELETE语句操作之后施行触发器操作。它重借使用来记录改动后的管理或检查,一旦爆发错误,能够用Rollback Transaction语句来回滚此次扣件,可是无法对视图定义AFTE奥迪Q3触发器。       (2)INSTEAD OF触发器:它在奉行INSERT、UPDATE、DELETE语句操作在此以前实践触发器本人所定义的操作。而INSTEAD OF触发器是能够定义在视图上的。

三:触发器的分类

 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 实例构建客商会话时将掀起那件事件。登入触发器将在登入的身份验证阶段完毕之后且客商会话实际构建从前激发。因而,来自触发器内部且平时将达到客商的保有音信(举例错误新闻和来自 PPRADOINT 语句的新闻)会传送到 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的代码比较,示例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

四:触发器的办事原理

触发器触发时:

  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 SE途乐VE凯雷德二〇〇八中,DML触发器的落成应用八个逻辑表DELETED和INSERTED。那多个表是成立在数据库服务器的内存中,大家仅有只读的权能。DELETED和INSERED表的布局和触发器所在的数据表的结构是一律的。当触发器实践到位后,它们也就能够被电动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的笔录。比如您插入一条数据,那么就能把这条记下插入到INSERTED表:DELETED表用于寄放你在操作  insert、update、delete语句前,你创制触发器表中数据库。举个例子你本来的表中有三条数据,那么他也会有三条数据。也便是说,大家能够行使那三个有的时候的驻留内部存款和储蓄器的表,测量试验有个别数据修改的功能及安装触发器操作的基准。

五:创设触发器

 创立触发器的语法: 

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层,假设递归中的第14个触发器激活了第15个触发器,则结果与公布的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表数据操作,不成功的话要考虑受触发器影响。

        触发器的利弊:

六:管理触发器 

1.翻看触发器

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

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

sysobjects 保存着数据库的靶子,此中 xtype 为 T奥德赛 的记录即为触发器对象。在 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数据Curry的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数据Curry的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.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)
)

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

注:EVENTDATA()可在触发器内部选择,重临有关数据库和服务器事件的音讯,以XML格式重返。只有一贯在DDL或登陆触发器内部援引EVENTDATA时,EVENTDATA才会回去数据。如果EVENTDATA由其他例程调用(就算这个例程由DDL或登入触发器举行调用),将回到 NULL

        与此同不经常常候,纵然触发器功效庞大,轻便可信地贯彻无数繁缛的效果,为啥又要慎用?过多触发器会促成数据库及应用程序的护卫困难,同有时间对触发器过分的借助,势必影响数据库的结构,同一时间增添了保卫安全的复杂性程序。

1.3.3.嵌套触发器

语法

1.3.3.1.嵌套触发器

万一一个触发器在实行操作时引发了另三个触发器,而以此触发器又抓住了下多少个触发器,那么那一个触发器正是嵌套触发器。嵌套触发器在装置时就被启用,不过足以应用sp_configure积累进度禁止使用和再度启用嵌套。
DML触发器和DDL触发器最多能够嵌套32层,能够经过nested triggers来安顿是还是不是足以嵌套AFTER触发器,不过无论此设置哪些都能够嵌套INSTEAD OF触发器。借使嵌套触发器踏入了Infiniti循环,该触发器将被甘休,并且回滚整个事情。嵌套触发器材备种种用处,举个例子保留前叁个触发器所影响的行的别本。
使用嵌套触发器时应当小心以下几点:

  • 暗许意况下,嵌套触发器配置选项开启。
  • 在同贰个触发器事务中,三个触发器不会被触发五次,触发器不会调用他协和来响应触发器中对同贰个表的第一回革新
  • 由于触发器是二个事情,一旦嵌套中任何一层的触发器出现谬误,将回滚整个职业。

示例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表中的数据如图所示
图片 37
Teacher_course表中的数据如图所示
图片 38
Course_selection**表中的数据如图所示
图片 39
以课程0013为例,实施下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数据如图所示
图片 40
Teacher_course表的数量如图所示
图片 41
Course_selection表的数码如图所示
图片 42
具备关于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表的数额如图所示
图片 43
Teacher_course表的多寡如图所示
图片 44
Course_selection表的数量如图所示
图片 45
以删除0012号教授路易为例,0012号教授讲课0013号课程,且teacher_course表中并无任何导师讲课0013号课程,根据逻辑要去除teacher_course表中0012号教师的所教课程记录和course_selection表中颇有0013号课程的选课记录。推行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数量如图所示
图片 46
Teacher_course表的数码如图所示
图片 47
Course_selection表的数额如图所示
图片 48
测验结果无误
参照上边包车型地铁数目,继续测验另一种情况,以删除0011号教授卢含笑为例,0011号教授教学0012号课程,在teacher_course表中还会有别的老师教学该科目,因而嵌套触发器会去除teacher_course表中有关0011号助教授课课程记录,但不会删除course_selection表中关于0012号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 49
Teacher表的多少如图所示
图片 50
Teacher_course表的多寡如图所示
图片 51
Course_selection表的数量如图所示
图片 52

        成立触发器

本文由杏彩发布,转载请注明来源

关键词: