菜单

《SQL Server 贰零零九从入门到驾驭》–201807一⑦

2019年4月7日 - MySQL

一.4.三.2.调用API函数来设置隐式事务情势

用来安装隐式事务情势的API机制是ODBC和OLE DB(不可能知晓,不多说了)

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够钦命执行触发器,而不是执行触发SQL说话,从而屏蔽原来的SQL话语,而转用执行触发器内部的话语。各类表只怕视图只好有3个INSTEAD
OF
触发器。INSTEAD
OF
触发器的特征是,能够使作为触发条件的SQL语句不履行。
Membership表的多少如图所示
图片 1
Call_slip表的多寡如图所示
图片 2
示例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'

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

结果如图所示
图片 6
Membership表的数据如图所示
图片 7
示例9:对LibraryManagement数据Curry的call_slip表写八个防超借触发器,3个读者的未还图书最七只好有5本,超出不能够再借(那里依然针对批量拍卖数量创立触发器)
Call_slip表的数目如图所示
图片 8
履行下列语句创设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','未归还')

结果如图所示
图片 9
Call_slip表的多寡如图所示,红框里是新插入的数据
图片 10

目录

1.3.1.2.DELETE触发器

当针对对象数据库运转DELETE言辞时就会激活DELETE触发器。用户一向运转DELETE言语和应用DELETE触发器又有所分歧,当激活DELETE触发器后,从受触发器影响的表中删除的行会被停放在三个与众不相同的近来表——DELETED表中。DELETED表还允许引用由开头化DELETE语句发生的日志数据。
DELETE触发器被激活时,须要思虑以下几点

示例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表的数额如图所示
图片 11
Class_student表的多少如图所示
图片 12
实践下列语句

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

student表来看,只有入学编号为2018001120180012的学习者成绩被删去。该操作激活了delete_student触发器
Class_student表的多寡如图所示
图片 13
入学编号为2018001120180012的学生分班音信已经从class_student表中活动删除。

一.5.隔断级别

当四个线程都敞开事务来操作数据库中的数据时,数据库要能实行隔开分离操作,以管教各种线程获取数据的准头。如若未有隔开分离操作,会产出以下三种意况:

诸如:A转100块钱给B,SQL语句如下

UPDATE acount
SET cash=cash+100
WHERE name='B'--此时A通知B
UPDATE acount
SET cash=cash-100
WHERE name='A'

推行完第二条语句时,A通告B,让B确认是不是到账,B确认钱到账(此时暴发了脏读),而后无论第3条SQL语句是或不是推行,只要工作未有交给,全体操作都将回滚,B第二回查看时发现钱并未有到账。

目录

1.2.事情分类

一.三.四.一.递归触发器

触发器被激活,更改了表中数量,那种变更又激活了它和谐,这种触发器被号称递归触发器。数据库创造时私下认可递归触发器禁用。但足以选用ALTER
DATABASE
分选来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用意况,如若嵌套触发器禁止使用,不管递归触发器的安插是怎样都将被剥夺。而在递归触发器中,inserted表和deleted表都只含有被上一遍触发器影响的行数据。
递归触发器有以下三种不相同连串(那边未有适合的利用示范可举,先不举例了)

一.陆.分布式事务

对三个数据库中的数据开始展览改动的政工,是分布式事务。那一个数据库能够是本地数据库,也足以是别的链接服务器上的数据库。
分布式事务由二个分布式事务协调程序(DTC)来决定,若想使用分布式事务,必须先运行该服务。在分布式事务中用COMMIT
TRANSACTION提交业务,数据库会自动调用三个两步提交协议:一.布告各个数据库核实它们能够交给该工作并保存能源。2.当种种相关数据库布告SQL
Server 二零零六得以每日提交该业务后,SQL Server
二〇〇九文告相关数据库提交该事务。如若有一个数据库无法得逞交付该事情,则SQL
Server 2010会通告全部相关数据库回滚该业务。

1.2.DML触发器

