《SQL Server 2008从入门到精晓》–201807壹七

发布时间:2019-04-14  栏目:NoSQL  评论:0 Comments

目录

目录

1.事务

事情在SQL
Server中也正是1个工作单元,能够确认保障同时发生的行为与数量的有用不产生争论,并且爱抚数据的完整性。在实际利用中,四个用户在同方今刻对同壹部分数据开始展览操作时,恐怕会出于多个用户的操作使其余用户的操作和数量失效。事务能够很好地消除那或多或少。事务总是确定保证数据库的完整性。

1.触发器

触发器是一种尤其的贮存进度,与表紧凑关系。

1.1.事务的ACID属性

  • 原子性(Atomicity):事务是办事单元。事务内的具有工作要不全体完事,要不全部没到位,不存在落成都部队分的布道。
  • 一致性(Consistency):事务完毕时,全体的多寡都不能够不是千篇一律的。事务截止时,全数内部数据结构都必须是不错的。
  • 隔离性(Isolation):由并发事务所做的改动必须与别的并发事务所做的修改隔开分离。事务识别数据时数据所处的图景,要不是另一并发事务修改前的情状,要不是另一并发事务修改后的意况,不设有中间状态。
  • 持久性(Durability):事务提交后,事务所实现的干活结出会博得永久保存。

示例1:情状如下二个代码

--语句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中学,只有1个工作,对列的立异要不全体得逞更新,要不全部翻新战败。而语句第22中学,有八个事情,固然当中有有些列更新败北,也不会潜移默化其余列的翻新。

1.1.DDL触发器

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

  • 幸免对数据库框架结构进行变更
  • 期望数据库中发出一些情形以响应数据库架构中的更改
  • 要记录数据库架构中的更改或事件

一.二.事务分类

1.2.DML触发器

当数据库服务器中发生多少操作语言(DML)事件时将被调用。如INSERT,DELETE,UPDATE等操作。将DML触发器和触发语句作为可在触发器内回滚的单个事务对待,假如检查测试到不当,则全体育工作作回滚。DML触发器在须臾间地方分外有效:

  • 可完成数据库相关表之间的级联更改
  • 能够防范恶意或不当的DML说话事件,并强制执行比CHECK封锁越来越复杂的任何限制
  • 能够评估数据修改前后表的意况,并基于该距离接纳措施

贰个表中的多个同类DML触发器,允许用多少个不等的操作来响应同2个改动语句
SQL Server
2008
为各种触发器创制了3个尤其的表:INSERTED表和DELETED表。那是多个逻辑表,由系统来创建和护卫,用户不可能对她们开始展览改动。它们存放在内部存款和储蓄器中,而不是在数据库中,并且组织与被DML触发器效用的表的布局同样。
INSERTED表中存放了由履行INSERTUPDATE语句而插入的兼具行,在实行INSERTUPDATE言语时,新的即将同时被插入到触发器成效的表和INSERTED表中。INSERTED表中的行是触发器成效的表中行的副本。
DELETED表中存放了由实践DELETEUPDATE语句而除去的有着行,在执行DELETEUPDATE言语时,被删去的即将由触发器效能的表中被移动到DELETED表,多个表中不会有重复行。

一.2.一.系统提供的事情

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

一.三.创立触发器

一.贰.贰.用户自定义的思想政治工作

实则行使中,日常利用用户自定义的事务。自定义的主意是,以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台存放仓库储存数量,另一台存放订单数量,用户下单的逻辑是,下单前先扣除仓库储存数据,再下单。假如未有分布式事务,简单现身扣除仓库储存数据,单下单却没得逞,造成多个数据库数据不平等的动静。

1.3.1.创建DML触发器

1.叁.管理业务

第一采用以下肆条语句管理业务:BEGIN TRANSACTION,COMMIT
TRANSACTION,ROLLBACK TRANSACTION和SAVE
TRANSACTION。其它还有1个全局变量能够用在事务处理语句中:@@E奇骏ROSportage和@@TRANCOUNT。
BEGIN TRANSACTION,COMMIT TRANSACTION,ROLLBACK TRANSACTION不多说了。

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中的代码稍作修改封装成二个用户自定义存款和储蓄进度
存款和储蓄进程示例三
修改后的代码如下

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

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

上述代码完成了三个这么的作用:设置1个工作,事务名changed,该业务的成效是向student表中插入一条记下并立异该记录的stu_sex字段。若是更新退步,则回滚到插入操作,即确认保障不管更新是还是不是中标,插入操作都能打响。

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表定义3个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表中机动删除。

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

