14第十四章触发器,2008从入门到精通

目录

触发器

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.管制触发器

        触发器简单介绍:

 

1.触发器

触发器是风流罗曼蒂克种新鲜的贮存进程,与表紧凑关系。

 
       

触发器(trigger)是个奇特的存放进程,它的实施不是由程序调用,亦不是手工业运维,而是由事件来触发,当对多个表打开操作( 
insert,delete,
update)时就能激活它执行,触发器常常用来进步数据的完整性限制和专业准则等。以作者之见触发器实际上正是三个平地风波,就好像C#中,点击叁个按键会触发相应的操作。

DML –>
AFTE本田CR-V / FOCR-V   UPDATE , INSERT , DELETE  — 用来级联删除

1.1.DDL触发器

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

  • 以免对数据库布局进行纠正
  • 瞩望数据库中发出一些情状以响应数据库构造中的改革
  • 要记录数据库构造中的更正或事件

        触发器的归类:

       
 –> INSTEAD OF  在 时间此前接触,也就是 bef

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卡塔尔(英语:State of Qatar)DML( 数据垄断(monopoly卡塔尔(英语:State of Qatar)语言 Data Manipulation Language)触发器:是指触发器在数据库中生出DML事件时将启用。DML事件即指在表或视图中期维校勘数据的insert、update、delete语句。
        (2卡塔尔DDL(数据定义语言
Data Definition
Language)触发器:是指当服务器或数据库中发出(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。
        (3卡塔尔(英语:State of Qatar)登入触发器:是指当顾客登陆SQL
SE昂CoraVE奔驰G级实例创建会话时接触。

INSERTED,
DELETED 两张表要卓越利用。

1.3.创立触发器

       
个中DML触发器最为常用,依照DML触发器触发的方式各异又分为以下两种状态:

在开创 DML
触发器时,无法运用下列语句:

1.3.1.创建DML触发器

     
(1)AFTEEnclave触发器:它是在实施INSERT、UPDATE、DELETE语句操作之后实施触发器操作。它根本是用于记录更改后的拍卖或检查,生龙活虎旦发生错误,能够用Rollback
Transaction语句来回滚此番扣件,不过不可能对视图定义AFTEHighlander触发器。      
(2)INSTEAD
OF触发器:它在实践INSERT、UPDATE、DELETE语句操作从前执行触发器本人所定义的操作。而INSTEAD
OF触发器是足以定义在视图上的。

CREATE /
ALTER /DROP DATABASE

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

LOAD DATABASE
/ LOAD LOG / RECONFIGURE

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表的多寡如图所示
图片 8
Class_student表的数量如图所示
图片 9
实践下列语句

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

student表来看,唯有入学编号为2018001120180012的上学的小孩子战绩被删去。该操作激活了delete_student触发器
Class_student表的数码如图所示
图片 10
入学编号为2018001120180012的学生疏班新闻已经从class_student表中自行删除。

        在SQL SE中华VVE大切诺基二零零六中,DML触发器的兑现利用多少个逻辑表DELETED和INSERTED。那七个表是树立在数据库服务器的内部存款和储蓄器中,大家唯有只读的权位。DELETED和INSERED表的结商谈触发器所在的数据表的结构是风流倜傥律的。当触发器实施到位后,它们也就能够被电动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的笔录。譬如您插入一条数据,那么就会把那条记下插入到INSERTED表:DELETED表用于存放你在操作 
insert、update、delete语句前,你创立触发器表中数据库。比方您原本的表中有三条数据,那么她也可能有三条数据。也便是说,大家得以行使那五个有的时候的驻留内部存储器的表,测量检验有些数据改过的机能及安装触发器操作的标准。

RESTORE
DATABASE  / RESTORE LOG

1.3.1.3.UPDATE触发器

当针对对象数据库运转UPDATE言辞时就能激活UPDATE触发器。对UPDATE触发器来讲,不常表INSERTEDDELETED照例有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以至被更新的表,来分明是还是不是更新了多行和怎么进行触发器动作。
Student表的数额如图所示
图片 11
Class_student表的多寡如图所示
图片 12
示例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表的数量如图所示
图片 13

注: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表的多寡如图所示
图片 14
Class_student表的数量如图所示
图片 15
实行下列语句新建触发器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表的数码如图所示
图片 16
Class_student表的数额如图所示
图片 17
大家再来验证update_stu_no_batch触发器对矫正单行stu_no数码是或不是有效。将student表class_student表的多少改回原本的楷模,然后实行下列语句

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

Class_student表的数量如图所示
图片 18

注:在将表数据改成原来的样本时,直接在编写制定前200行中操作依然用T-SQL话语操作,对student表数据操作,不成事的话要思虑受键和平左券束的熏陶,对class_student表数据操作,不成事的话要考虑受触发器影响。

        触发器的得失:

机关事务管理形式下,照旧在隐式或突显事务管理情势下,只要在
触发器中发生 BEGIN TRANSACTION
语句,实际上就从头了多个嵌套事务,当触发器中动用 ROLLBACK TRANSACTION
语句回滚嵌套事务时,触发器本人产生的持有的 BEGIN TRANSACTION
语句回滚嵌套事务时,触发器本人发生的额全体 BEGIN TRANSACTION
语句豆乳被忽视, ROLLBACK 将回滚到最外界的 BEGIN TRANSACTION 。而在 那最外界的 此前的 事务都早已交由的就不会吸收接纳影响,

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够钦命推行触发器,并非试行触发SQL言辞,进而屏蔽原本的SQL话语,而转用实施触发器内部的言语。每一个表或然视图只可以有1个INSTEAD
OF
触发器。INSTEAD
OF
触发器的特色是,能够使作为触发条件的SQL语句不实施。
Membership表的数目如图所示
图片 19
Call_slip表的数额如图所示
图片 20
示例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'

结果如图所示
图片 21
该触发器只针对DELETE一条数据有效
示例8:对LibraryManagement数据Curry的membership表写二个防批量删除触发器,尚有借书未还的读者非常小概被去除
Membership表的多少如图所示
图片 22
Call_slip表的数据如图所示
图片 23
执行下列语句新建触发器(将示例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

结果如图所示
图片 24
Membership表的数量如图所示
图片 25
示例9:对LibraryManagement数据Curry的call_slip表写二个防超借触发器,贰个读者的未还图书最四只可以有5本,超出不可能再借(这里照旧针对批量甩卖多少制造触发器)
Call_slip表的数额如图所示
图片 26
实行下列语句创造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','未归还')

结果如图所示
图片 27
Call_slip表的数额如图所示,红框里是新插入的多少
图片 28

     
  触发器可透过数据库中的相关表完成级联改进,能够强制比用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)
)

结果如图所示
图片 29
图片 30

注:EVENTDATA()可在触发器内部选取,重临有关数据库和服务器事件的音信,以XML格式重回。唯有一向在DDL或登入触发器内部援用EVENTDATA时,EVENTDATA才会重临数据。假诺EVENTDATA由别的例程调用(即便那几个例程由DDL或登陆触发器进行调用),将赶回
NULL

       
与此同期,纵然触发器成效强盛,轻易可信赖地完毕广大复杂的效果与利益,为何又要慎用?过多触发器会招致数据库及应用程序的保障困难,同期对触发器过分的依附,势必影响数据库的构造,同期扩充了爱惜的复杂程序。

之所以,若要在 触发器中进行部分回滚,应当选择 SAVE TRANSACTION
语句设置八个作业保存点,那样就不会回滚到 外界的 事务中去了。

1.3.3.嵌套触发器

语法

只顾:  在 触发器中 书写 COMMIT TRANSACTION 的 语句,如若此前有 BEGIN
TRANSACTION 语句,会被以为是

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表中的数据如图所示
图片 31
Teacher_course表中的数据如图所示
图片 32
Course_selection**表中的数据如图所示
图片 33
以课程0013为例,试行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的多少如图所示
图片 34
Teacher_course表的数据如图所示
图片 35
Course_selection表的数量如图所示
图片 36
不非亲非故于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表的数量如图所示
图片 37
Teacher_course表的数码如图所示
图片 38
Course_selection表的数额如图所示
图片 39
以删除0012号教授路易为例,0012号教授执教0013号课程,且teacher_course表中并无其余导师执教0013号课程,依照逻辑要删减teacher_course表中0012号教授的所教课程记录和course_selection表中有着0013号课程的选课记录。施行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的多少如图所示
图片 40
Teacher_course表的数据如图所示
图片 41
Course_selection表的数目如图所示
图片 42
测验结果正确
参照上边包车型地铁数据,继续测量试验另黄金时代种情景,以删除0011号教授卢含笑为例,0011号教师传授0012号课程,在teacher_course表中还会有此外老师传授该科目,因而嵌套触发器会去除teacher_course表中有关0011号助教授课课程记录,但不会删除course_selection表中有关0012号课程的选课记录。实施下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 43
Teacher表的数码如图所示
图片 44
Teacher_course表的多少如图所示
图片 45
Course_selection表的数据如图所示
图片 46

        树立触发器

仅付给该嵌套事务,要是 在 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触发器

结果如图所示
图片 47
履行下列语句

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

结果如图所示
图片 48

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层,生龙活虎旦有第十六个触发器参与进去,结果与ROLLBACK指令雷同,全体数据都将被擦除

          改进触发器:

FILX570ST 和 LAST
触发器之间的施行并不曾前后相继顺序:

1.3.4.4.启用递归触发器

可以动用SQL Server 2008的微型机工具来启用递归触发器。
图片 49

 

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 触发器钦命为 第风流倜傥或 最后 一个触发器

          展开和剥夺:

假若应用了 ALTE奔驰G级 T昂科拉IGGECRUISER 语句 改善了 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 服务器配置选项来空值是不是足以嵌套AFTEEnclave 触发器。
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.

 

瞩目: 只有在设置 RECUHavalSIVE_T牧马人IGGE奥迪Q3S
数据库选项为 ON 的情况下,才允许以递归格局调用AFTE奥迪Q7触发器:

   明确命令禁绝将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)

图片 50

CREATE TRIGGER del_Stu 

ON dbo.Students

FOR DELETE

AS

SELECT * FROM DELETED

DELETE FROM dbo.Students WHERE StudentName = ‘Frank’

图片 51

图片 52

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 触发器,也理应使用 ALTERAV4 T凯雷德IGGETiguan 语句。 比如:

ALTER TRIGGER Safety

ON DATABASE 

FOR CREATE_TABLE

AS

    PRINT N’CREATE TABLE 出错’;

    SELECT 

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

ROLLBACK;

初藳链接

本文由豆约翰博客备份行家远程少年老成键发布

发表评论

电子邮件地址不会被公开。 必填项已用*标注