当数据库服务器中发生多少操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,假设检查实验到不当,则整个事情回滚。DML触发器在刹那间下面格外实惠:

多个表中的三个同类DML触发器,允许用多个分化的操作来响应同一个改动语句
SQL Server
2008
为种种触发器创立了一个优良的表:INSERTED表和DELETED表。那是四个逻辑表,由系统来创立和护卫,用户不能够对他们举办改动。它们存放在内部存款和储蓄器中,而不是在数据库中,并且组织与被DML触发器效率的表的组织同样。
INSERTED表中存放了由进行INSERTUPDATE语句而插入的兼具行,在实践INSERTUPDATE说话时,新的就要同时被插入到触发器效率的表和INSERTED表中。INSERTED表中的行是触发器作用的表中央银行的副本。
DELETED表中存放了由实施DELETEUPDATE语句而删除的有所行,在执行DELETEUPDATE说话时,被删去的就要由触发器效用的表中被移动到DELETED表,三个表中不会有重复行。

1.1.事务的ACID属性

示例1:境况如下3个代码

--语句1:
UPDATE student
SET stu_birthday='1993-02-01',
stu_native_place='山西',
stu_phone='15729810290'
WHERE stu_no='20180101'
--语句2:
UPDATE student
SET stu_birthday='1993-02-01'
WHERE stu_no='20180101'
UPDATE student
SET stu_native_place='山西'
WHERE stu_no='20180101'
UPDATE student
SET stu_phone='15729810290'
WHERE stu_no='20180101'

在语句第11中学,只有一个作业,对列的更新要不全部成功更新,要不全部更新失败。而语句2中,有多个业务,即使当中有有个别列更新退步,也不会潜移默化其余列的更新。

1.3.1.1.INSERT触发器

示例1:成立2个触发器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表中的数据如图所示
图片 14
游标示例二:对student表中还未分班的学生实行分班
Student表中的数据如图所示
图片 15
其中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表的数据
图片 16
Class_student表的数目
图片 17
至此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
图片 18
履行存储进程

EXEC dbo.student_division

结果如图所示
Student表的多寡(分班状态都为true了)
图片 19
Class_student表的数量
图片 20

一.三.管制业务

要害接纳以下肆条语句管理作业:BEGIN TRANSACTION,COMMIT
TRANSACTION,ROLLBACK TRANSACTION和SAVE
TRANSACTION。其余还有二个全局变量能够用在事务处理语句中:@@E奥迪Q3RO牧马人和@@TRANCOUNT。
BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION不多说了。

1.3.1.创建DML触发器

1.3.1.SAVE TRANSACTION

同意有的地付诸多个作业,同时还是能回退这些事情的剩余部分。
示例3:BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK
TRANSACTION和SAVE TRANSACTION的咬合使用
实施下列语句

BEGIN TRANSACTION changed
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180014','谭晶','男','533')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180014'
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed

上述代码完毕了三个这么的效率:设置贰个政工,事务名changed,该事情的效益是向student表中插入一条记下并创新该记录的stu_sex字段。如果更新失利,则回滚到插入操作,即确定保障不管更新是还是不是中标,插入操作都能不负众望。

一.三.三.3.禁止使用和启用嵌套触发器

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

1.4.3.1.通过SET IMPLICIT_TRANSACTIONS ON语句设置隐式事务形式

显式事务格局方式会在有大量DDL和DML语句执行时自动开首,并平素维系到用户分明提交终止。也便是说,如果设置了隐式事务方式,而SQL语句中又有工作未有明白提交,即采用COMMIT
TRANSACTION语句提交,那么用户断开连接,大概关闭数据库时,系统会询问有未提交的事情,是或不是交付,借使采取否,那么未提交的事体将会被回滚,下次延续时就不存在了。
示例7:执行下列语句

SET IMPLICIT_TRANSACTIONS ON
GO

USE test
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
COMMIT TRANSACTION
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUES
('1003','卢哲','27')
SELECT * FROM T1

