澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:触发器入门,二零零六从

2019-05-17 作者:数据库网络   |   浏览(160)

目录

触发器

概念:

读书目录

概念:

  • 1.触发器
    • 1.1.DDL触发器
    • 1.2.DML触发器
    • 一.三.创办触发器
      • 1.3.1.创建DML触发器
      • 1.3.2.创建DDL触发器
      • 1.三.3.嵌套触发器
      • 一.三.四.递归触发器
    • 一.4.管理触发器

        触发器简单介绍:

  触发器(trigger)是SQL server 提需要程序猿和数量剖析员来保险数据完整性的一种办法,它是与表事件相关的奇特的储存进度,它的施行不是由程序调用,也不是手工业运转,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就能激活它执行。触发器平时用来升高数据的完整性约束和事务规则等。 触发器能够从 DBA_TRIGGERS ,USER_TOdysseyIGGEENVISIONS 数据字典中查到。

  • 壹:触发器的独到之处
  • 二:触发器的效果
  • 3:触发器的归类
  • 4:触发器的劳作规律
  • 5:创制触发器
  • 6:管理触发器 

  触发器(trigger)是SQL server 提必要程序猿和数码分析员来保险数据完整性的1种办法,它是与表事件相关的出格的贮存进程,它的奉行不是由程序调用,也不是手工业运维,而是由事件来触发,当对二个表张开操作( insert,delete, update)时就能够激活它实行。触发器平常用来坚实数据的完整性约束和作业规则等。 触发器能够从 DBA_TRIGGERS ,USER_T奥迪Q7IGGERubiconS 数据字典中查到。

1.触发器

触发器是壹种非常的存款和储蓄进程,与表紧凑关系。

          触发器(trigger)是个独特的积存进程,它的推行不是由程序调用,也不是手工业运行,而是由事件来触发,当对多个表实行操作(  insert,delete, update)时就能够激活它实践,触发器日常用来提升数据的完整性约束和作业规则等。以小编之见触发器实际上正是1个风云,就像C#中,点击三个按键会触发相应的操作。

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

概念:

触发器和存款和储蓄进程的差别:

1.1.DDL触发器

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

  • 谨防对数据库框架结构实行改变
  • 希望数据库中生出一些情状以响应数据库架构中的退换
  • 要记录数据库架构中的改换或事件

        触发器的分类:

  触发器与存款和储蓄进程的界别是运作格局的不等,触发器无法施行EXECUTE语句调用,而是在用户实践Transact-SQL语句时自动触发实行而存款和储蓄进程须求用户,应用程序只怕触发器来显示地调用并试行。

  触发器(trigger)是SQL server 提须要技师和多少深入分析员来保险数据完整性的一种艺术,它是与表事件相关的出格的存款和储蓄进程,它的施行不是由程序调用,也不是手工业运维,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就能激活它实践。触发器平常用来加强数据的完整性约束和事务规则等。 触发器能够从 DBA_TRIGGERS ,USER_T路虎极光IGGE奇骏S 数据字典中查到。

  触发器与仓库储存进度的界别是运作方式的分化,触发器不可能试行EXECUTE语句调用,而是在用户推行Transact-SQL语句时自动触发施行而存款和储蓄进程需求用户,应用程序或许触发器来展现地调用并实施。

1.2.DML触发器

当数据库服务器中发出多少操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务看待,要是检查实验到不当,则整个职业回滚。DML触发器在弹指间下面十二分实惠:

  • 可达成数据库相关表之间的级联更换
  • 能够免卫恶意或不当的DML讲话事件,并强制实行比CHECK自律越来越复杂的其余限制
  • 可以评估数据修改前后表的意况,并依靠该差距选择措施