@@TRANCOUNT变量报告当前嵌套事务为第几层嵌套,每种BEGIN
TRANSACTION都能使@@TRANCOUNT加一,@@E哈弗ROCRUISER变量用来保存任何一条T-SQL语句的风靡错误号。
示例4:对示例叁中代码加上对@@TRANCOUNT和@@E君越RO奇骏变量的造访
实践下列语句

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

结果如图所示
图片 11
示例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

自己在changed一和changed三中对@@TRANCOUNT变量进行了拜访,结果如图所示
图片 12
每个BEGIN TRANSACTION都使@@TRANCOUNT加一。

1.3.1.3.UPDATE触发器

当针对对象数据库运转UPDATE讲话时就会激活UPDATE触发器。对UPDATE触发器来说,权且表INSERTEDDELETED反之亦然有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来鲜明是还是不是更新了多行和什么实施触发器动作。
Student表的数目如图所示
图片 13
Class_student表的多少如图所示
图片 14
示例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表的数额如图所示
图片 15

注: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表的数目如图所示
图片 16
Class_student表的数额如图所示
图片 17
施行下列语句新建触发器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表的数额如图所示
图片 18
Class_student表的多寡如图所示
图片 19
咱俩再来验证update_stu_no_batch触发器对立异单行stu_no多少是不是行得通。将student表class_student表的多寡改回原来的样板,然后实施下列语句

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

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

注:在将表数据改成原来的典范时,直接在编写制定前200行中操作照旧用T-SQL讲话操作,对student表数据操作,不成事的话要考虑受键和平条约束的震慑,对class_student表数据操作,不成功的话要惦念受触发器影响。

1.四.SQL Server本地作业援助

应用程序首要透过设置工作初阶时间和业务停止时间来保管业务。那能够因而函数可能应用程序接口(API)达成。私下认可情状下,事务按连接级别实行拍卖,使用API函数或许SQL语句,能够将工作作为显式,隐式和活动提交业务来拍卖。

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够内定执行触发器,而不是履行触发SQL说话,从而屏蔽原来的SQL话语,而转向执行触发器内部的话语。每一种表大概视图只好有三个INSTEAD
OF
触发器。INSTEAD
OF
触发器的特征是,能够使作为触发条件的SQL语句不举行。
Membership表的多少如图所示
图片 21
Call_slip表的多寡如图所示
图片 22
示例7:对LibraryManagement数据Curry的membership表写2个防删除触发器,尚有借书未还的读者不能够被删除
施行下列语句创造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'

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

结果如图所示
图片 26
Membership表的多寡如图所示
图片 27
示例9:对LibraryManagement数据Curry的call_slip表写四个防超借触发器,二个读者的未还图书最两只好有伍本,超出无法再借(那里照旧针对批量甩卖多少创造触发器)
Call_slip表的数量如图所示
图片 28
推行下列语句创设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”的用户借书未还超越伍本,应该是心有余而力不足再借的。

--测试数据
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','未归还')

结果如图所示
图片 29
Call_slip表的多寡如图所示,红框里是新插入的多寡
图片 30

一.四.壹.自动提交业务方式

活动提交业务情势是SQL
Server暗中同意的事务管理形式,每一种SQL语句都以2个工作,在实现时都会被交付或回滚。在机动提交业务情势下,当遇到的失实是编写翻译时不当,会回滚整个批处理,当蒙受的不当是运维时不当,不会回滚整个批处理,而是实行部分语句并交由。
示例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

结果可以观察,T壹表就算被创立了,不过3条数据都未曾加塞儿成功。可知编写翻译时不当会回滚整个批处理。
删去T一表后执行下列语句

--运行时错误代码
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

结果如图所示
图片 31
仅错误的INSE大切诺基T语句不进行,而全部批处理并不曾回滚。可知运营时不当不会招致整个批处理被回滚,仅仅只是中断执行。

1.3.2.创建DDL触发器

DDL触发器只为了响应CREATEDROPALTER事件而激活,它的效用域是总体数据库只怕服务器,而不是效益域某张表或打算。它能够使得控制哪位用户能够修改数据库结构以及哪些修改。
示例10:创造3个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)
)

结果如图所示
图片 32
图片 33

注:EVENTDATA()可在触发器内部接纳,重临有关数据库和服务器事件的音讯,以XML格式重回。唯有一贯在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会回去数据。尽管EVENTDATA由别的例程调用(尽管那么些例程由DDL或登录触发器进行调用),将回到
NULL

壹.肆.二.显式事务形式

有可想而知使用BEGIN
TRANSACTION语句定义三个业务的正是显式事务情势。示例2,三,四,5都以显式事务形式。

①.三.三.嵌套触发器

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