结果如图所示
图片 21
下一场断开连接,出现如下提示
图片 22
一经选用否的话,再度连接成功后SELECT T一表,结果如图所示
图片 23
会意识1002和拾0三的笔录都被回滚了,那是因为在插入的时候,那两条语句的作业未有COMMIT,只有首先条插入语句被交给了。那便是隐式事务格局。

一.三.成立触发器

壹.5.1.种种隔开分离级别

工作隔断级别越高,越能保障数据的壹致性和完整性。

一.4.二.显式事务格局

有举世知名使用BEGIN
TRANSACTION语句定义二个工作的正是显式事务情势。示例贰,叁,四,伍都以显式事务形式。

壹.3.4.2.一贯递归

直接递归触发器是指任何递归进度只有它本身1个触发器的参与。本身激活了投机。

壹.2.二.用户自定义的政工

实质上应用中,平常应用用户自定义的事务。自定义的方法是,以BEGIN
TRANSACTION开头,以COMMIT TRANSACTION或ROLLBACK
TRANSACTION停止。那八个语句之间具有语句都被视为一体。
示例2:自定义事务的接纳

BEGIN TRANSACTION
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180013','贾乃亮','1993-01-20','498')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180014','周星星','1993-07-20','532')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180015','雨化田','错误格式数据','570')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180016','周琪','1993-01-20','653')
INSERT INTO student(stu_no,stu_name,stu_birthday,stu_enter_score)
VALUES('20180017','陈璐','1998-01-20','599')
COMMIT TRANSACTION

在上头的事情中,第二条插入数据是张冠李戴数据,不只怕得逞插入,执行上边的口舌,发现拥有插入语句都未曾被实施成功。
再有一种用户自定义事务——分布式事务。倘诺在相比较复杂的条件中,有多台服务器,为了确认保障服务器中数量的完整性和壹致性,就非得定义三个分布式事务。举个例子,有二台服务器,壹台存放仓库储存数量,另1台存放订单数量,用户下单的逻辑是,下单前先扣除仓库储存数量,再下单。假诺未有分布式事务,简单并发扣除仓库储存数据,单下单却没得逞,造成五个数据库数据不一样的动静。

一.3.叁.二.查看触发器嵌套的层数

能够使用@@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触发器

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

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

结果如图所示
图片 25

壹.八.2.停下工作

停止业务恐怕必须运转KILL语句,使用该语句时要小心,越发是在运行重点的进度时。

1.1.DDL触发器

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

一.伍.2.安装工作隔离级别

私下认可情状下,SQL Server 二〇〇八的业务隔离级别为付出读。可由此SET TRANSACTION
ISOLATION LEVEL来设置工作隔开分离级别。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

借使三个触发器在推行操作时引发了另三个触发器,而这些触发器又引发了下3个触发器,那么这几个触发器正是嵌套触发器。嵌套触发器在装置时就被启用,不过能够运用sp_configure仓库储存进程禁止使用和另行启用嵌套。
DML触发器和DDL触发器最多可以嵌套3贰层,能够由此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就形成了3个嵌套触发器,上面来表明嵌套触发器的正确。 Course表中的数据如图所示
图片 26
Teacher_course表中的数据如图所示
图片 27
Course_selection**表中的数据如图所示
图片 28
以课程00一三为例,执行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数额如图所示
图片 29
Teacher_course表的多寡如图所示
图片 30
Course_selection表的数据如图所示
图片 31
具备关于00一三课程的数码都被删去。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,小编额外参预了2个论断,当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表的多少如图所示
图片 32
Teacher_course表的多寡如图所示
图片 33
Course_selection表的数量如图所示
图片 34
以删除001贰号教师路易为例,001二号教师授课00壹三号课程,且teacher_course表中并无别的老师讲课00一三号课程,依据逻辑要刨除teacher_course表中001二号教授的所教课程记录和course_selection表中颇具00一叁号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数据如图所示
图片 35
Teacher_course表的数目如图所示
图片 36
Course_selection表的数额如图所示
图片 37
测试结果正确
参考上边的数目,继续测试另一种处境,以删除001壹号助教卢含笑为例,001一号教师授课001二号课程,在teacher_course表中还有其余老师授课该科目,因而嵌套触发器会去除teacher_course表中有关001一号助教讲课课程记录,但不会删除course_selection表中有关001二号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 38
Teacher表的数目如图所示
图片 39
Teacher_course表的数额如图所示
图片 40
Course_selection表的多少如图所示
图片 41