三个表中的四个同类DML触发器,允许用多少个不等的操作来响应同三个修改语句
SQL Server 2008为各类触发器成立了3个非常的表:INSERTED表和DELETED表。那是五个逻辑表,由系统来制造和护卫,用户无法对她们开始展览修改。它们存放在内部存款和储蓄器中,而不是在数据库中,并且协会与被DML触发器成效的表的布局同样。
INSERTED表中存放了由施行INSERTUPDATE语句而插入的持有行,在实行INSERTUPDATE言辞时,新的就要同时被插入到触发器成效的表和INSERTED表中。INSERTED表中的行是触发器成效的表中央银行的别本。
DELETED表中存放了由试行DELETEUPDATE语句而除去的保有行,在实行DELETEUPDATE话语时,被剔除的将在由触发器功效的表中被移位到DELETED表,八个表中不会有重复行。

        (壹)DML( 数据垄断语言 Data Manipulation Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中期维修改数据的insert、update、delete语句。         (二)DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发出(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。         (3)登入触发器:是指当用户登入SQL SE昂科拉VELacrosse实例组建会话时接触。

一:触发器的独到之处

 壹.触发器是半自动的。当对表中的数目做了其余修改未来随即被激活。

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

 三.触发器能够强制限制。那些限制比用CHECK约束所定义的更复杂。与CHECK约束不相同的是,触发器能够引用其余表中的列。

触发器和存款和储蓄进度的分别:

一:触发器的独到之处

 一.触发器是全自动的。当对表中的数据做了别的改造之后立时被激活。

 二.触发器能够由此数据库中的相关表实行层叠修改。

 三.触发器能够强制限制。这个限制比用CHECK约束所定义的更纷繁芜杂。与CHECK约束区别的是,触发器能够引用别的表中的列。

一.三.开立触发器

        在那之中DML触发器最为常用,依照DML触发器触发的点子各异又分为以下二种意况:

二:触发器的功能

 触发器的关键功效就是其能够落成由主键和外键所不能够确定保障的纷纷参照完整性和数码的壹致性,它亦可对数据库中的相关表实行级联修改,升高比CHECK约束更目不暇接的的数据完整性,并自定义错误音讯。触发器的根本成效珍视有以下接个地点:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中全部相关的表,自动触发其余与之有关的操作
  3. 盯住变化,撤除或回滚非法操作,制止不法修改数据
  4. 回来自定义的失实音讯,约束无法回到音讯,而触发器能够
  5. 触发器能够调用越多的储存进度

  触发器与仓库储存进度的分别是运作格局的不等,触发器不能够施行EXECUTE语句调用,而是在用户实行Transact-SQL语句时自动触发执行而存储进度须求用户,应用程序也许触发器来展现地调用并试行。

贰:触发器的法力

 触发器的要害成效正是其能够落到实处由主键和外键所无法担保的纷纷参照完整性和数量的1致性,它亦可对数据库中的相关表进行级联修改,升高比CHECK约束更复杂的的数据完整性,并自定义错误音信。触发器的重中之重意义珍视有以下接个地点:

  1. 强制数据库间的引用完整性
  2. 级联修改数据库中享有相关的表,自动触发此外与之有关的操作
  3. 盯住变化,撤消或回滚不合法操作,幸免违规修改数据
  4. 回到自定义的荒唐音信,约束不能够回来音讯,而触发器能够
  5. 触发器能够调用愈来愈多的积累进程

1.3.1.创建DML触发器

      (1)AFTELX570触发器:它是在实践INSERT、UPDATE、DELETE语句操作之后试行触发器操作。它根本是用来记录改造后的管理或检查,一旦产生错误,能够用Rollback Transaction语句来回滚此番扣件,可是无法对视图定义AFTE奥迪Q7触发器。       (二)INSTEAD OF触发器:它在实施INSERT、UPDATE、DELETE语句操作以前实践触发器自己所定义的操作。而INSTEAD OF触发器是可以定义在视图上的。

叁:触发器的分类

 SqlServer包含三种不荒谬项目的触发器:DML触发器、DDL触发器和登陆触发器。

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

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

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

当蒙受下列意况时,应考虑动用DML触发器:

  1. 经过数据库中的相关表达成级联改动
  2. 防止恶意依旧失实的insert、update和delete操作,并强制实施check约束定义的限量越来越复杂的任何限制。
  3. 评估数据修改前后表的情事,并基于该距离才去措施。

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

 DDL触发器是当服务器恐怕数据库中产生多少定义语言(首假设以create,drop,alter早先的讲话)事件时被激活使用,使用DDL触发器可避防范对数据框架结构实行的一些改换或记录数据中的改变或事件操作。

三.登入触发器

    登入触发器将为响应 LOGIN 事件而振作存款和储蓄进程。与 SQL Server 实例创立用户会话时将引发此事件。登入触发器就要登入的身份验证阶段达成以往且用户会话实际构建在此以前激发。因而,来自触发器内部且一般将达到用户的有着音信(比方错误消息和来自 PCR-VINT 语句的音讯)会传递到 SQL Server 错误日志。如若身份验证失利,将不激情登入触发器。

回去顶上部分

三:触发器的归类

 SqlServer包涵三种健康项指标触发器:DML触发器、DDL触发器和登陆触发器。

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

 DML触发器是局地增大在特定表或视图上的操作代码,当数据库服务器中发生多少操作语言事件时进行那一个操作。SqlServer中的DML触发器有二种:

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

当蒙受下列情形时,应思量选用DML触发器:

  1. 通过数据库中的相关表完结级联退换
  2. 防御恶意如故失实的insert、update和delete操作,并强制施行check约束定义的限制越来越复杂的任何限制。
  3. 评估数据修改前后表的情状,并基于该出入才去措施。

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

 DDL触发器是当服务器只怕数据库中生出多少定义语言(重若是以create,drop,alter初始的语句)事件时被激活使用,使用DDL触发器能够幸免对数据架构举行的少数改动或记录数据中的改变或事件操作。

叁.登入触发器

    登陆触发器将为响应 LOGIN 事件而激情存款和储蓄进度。与 SQL Server 实例创设用户会话时将掀起此事件。登入触发器将要登陆的身份验证阶段完毕以往且用户会话实际建设构造在此以前激发。因而,来自触发器内部且经常将到达用户的享有音讯(比如错误新闻和来自 PKugaINT 语句的音信)会传递到 SQL Server 错误日志。假如身份验证战败,将不激情登6触发器。

澳门新萄京官方网站:触发器入门,二零零六从入门到明白。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表中有着学员都已分班
为了以往有利于,可以将游标示例二中的代码稍作修改封装成二个用户自定义存款和储蓄进度
积攒进程示例叁
修改后的代码如下

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

注:和游标示例二的代码相比较,示例3的代码增多了将全数学员分班状态标志为0的经过,去掉了丰盛stu_division_state列的历程,但对原本已有些学员的分班状态赋值那几个手续未有删去,而是举办重新校验。并且删除了两段代码中的GO和第二段用于给学员分班的代码中对@stu_no变量的再一次评释。

student表插入数据并运营student_division的贮存进度

注:对student表插入数据前应先禁止使用示例壹的触发器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

4:触发器的工作规律

触发器触发时:

  1. 系统自动在内部存款和储蓄器中创设deleted表或inserted表;
  2. 只读,差别意修改,触发器试行到位后,自动删除。

inserted表:

  1. 暂且保存了插入或更新后的记录行;
  2. 能够从inserted表中检查插入的数码是或不是满意工作必要;
  3. 假若不满意,则向用户发送报告错误音讯,并回滚插入操作。 

deleted表:

  1. 有的时候保存了去除或更新前的记录行;
  2. 能够从deleted表中检查被删除的数据是或不是餍足职业供给;
  3. 假若不满意,则向用户告知错误消息,并回滚插入操作。

inserted表和deleted表对照: 

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

 

 

 

澳门新萄京官方网站 8

一:触发器的帮助和益处

 一.触发器是自动的。当对表中的数目做了别样修改今后立即被激活。

 贰.触发器能够由此数据库中的相关表进行层叠修改。

 三.触发器能够强制限制。那一个限制比用CHECK约束所定义的更复杂。与CHECK约束区别的是,触发器能够引用其他表中的列。

再次回到最上端

肆:触发器的办事原理

触发器触发时:

  1. 系统自动在内部存款和储蓄器中开创deleted表或inserted表;
  2. 只读,不允许修改,触发器实行到位后,自动删除。

inserted表:

  1. 一时保存了插入或更新后的记录行;
  2. 能够从inserted表中检查插入的数据是还是不是满意专业必要;
  3. 假使不满意,则向用户发送报告错误音讯,并回滚插入操作。 

deleted表:

  1. 不常保存了除去或更新前的记录行;
  2. 能够从deleted表中反省被去除的数额是还是不是满足专门的工作供给;
  3. 借使不知足,则向用户告知错误音信,并回滚插入操作。

inserted表和deleted表对照: 

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

 

 

 

澳门新萄京官方网站 9

1.3.1.2.DELETE触发器

当针对对象数据库运营DELETE讲话时就能够激活DELETE触发器。用户直接运维DELETE言语和选用DELETE触发器又有所差别,当激活DELETE触发器后,从受触发器影响的表中删除的行会被放置在3个非常的临时表——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表的数量如图所示
澳门新萄京官方网站 10
Class_student表的数额如图所示
澳门新萄京官方网站 11
施行下列语句

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

student表来看,只有入学编号为2018001120180012的上学的儿童战绩被去除。该操作激活了delete_student触发器
Class_student表的数额如图所示
澳门新萄京官方网站 12
入学编号为2018001120180012的学生疏班新闻已经从class_student表中自动删除。

        在SQL SE普拉多VE中华V二零一零中,DML触发器的完毕接纳八个逻辑表DELETED和INSERTED。那四个表是起家在数据库服务器的内部存款和储蓄器中,大家只有只读的权力。DELETED和INSERED表的结商谈触发器所在的数据表的协会是同一的。当触发器实践到位后,它们也就能被自动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的笔录。比如你插入一条数据,那么就能把那条记下插入到INSERTED表:DELETED表用于存放你在操作  insert、update、delete语句前,你创设触发器表中数据库。比如您本来的表中有三条数据,那么他也可能有三条数据。也正是说,我们得以应用那八个有时的驻留内部存款和储蓄器的表,测试有个别数据修改的职能及安装触发器操作的规格。

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;

推行上边的话语后,结果如下图所示:

澳门新萄京官方网站 13

 既然定义了学生总量表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);

结果如下:

澳门新萄京官方网站 14

 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;

结果如下:

澳门新萄京官方网站 15

 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;

澳门新萄京官方网站 16

 四.开立取代触发器

  与后边介绍的几种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

澳门新萄京官方网站 17

伍.嵌套触发器介绍

 假使3个触发器在实施操作时调用了另外1个触发器,而那几个触发器又随着调用了下八个触发器,那么就造成了嵌套触发器。嵌套触发器在设置时就被启用,不过足以选取系统存款和储蓄进度sp_configure禁止使用和重新启用嵌套触发器。

 

  嵌套触发器不自然要产生五个环,它能够 T一->T贰->T叁...那样平昔触发下去,最多允许嵌套 32 层。要是嵌套的次数超越限制,那么该触发器将被终止,并回滚整个工作,使用嵌套触发器供给留意以下几点:

  • 暗中同意情状下,嵌套触发器配置选项是展开的。
  • 在同2个触发器事务中,1个嵌套触发器不可能被触发两遍。
  • 是因为触发器是多少个业务,固然在壹多种嵌套触发器的随机档案的次序中发出错误,则整个事物都将吊销,而且具备数据回滚。

嵌套是用来保持总体数据库的完整性的第捌分一效,但神迹可能要求禁用嵌套,假若禁止使用了嵌套,那么修改三个触发器的兑现不会再接触该表上的其他触发器。在下述情况下,供给禁止使用嵌套触发器:

  • 嵌套触发供给复杂而有理论的统一打算,级联修改恐怕会修改用户不想涉及的多寡。
  • 在一多元嵌套触发器中的肆意点的时间修改操作都会触发一些触发器,固然那时数据库提供很强的维护作用,但万一以特定的11更新表,就能够发生难点。

运用下列语句禁止使用嵌套和重新启用嵌套:

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

陆.递归触发器

  触发器的递归是指二个触发器从其内部再一遍激活该触发器,举例update操作激活的触发器内部还有一条数据表的换代语句,那么那么些创新语句就有相当大可能率激活那个触发器自己,当然,这种递归的触发器内部还会有咬定语句,唯有自然情形下才会进行那几个T_SQL语句,不然就改为有线调用的死循环了。

SqlServer中的递归触发器包涵三种:直接递归和直接递归。

  • 一向递归:触发器被触发后并施行二个操作,而该操作又利用多个触发器再度被触发。
  • 直接递归:触发器被触发并实践三个操作,而该操作又使另七个表中的有个别触发器被触发,第2个触发器使原始表得到更新,从而再度接触第二个触发器。

暗许情况下,递归触发器选项是剥夺的。递归触发器最多只好递归1六层,纵然递归中的第25个触发器激活了第一7个触发器,则结果与发布的rollback命令同样,全体数据都将回滚。 

我们比方解释如下,假设有表一、表二称呼分别为 T一、T二,在 T1、T二 上个别有触发器 G一、G二。

  • 直接递归:对 T一 操作从而触发 G一,G一 对 T贰 操作从而触发 G二,G2 对 T一 操作从而再一次触发 G一...
  • 直白递归:对 T壹 操作从而触发 G一,G一 对 T1 操作从而再度触发 G一... 

安装直接递归:

暗许情状下是不准直接递归的,要设置为允许有二种艺术:

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

贰:触发器的机能

 触发器的重大功效就是其可以达成由主键和外键所不能够确认保证的繁杂参照完整性和多少的①致性,它亦可对数据库中的相关表进行级联修改,提升比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;

实行上边的口舌后,结果如下图所示:

澳门新萄京官方网站 18

 既然定义了学生总量表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);