隐式事务情势是1种连接选项,在该选项下各类连接执行的SQL语句都被视为单独的工作。当连接以隐式事务情势开始展览操作时,SQL
Server将在事情提交或业务回滚后自行起首新工作。隐式事务形式无需BEGIN
TRANSACTION那种话语来进展定义。

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

若是2个触发器在执行操作时引发了另多个触发器,而这些触发器又引发了下一个触发器,那么那几个触发器便是嵌套触发器。嵌套触发器在安装时就被启用,不过足以接纳sp_configure仓储进度禁止使用和重复启用嵌套。
DML触发器和DDL触发器最多能够嵌套32层,能够透过nested
triggers
来陈设是或不是可以嵌套AFTER触发器,不过不管此设置哪些都能够嵌套INSTEAD
OF
触发器。倘诺嵌套触发器进入了无与伦比循环,该触发器将被终止,并且回滚整个事情。嵌套触发器具有三种用处,比如保留前四个触发器所影响的行的副本。
使用嵌套触发器时应当小心以下几点:

  • 默许意况下,嵌套触发器配置选项开启。
  • 在同2个触发器事务中,3个触发器不会被触发三遍,触发器不会调用他本身来响应触发器中对同三个表的第3回立异
  • 是因为触发器是3个事务,一旦嵌套中其它1层的触发器现身错误,将回滚整个事情。

示例11:有teacher_course表(教授所教学程表),course表(课程表)和course_selection表(学生选课表),写1个嵌套触发器,达成课程撤销后,删除教授所教学程表中有关该科目标笔录,而导师所教师程表中该课程的记录被撤除,导致该学科的学习者选课记录也做相应撤消。
施行下列语句

--创建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表中的数据如图所示
图片 34
Teacher_course表中的数据如图所示
图片 35
Course_selection**表中的数据如图所示
图片 36
以课程00壹叁为例,执行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的数码如图所示
图片 37
Teacher_course表的数额如图所示
图片 38
Course_selection表的多寡如图所示
图片 39
有着有关00一叁课程的数量都被删除。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,作者额外到场了三个判断,当teacher_course表中还有老师在讲解那门课程时,全部关于那门学科的学员选课消息都置之脑后删除。那样做在嵌套触发器里是剩下的,删除一门课程,必然会去除teacher_course表中有所与那门课程有关的笔录,也决然删除course_selection表中存有与那门学科有关的记录,可是,那样做能够确定保证该触发器能够单独于嵌套触发器被单独激活。Teacher_course_delete_batch触发器还能够用来其它嵌套触发器中,看示例12

示例12:有teacher表(教授音信表),teacher_course(助教所教学程表),和course_selection表(学生选课记录表),写一个嵌套触发器,完毕当2个导师离职时,在剔除该教授所教课程音讯,假若未有教授教那门学科,再删除该科目选课记录。
其中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表的数额如图所示
图片 40
Teacher_course表的多寡如图所示
图片 41
Course_selection表的数据如图所示
图片 42
以删除0012号教授路易为例,001二号教师教学00壹三号课程,且teacher_course表中并无此外导师授课001三号课程,依据逻辑要删减teacher_course表中001二号教授的所教课程记录和course_selection表中装有00壹三号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

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

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 46
Teacher表的数量如图所示
图片 47
Teacher_course表的数目如图所示
图片 48
Course_selection表的数码如图所示
图片 49

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

结果如图所示
图片 50
下一场断开连接,出现如下提示
图片 51
一经选取否的话,再度连接成功后SELECT T壹表,结果如图所示
图片 52
会发现100二和十0三的记录都被回滚了,那是因为在插入的时候,那两条语句的政工未有COMMIT,惟有首先条插入语句被交付了。那正是隐式事务方式。

壹.三.三.二.翻看触发器嵌套的层数

能够采纳@@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触发器

结果如图所示
图片 53
实践下列语句

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

结果如图所示
图片 54

壹.四.三.二.调用API函数来设置隐式事务方式

用来安装隐式事务方式的API机制是ODBC和OLE DB(不能够领会,不多说了)

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

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

一.四.4.批范围的事情

该业务只适用于多个移动的结果集。在MA索罗德S会话中运转的SQL显式或隐式事务,将变成批范围事务,当批处理实现时,如若批范围事务还未有被交付或回滚,SQL
Server将活动对其进展回滚。

壹.三.四.递归触发器

1.5.隔开级别

当多少个线程都敞开事务来操作数据库中的数据时,数据库要能实行隔开操作,以保证各样线程获取数据的准确性。如果未有隔断操作,会出现以下两种情景:

  • 脏读:八个事务处理过程里读取了另二个未提交的事体中的数据。

例如:A转十0块钱给B,SQL语句如下

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