壹.四.叁.隐式事务形式

隐式事务格局是一种连接选项,在该选项下每一个连接执行的SQL语句都被视为单独的事体。当连接以隐式事务方式举办操作时,SQL
Server将在业务提交或作业回滚后自行开始新业务。隐式事务方式无需BEGIN
TRANSACTION那种话语来举办定义。

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

能够运用SQL Server 2008的管理器工具来启用递归触发器。
图片 42

1.三.三.嵌套触发器

1.2.一.体系提供的事体

系统提供的作业是指执行有个别T-SQL语句时,一条语句段构成了二个业务,如ALTEBMWX三TABLE,CREATE,DELETE,DROP,FETCH等。

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

结果如图所示
图片 43
图片 44

注:EVENTDATA()可在触发器内部选择,再次来到有关数据库和服务器事件的消息,以XML格式再次来到。唯有平昔在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会重临数据。假如EVENTDATA由其余例程调用(固然这一个例程由DDL或登录触发器举办调用),将回来
NULL

1.八.管制长日子运作的工作

壹.三.四.叁.直接递归

直接递归触发器是指任何递归进程有多少个触发器参加,例如A激活B,B激活C,C激活A。能够当作是递归和嵌套的结合。
运用递归触发器时须求注意以下几点:
递归触发器很复杂,必要通过有系统的布置性和周到测试
在任意点的数额修改都会激活递归触发器。只可以按触发器被激活的一定顺序更新表。
不无触发器1起构成2个大事务,任意触发器的随意地点上的ROLLBACK说话都将注销全数数据的输入,全体数据均被擦除。
触发器最四只好递归16层,1旦有第贰7个触发器参加进来,结果与ROLLBACK命令1样,全数数据都将被擦除

一.4.SQL Server本地业务援助

应用程序主要通过安装工作先河时间和作业甘休时间来治本业务。那可以透过函数也许应用程序接口(API)完结。默认情形下,事务按连接级别举行拍卖,使用API函数大概SQL语句,能够将事情作为显式,隐式和自行提交业务来拍卖。

1.三.4.递归触发器

1.3.2.@@TRANCOUNT变量和@@ERROR变量

@@TRANCOUNT变量报告当前嵌套事务为第几层嵌套,每一个BEGIN
TRANSACTION都能使@@TRANCOUNT加一,@@E凯雷德ROCR-V变量用来保存任何一条T-SQL语句的摩登错误号。
示例4:对示例三中代码加上对@@TRANCOUNT和@@ERAV4RO猎豹CS陆变量的拜访
实践下列语句

BEGIN TRANSACTION changed
SELECT @@TRANCOUNT AS trancount
INSERT INTO student(stu_no,stu_name,stu_sex,stu_enter_score)
VALUES('20180016','陈甜甜','女','661')
SAVE TRANSACTION saveinsert--设置保存事务点saveinsert
UPDATE student
SET stu_sex='错误数据'
WHERE stu_no='20180016'
SELECT @@ERROR AS error
ROLLBACK TRANSACTION saveinsert--回滚到保存事务点saveinsert
COMMIT TRANSACTION changed
GO

结果如图所示
图片 45
示例5:对@@TRANCOUNT变量的敞亮
施行下列语句