结果如下:

澳门新萄京官方网站 19

 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;

结果如下:

澳门新萄京官方网站 20

 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;

澳门新萄京官方网站 21

 四.成立取代触发器

  与日前介绍的三种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

澳门新萄京官方网站 22

伍.嵌套触发器介绍

 就算二个触发器在实施操作时调用了别的一个触发器,而以此触发器又随着调用了下四个触发器,那么就形成了嵌套触发器。嵌套触发器在安装时就被启用,可是足以动用系统存款和储蓄进程sp_configure禁止使用和另行启用嵌套触发器。

 

  嵌套触发器不显明要变成贰个环,它可以 T1->T二->T三...那样直白触发下去,最多允许嵌套 32 层。假诺嵌套的次数超越限定,那么该触发器将被结束,并回滚整个专门的职业,使用嵌套触发器要求注意以下几点:

  • 暗中同意景况下,嵌套触发器配置选项是敞开的。
  • 在同叁个触发器事务中,多个嵌套触发器无法被触发四回。
  • 出于触发器是二个职业,若是在1雨后冬笋嵌套触发器的妄动档案的次序中爆发错误,则整个事物都将收回,而且装有数据回滚。

嵌套是用来保证总体数据库的完整性的显要意义,但有的时候恐怕必要禁止使用嵌套,要是禁止使用了嵌套,那么修改二个触发器的落到实处不会再接触该表上的别的触发器。在下述情状下,须要禁止使用嵌套触发器:

  • 嵌套触发供给复杂而有理论的统一盘算,级联修改或许会修改用户不想涉及的多寡。
  • 在1多重嵌套触发器中的大4点的时辰修改操作都会触发一些触发器,就算那时数据库提供很强的保安效率,但壹旦以特定的相继更新表,就能够发生难题。