实施完第1条语句时,A布告B,让B确认是还是不是到账,B确认钱到账(此时爆发了脏读),而后无论第1条SQL语句是还是不是执行,只要工作未有交到,全部操作都将回滚,B第三次查看时发现钱未有到账。

  • 不行重复读:二个事情限制内多次询问某些数据,重临分裂的值,那是因为该数量被另三个工作修改并提交了。脏读和不得重复读的分裂在于,脏读是读取了另一个业务还未提交的数量,不可重复都是读取了累累读取了前1个事情提交了的数目
  • 幻读:比如事务T1将表中某一列数据从一修改成二,同时T二事务插入一条数据,该列值照旧是一,那么用户查询时就会发觉该表还有1列数据为壹,未被T一事务修改。

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

触发器被激活,更改了表中多少,那种转移又激活了它本人,那种触发器被喻为递归触发器。数据库创制时私下认可递归触发器禁止使用。但足以应用ALTER
DATABASE
挑选来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用情状,要是嵌套触发器禁用,不管递归触发器的配置是怎样都将被剥夺。而在递归触发器中,inserted表和deleted表都只含有被上1回触发器影响的行数据。
递归触发器有以下二种不一样门类(那边未有适用的接纳示范可举,先不举例了)

一.5.一.八种隔开级别

  • 未提交读(READ
    UNCOMMITTED):事务隔断的最低级别,可进行未提交读和脏读,任何意况都无法儿确定保证
  • 交由读(READ
    COMMITTED):在读取数据时间控制制共享锁,制止脏读,但无能为力制止不可重复读和幻读。它是SQL
    Server 二〇一〇的私下认可值。
  • 可再一次读(REPEATABLE
    READ):锁定查询过程中拥有数据,幸免用户更新数据,幸免了脏读和不足重复读的产生,不可能制止幻读。
  • 可串行读(SE瑞虎IALZABLE):在数额集上放置3个范围锁,幸免别的用户在工作达成以前更新数据或插入行,是业务隔开的最大范围级别,防止了脏读,不可重复读和幻读的发出。

政工隔开级别越高,越能保障数据的一致性和完整性。

1.三.4.2.直接递归

一直递归触发器是指任何递归进度只有它自身一个触发器的插手。自身激活了和睦。

一.5.二.设置工作隔开分离级别

暗中认可情形下,SQL Server 贰零1零的事情隔断级别为付出读。可透过SET TRANSACTION
ISOLATION LEVEL来安装工作隔开级别。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

1.三.四.三.直接递归

直接递归触发器是指任何递归进度有四个触发器加入,例如A激活B,B激活C,C激活A。能够当作是递归和嵌套的构成。
运用递归触发器时须要注意以下几点:
递归触发器很复杂,须要经过有系统的设计和全面测试
在任意点的数量修改都会激活递归触发器。只好按触发器被激活的一定顺序更新表。
全体触发器1起构成七个大事务,任意触发器的随意地方上的ROLLBACK话语都将撤除全体数据的输入,全数数据均被擦除。
触发器最五只好递归1陆层,壹旦有第二多少个触发器参预进来,结果与ROLLBACK命令一样,全数数据都将被擦除

1.陆.分布式事务

对八个数据库中的数据开始展览改动的政工,是分布式事务。这么些数据库能够是地点数据库,也得以是其余链接服务器上的数据库。
分布式事务由一个分布式事务协调程序(DTC)来支配,若想接纳分布式事务,必须先运行该服务。在分布式事务中用COMMIT
TRANSACTION提交业务,数据库会自行调用贰个两步提交协议:一.通报每种数据库核实它们能够交给该事务并保留财富。2.当种种相关数据库公告SQL
Server 二〇一〇可以随时提交该工作后,SQL Server
二〇〇八通报有关数据库提交该业务。假使有一个数据库无法不负众望交付该事务,则SQL
Server 二〇〇八会通报全体有关数据库回滚该事情。

1.3.四.4.启用递归触发器

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

1.七.高等事务主题

  • 嵌套事务:显式事务可以嵌套在蕴藏进程中
  • 作业保存点:提供了一种能够部分回滚事务的编写制定
  • 绑定会话:有利于在一个服务器上的多少个会话之间的调和操作,允许2个或八个会话共享工作和锁,并且能够运用同一个数码,不会有锁的争辩

一.四.管制触发器

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

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

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

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

执行下列语句

SELECT * FROM sys.dm_tran_database_transactions

结果如图所示
图片 56

一.八.二.停下业务

甘休作业或许必须运营KILL语句,使用该语句时要小心,尤其是在运转重点的经过时。

留下评论

网站地图xml地图