BEGIN TRANSACTION changed1
SELECT @@TRANCOUNT AS trancount
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('07','TEST','TEST')
BEGIN TRANSACTION changed2
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('08','TEST','TEST')
BEGIN TRANSACTION changed3
SELECT @@TRANCOUNT AS trancount
INSERT INTO class(class_id,class_name,enter_score_level)
VALUES('09','TEST','TEST')
COMMIT TRANSACTION changed3
COMMIT TRANSACTION changed2
COMMIT TRANSACTION changed1

我在changed1和changed叁中对@@TRANCOUNT变量举办了走访,结果如图所示
图片 46
每个BEGIN TRANSACTION都使@@TRANCOUNT加一。

1.3.1.3.UPDATE触发器

当针对对象数据库运维UPDATE言语时就会激活UPDATE触发器。对UPDATE触发器来说,一时半刻表INSERTEDDELETED还是有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来规定是或不是更新了多行和什么执行触发器动作。
Student表的多少如图所示
图片 47
Class_student表的数据如图所示
图片 48
示例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表的多寡如图所示
图片 49

注: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表的数码如图所示
图片 50
Class_student表的数额如图所示
图片 51
施行下列语句新建触发器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表的数额如图所示
图片 52
Class_student表的多寡如图所示
图片 53
大家再来验证update_stu_no_batch触发器对立异单行stu_no数量是或不是管用。将student表class_student表的数据改回原来的楷模,然后实施下列语句

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

Class_student表的数目如图所示
图片 54

注:在将表数据改成原来的榜样时,直接在编辑前200行中操作仍旧用T-SQL讲话操作,对student表数据操作,不成事的话要怀恋受键和封锁的熏陶,对class_student表数据操作,不成事的话要思虑受触发器影响。

一.8.一.查看长期运作的业务

执行下列语句

SELECT * FROM sys.dm_tran_database_transactions

结果如图所示
图片 55

1.触发器

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

1.四.四.批限制的事务

该事情只适用于四个运动的结果集。在MABMWX3S会话中运转的SQL显式或隐式事务,将成为批范围事务,当批处理完毕时,即便批范围事务还向来不被交付或回滚,SQL
Server将机关对其实行回滚。

1.四.管理触发器

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

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.事务

事务在SQL
Server中约等于贰个行事单元,能够保险同时发出的一言一动与数据的有效性不产生争持,并且吝惜数据的完整性。在实际应用中,多个用户在同样时刻对同1部分数据举办操作时,大概会出于一个用户的操作使别的用户的操作和数码失效。事务能够很好地化解那一点。事务总是确认保障数据库的完整性。

1.四.壹.电动提交业务形式

机动提交业务格局是SQL
Server私下认可的事务管理形式,各样SQL语句都是二个业务,在形成时都会被提交或回滚。在机关提交业务情势下,当碰到的失实是编写翻译时不当,会回滚整个批处理,当蒙受的不当是运转时不当,不会回滚整个批处理,而是实行部分语句并交付。
示例6:遇到编写翻译时不当和周转时不当时,事务处理情势是例外的
执行下列语句

--编译时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUE
('1003','卢哲','27')--语法错误,回滚整个批处理
GO
SELECT * FROM T1

结果能够见见,T1表即便被创设了,可是三条数据都尚未加塞儿成功。可知编写翻译时不当会回滚整个批处理。
去除T1表后实行下列语句

--运行时错误代码
USE test
GO
CREATE TABLE T1(
id INT NOT NULL,
name VARCHAR(20),
age INT,
CONSTRAINT pk_id PRIMARY KEY(id)
)
GO
INSERT INTO T1(id,name,age)VALUES
('1001','宋佳佳','26')
INSERT INTO T1(id,name,age)VALUES
('1002','陈琦','23')
INSERT INTO T1(id,name,age)VALUES
('1001','卢哲','27')--主键重复错误,仅该语句不执行
GO
SELECT * FROM T1

结果如图所示
图片 56
仅错误的INSE酷路泽T语句不执行,而整整批处理并未回滚。可见运转时不当不会导致整个批处理被回滚,仅仅只是中断执行。

一.柒.尖端事务大旨

相关文章

发表评论

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

网站地图xml地图