应用下列语句禁止使用嵌套和重复启用嵌套:

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

六.递归触发器

  触发器的递归是指2个触发器从其内部再一次激活该触发器,比如update操作激活的触发器内部还有一条数据表的更新语句,那么那一个创新语句就有比非常大或者激活这几个触发器自个儿,当然,这种递归的触发器内部还会有咬定语句,只有断定意况下才会推行那一个T_SQL语句,不然就产生无线调用的死循环了。

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

  • 一向递归:触发器被触发后并奉行3个操作,而该操作又利用三个触发器再一次被触发。
  • 直接递归:触发器被触发并进行2个操作,而该操作又使另二个表中的某部触发器被触发,第一个触发器使原始表获得更新,从而再一次接触第三个触发器。

默许情形下,递归触发器选项是剥夺的。递归触发器最七只可以递归1陆层,就算递归中的第3陆个触发器激活了第三8个触发器,则结果与发表的rollback命令同样,全体数据都将回滚。 

笔者们举例表明如下,纵然有表一、表贰称号分别为 T一、T二,在 T一、T贰 上各自有触发器 G1、G贰。

  • 直接递归:对 T一 操作从而触发 G1,G一 对 T二 操作从而触发 G二,G2 对 T1 操作从而再一次触发 G壹...
  • 一贯递归:对 T一 操作从而触发 G一,G1 对 T一 操作从而再一次触发 G一... 

安装直接递归:

默许景况下是禁止直接递归的,要设置为允许有三种情势:

  • 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表的数据如图所示
澳门新萄京官方网站 23
Class_student表的数码如图所示
澳门新萄京官方网站 24
示例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表的数量如图所示
澳门新萄京官方网站 25

注:update_stu_no_single触发器只好对单行记录的UPDATE操作起效,如果批量UPDATE stu_no,实行语句时会提示子查询再次来到的值持续一个。上边包车型地铁示例6将提供批量UPDATE stu_no的触发器

示例6:实现当student表的stu_no字段批量更新时,class_student表的stu_no也一块儿批量翻新
首先将student表和class_student表的数据修改成原来的金科玉律,并且删除update_stu_no_single触发器
Student表的多少如图所示
澳门新萄京官方网站 26
Class_student表的数量如图所示
澳门新萄京官方网站 27
实施下列语句新建触发器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表的数量如图所示
澳门新萄京官方网站 28
Class_student表的数码如图所示
澳门新萄京官方网站 29
大家再来验证update_stu_no_batch触发器对峙异单行stu_no多少是不是行得通。将student表class_student表的数目改回原来的范例,然后实践下列语句

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

Class_student表的数据如图所示
澳门新萄京官方网站 30

注:在将表数据改成原来的标准时,直接在编写前200行中操作照旧用T-SQL话语操作,对student表数据操作,不成功的话要思索受键和自律的影响,对class_student表数据操作,不成功的话要考虑受触发器影响。

        触发器的利害:

6:管理触发器 

一.翻看触发器

(一).查看数据库中持有的触发器

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

sysobjects 保存着数据库的对象,在那之中 xtype 为 TLAND 的笔录即为触发器对象。在 name 1列,大家得以见到触发器名称。

(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

二.禁用启用触发器

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

  要是有八个触发器,则相继触发器名称之间用英文逗号隔开分离。

  若是把“触发器名称”换到“ALL”,则代表禁止使用或启用该表的整个触发器。

三更动触发器

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

四.刨除触发器

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

三:触发器的分类

 SqlServer包括两种健康项目标触发器:DML触发器、DDL触发器和登入触发器。

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

 DML触发器是部分叠合在特定表或视图上的操作代码,当数据库服务器中产生多少操作语言事件时实施那几个操作。SqlServer中的DML触发器有三种:

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

当蒙受下列境况时,应思量选拔DML触发器:

  1. 透过数据库中的相关表达成级联改动
  2. 幸免恶意照旧不当的insert、update和delete操作,并强制试行check约束定义的限定越来越复杂的其他限制。
  3. 评估数据修改前后表的情状,并基于该距离才去措施。

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

 DDL触发器是当服务器也许数据库中发出多少定义语言(主固然以create,drop,alter开始的语句)事件时被激活使用,使用DDL触发器可避防御对数码架构实行的有些改变或记录数据中的更动或事件操作。

三.登陆触发器

    登入触发器将为响应 LOGIN 事件而振奋存款和储蓄进度。与 SQL Server 实例创立用户会话时将抓住此事件。登入触发器将在签到的身份验证阶段完毕今后且用户会话实际构建此前激发。因而,来自触发器内部且一般将到达用户的持有新闻(举例错误音信和来源 P宝马X5INT 语句的新闻)会传递到 SQL Server 错误日志。倘若身份验证失利,将不激情登六触发器。

再次回到最上端

六:管理触发器 

一.翻看触发器

(壹).查看数据库中颇具的触发器

--查看数据库中所有的触发器
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;

四.删减触发器

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

1.3.1.4.INSTEAD OF触发器

INSTEAD OF触发器能够钦赐实施触发器,而不是举行触发SQL话语,从而屏蔽原来的SQL话语,而转向试行触发器内部的讲话。每一个表或许视图只好有3个INSTEAD OF触发器。INSTEAD OF触发器的特点是,能够使作为触发条件的SQL语句不实行。
Membership表的数码如图所示
澳门新萄京官方网站 31
Call_slip表的多寡如图所示
澳门新萄京官方网站 32
示例7:对LibraryManagement数据Curry的membership表写3个防删除触发器,尚有借书未还的读者不大概被剔除
奉行下列语句创设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'

结果如图所示
澳门新萄京官方网站 33
该触发器只针对DELETE一条数占领效
示例8:对LibraryManagement数据Curry的membership表写1个防批量删除触发器,尚有借书未还的读者不能被去除
Membership表的数据如图所示
澳门新萄京官方网站 34
Call_slip表的数码如图所示
澳门新萄京官方网站 35
实践下列语句新建触发器(将示例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

结果如图所示
澳门新萄京官方网站 36
Membership表的数目如图所示
澳门新萄京官方网站 37
示例9:对LibraryManagement数据Curry的call_slip表写二个防超借触发器,三个读者的未还图书最多只好有5本,超出无法再借(这里依然针对批量甩卖数据创设触发器)
Call_slip表的数额如图所示
澳门新萄京官方网站 38
实践下列语句创造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','未归还')

结果如图所示
澳门新萄京官方网站 39
Call_slip表的多少如图所示,红框里是新插入的多寡
澳门新萄京官方网站 40

        触发器可因而数据库中的相关表完毕级联改动,能够强制比用CHECK约束定义的牢笼越来越复杂的封锁。与 CHECK 约束分歧,触发器能够引用别的表中的列,举例触发器能够行使另1个表中的 SELECT 相比插入或更新的数额,以及实行其余操作。触发器也得以依靠数据修改前后的表状态,再行接纳对策。一个表中的多少个同类触发器(INSERT、UPDATE 或 DELETE)允许利用八个分歧的机关以响应同2个修改语句。

肆:触发器的专门的学问规律

触发器触发时:

  1. 系统活动在内存中创建deleted表或inserted表;
  2. 只读,不容许修改,触发器实践到位后,自动删除。

inserted表:

  1. 临时保存了插入或更新后的记录行;
  2. 能够从inserted表中检查插入的数量是或不是满意职业要求;
  3. 即使不满足,则向用户发送报告错误音信,并回滚插入操作。 

deleted表:

  1. 有的时候保存了去除或更新前的记录行;
  2. 能够从deleted表中反省被删除的多少是还是不是满意职业必要;
  3. 若是不满意,则向用户告知错误消息,并回滚插入操作。

inserted表和deleted表对照: 

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

 

 

 

澳门新萄京官方网站 41

回去顶上部分

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)
)

结果如图所示
澳门新萄京官方网站 42
澳门新萄京官方网站 43

注:EVENTDATA()可在触发器内部使用,再次来到有关数据库和服务器事件的音讯,以XML格式再次回到。唯有一向在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会回去数据。即使EVENTDATA由别的例程调用(就算那几个例程由DDL或登入触发器举行调用),将回到 NULL

        与此同时,尽管触发器效能庞大,轻易可信地达成广大错综相连的作用,为啥又要慎用?过多触发器会促成数据库及应用程序的保卫安全困难,同时对触发器过分的依赖,势必影响数据库的构造,同时扩展了保险的繁杂程序。

5:创设触发器

 创设触发器的语法: 

澳门新萄京官方网站 44;)

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指定触发器的类型

澳门新萄京官方网站 45;)

 准备测试数据:

澳门新萄京官方网站 46;)

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

澳门新萄京官方网站 47;)

1.创建insert触发器

澳门新萄京官方网站 48;)

--创建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

澳门新萄京官方网站 49;)

澳门新萄京官方网站 50;)

--测试触发器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;

澳门新萄京官方网站 51;)

实践上面的语句后,结果如下图所示:

澳门新萄京官方网站 52

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

澳门新萄京官方网站 53;)

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

澳门新萄京官方网站 54;)

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

结果如下:

澳门新萄京官方网站 55

 2.创建delete触发器

  用户实行delete操作,就能够激活delete触发器,从而调控用户能够从数据库中删除数据记录,触发delete触发器后,用户删除的记录会被增添到deleted表中,原来表的应和记录被删去,所以在deleted表中查阅删除的笔录。

澳门新萄京官方网站 56;)

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

澳门新萄京官方网站 57;)

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

结果如下:

澳门新萄京官方网站 58

 3.创建UPDATE触发器

  update触发器是当用户在钦赐表上施行update语句时被调用被调用,那连串型的触发器用来约束用户对数码的改造。update触发器能够推行二种操作:更新前的笔录存款和储蓄在deleted表中,更新后的记录存款和储蓄在inserted表中。

澳门新萄京官方网站 59;)

--创建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

澳门新萄京官方网站 60;)

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

澳门新萄京官方网站 61

 四.创造替代触发器

  与后面介绍的二种after触发器不一致,SqlServer服务器在实践after触发器的sql代码后,先创制有时的inserted表和deleted表,然后施行代码中对数据库操作,最终才激活触发器中的代码。而对此代表(instead of**)触发器,SqlServer服务器在举行触发instead of 触发器的代码时,先成立一时半刻的inserted表和deleted表,然后径直触发instead of触发器,而拒绝实行用户输入的DML操作语句。**

澳门新萄京官方网站 62;)

--创建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

澳门新萄京官方网站 63;)

创造达成,实施一条insert语句触发触发器trig_insteadOf

澳门新萄京官方网站 64

5.嵌套触发器介绍

 借使三个触发器在施行操作时调用了其余叁个触发器,而这么些触发器又进而调用了下四个触发器,那么就形成了嵌套触发器。嵌套触发器在装置时就被启用,不过足以应用系统存款和储蓄进度sp_configure禁止使用和重复启用嵌套触发器。

 

  嵌套触发器不必然要形成二个环,它能够 T一->T2->T三...那样直接触发下去,最多允许嵌套 32 层。假设嵌套的次数超过限定,那么该触发器将被截止,并回滚整个业务,使用嵌套触发器要求小心以下几点:

  • 暗中认可景况下,嵌套触发器配置选项是展开的。
  • 在同二个触发器事务中,多个嵌套触发器不可能被触发五回。
  • 是因为触发器是1个业务,尽管在壹层层嵌套触发器的随机档案的次序中发出错误,则整个事物都将撤消,而且全体数据回滚。

嵌套是用来保持总体数据库的完整性的关键成效,但神蹟也许须求禁止使用嵌套,纵然禁止使用了嵌套,那么修改2个触发器的兑现不会再接触该表上的别的触发器。在下述情形下,需求禁止使用嵌套触发器:

  • 嵌套触发须求复杂而有理论的宏图,级联修改或许会修改用户不想涉及的数量。
  • 在壹密密麻麻嵌套触发器中的自便点的小时修改操作都会接触一些触发器,固然那时数据库提供很强的保障作用,但一旦以一定的逐条更新表,就能够产生难题。

行使下列语句禁止使用嵌套和重复启用嵌套:

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

6.递归触发器

  触发器的递归是指叁个触发器从其内部再1遍激活该触发器,比如update操作激活的触发器内部还有一条数据表的更新语句,那么那些立异语句就有希望激活这么些触发器自个儿,当然,这种递归的触发器内部还会有咬定语句,唯有自然情形下才会试行这一个T_SQL语句,不然就成为有线调用的死循环了。

SqlServer中的递归触发器包含二种:直接递归和直接递归。

  • 平素递归:触发器被触发后并试行一个操作,而该操作又利用1个触发器再次被触发。
  • 直接递归:触发器被触发并实践3个操作,而该操作又使另三个表中的某部触发器被触发,第1个触发器使原始表获得更新,从而再度接触第二个触发器。

暗中同意情形下,递归触发器选项是剥夺的。递归触发器最多只好递归1陆层,假若递归中的第贰多少个触发器激活了第3八个触发器,则结果与公布的rollback命令同样,全体数据都将回滚。 

大家比方解释如下,假若有表壹、表二名称分别为 T一、T贰,在 T一、T二 上个别有触发器 G壹、G2。

  • 直接递归:对 T壹 操作从而触发 G一,G一 对 T2 操作从而触发 G二,G二 对 T一 操作从而再度触发 G一...
  • 直白递归:对 T一 操作从而触发 G一,G一 对 T1 操作从而再度触发 G1... 

设置直接递归:

暗许情状下是不准直接递归的,要设置为允许有三种方法:

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

回来顶端

一.三.叁.嵌套触发器

语法

6:管理触发器 

1.查看触发器

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

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

sysobjects 保存着数据库的靶子,其中 xtype 为 T安德拉 的笔录即为触发器对象。在 name 1列,咱们能够看出触发器名称。

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

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

 将会以表的样式展现触发器内容。 

 除了触发器外,sp_helptext 还足以显得 规则、暗中认可值、未加密的存款和储蓄进度、用户定义函数、视图的文件。

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

  sp_helptrigger 有七个参数:第叁个参数为表名;第二个为触发器类型,为 char(陆) 类型,能够是 INSERT、UPDATE、DELETE,假诺轻松则显示钦赐表中全体类型触发器的天性。

use 数据库名
go
exec sp_helptrigger tableName

二.禁止使用启用触发器

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

  要是有四个触发器,则相继触发器名称之间用英文逗号隔绝。

  要是把“触发器名称”换来“ALL”,则象征禁止使用或启用该表的万事触发器。

叁修改触发器

澳门新萄京官方网站 65;)

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

澳门新萄京官方网站 66;)

四.删减触发器

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

原来的著作转自:

一.三.叁.壹.嵌套触发器

1经三个触发器在试行操作时引发了另2个触发器,而以此触发器又掀起了下2个触发器,那么这个触发器正是嵌套触发器。嵌套触发器在装置时就被启用,但是足以行使sp_configure储存进程禁止使用和重复启用嵌套。
DML触发器和DDL触发器最多能够嵌套32层,能够经过nested triggers来铺排是或不是足以嵌套AFTER触发器,但是不论此设置什么样都得以嵌套INSTEAD OF触发器。假使嵌套触发器进入了Infiniti循环,该触发器将被终止,并且回滚整个业务。嵌套触发器械备两种用场,举例保留前二个触发器所影响的行的别本。
应用嵌套触发器时应该注意以下几点:

  • 暗许意况下,嵌套触发器配置选项开启。
  • 在同1个触发器事务中,三个触发器不会被触发四遍,触发器不会调用他本人来响应触发器中对同四个表的第1次立异
  • 出于触发器是3个事务,1旦嵌套中别的1层的触发器出现谬误,将回滚整个事情。

示例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表中的数据如图所示
澳门新萄京官方网站 67
Teacher_course表中的数据如图所示
澳门新萄京官方网站 68
Course_selection**表中的数据如图所示
澳门新萄京官方网站 69
以课程00一3为例,施行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数额如图所示
澳门新萄京官方网站 70
Teacher_course表的数据如图所示
澳门新萄京官方网站 71
Course_selection表的数码如图所示
澳门新萄京官方网站 72
享有关于00一三科指标数据都被去除。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,笔者额外插手了四个判别,当teacher_course表中还有老师在讲课那门课程时,全数关于那门学科的上学的小孩子选课信息都不以为然删除。那样做在嵌套触发器里是多余的,删除壹门科目,必然会删除teacher_course表中颇具与那门科目有关的笔录,也明确删除course_selection表中享有与这门课程有关的记录,然则,那样做可以确认保证该触发器能够单独于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能够用来别的嵌套触发器中,看示例12

示例12:有teacher表(教授消息表),teacher_course(教授所教学程表),和course_selection表(学生选课记录表),写二个嵌套触发器,完成当3个民间兴办助教离职时,在剔除该教授所教课程音讯,若是未有老师教那门课程,再删除该学科选课记录。
其中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表的数额如图所示
澳门新萄京官方网站 73
Teacher_course表的数据如图所示
澳门新萄京官方网站 74
Course_selection表的数码如图所示
澳门新萄京官方网站 75
以删除001二号助教路易为例,001二号助教授课00一三号课程,且teacher_course表中并无任何导师教学00一3号课程,遵照逻辑要刨除teacher_course表中001二号教师的所教课程记录和course_selection表中有所001三号课程的选课记录。奉行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数据如图所示
澳门新萄京官方网站 76
Teacher_course表的数码如图所示
澳门新萄京官方网站 77
Course_selection表的多少如图所示
澳门新萄京官方网站 78
测试结果正确
参照上边的数据,继续测试另1种情状,以删除001一号教师卢含笑为例,001一号教授教学001二号课程,在teacher_course表中还有任何老师讲课该科目,由此嵌套触发器会去除teacher_course表中有关0011号教授教学课程记录,但不会去除course_selection表中有关001二号课程的选课记录。实施下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
澳门新萄京官方网站 79
Teacher表的多寡如图所示
澳门新萄京官方网站 80
Teacher_course表的数目如图所示
澳门新萄京官方网站 81
Course_selection表的多少如图所示
澳门新萄京官方网站 82

        树立触发器

一.三.三.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触发器

结果如图所示
澳门新萄京官方网站 83
实践下列语句

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

结果如图所示
澳门新萄京官方网站 84

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

1.叁.三.叁.禁止使用和启用嵌套触发器

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

        **去除触发器:**

一.3.四.递归触发器

 

一.3.四.壹.递归触发器

触发器被激活,改动了表中多少,这种改动又激活了它和煦,这种触发器被称之为递归触发器。数据库创制时默许递归触发器禁止使用。但足以行使ALTER DATABASE选料来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用景况,假诺嵌套触发器禁止使用,不管递归触发器的配置是怎么都将被剥夺。而在递归触发器中,inserted表和deleted表都只包涵被上二遍触发器影响的行数据。
递归触发器有以下三种分化品种(那边未有适当的行使示范可举,先不譬喻了)

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

一.叁.4.贰.直接递归

直白递归触发器是指任何递归进程唯有它自己1个触发器的涉企。自身激活了和煦。

 

1.3.4.三.直接递归

直接递归触发器是指任何递归进程有五个触发器出席,例如A激活B,B激活C,C激活A。能够看做是递归和嵌套的组成。
利用递归触发器时须求专注以下几点:
递归触发器很复杂,供给经过有系统的宏图和周全测试
在率性点的数额修改都会激活递归触发器。只好按触发器被激活的一定顺序更新表。
怀有触发器一同构成1个大事务,任性触发器的专擅地点上的ROLLBACK言语都将撤除全数数据的输入,全部数据均被擦除。
触发器最四只可以递归1陆层,一旦有第三捌个触发器参与进来,结果与ROLLBACK指令同样,全部数据都将被擦除

          修改触发器:

一.3.四.四.启用递归触发器

能够应用SQL Server 2008的管理器工具来启用递归触发器。
澳门新萄京官方网站 85

 

一.肆.管制触发器

剥夺和启用触发器
试行下列语句禁止使用和启用触发器

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 ] 

 

          开启和剥夺:

 

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

 

          提示和保险:

 

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

 

示例

 

    在S表创建UPDATE触发器:

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

 

    取缔删除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 

 

   取缔将SC表中不比格学生的实际业绩改为合格:

 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

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:触发器入门,二零零六从

关键词: