《SQL Server 二零零六从入门到领会》–20180716

By admin in SQL on 2019年8月30日

1.锁

当多个客商同临时候对同三个数额开展更改时会发生并发难题,使用工作就能够化解那一个难点。可是为了防止万一别的客商修改另五个还没到位的政工中的数据,就供给在作业中用到锁。
SQL Server
贰零壹零提供了种种锁情势:排他锁,分享锁,更新锁,意向锁,键范围锁,架构锁和大体积更新锁。
查询sys.dm_tran_locks视图能够高速明白SQL Server 二零一零内的加锁情状。

SELECT * FROM sys.dm_tran_locks;

注:关于锁的知识书中没细讲,将要事后的博客中补充。

目录

2.游标

游标是附近于C语言指针一样的布局,是一种多少访问机制,允许客户访谈单独的数据行。游标首要由游标结果集和游标地点组成。游标结果集是概念游标的SELECT语句再次来到行的集纳,游标地方是指向这些结果聚集某一行的指针。
示例1:用游标检索出student表中每行记录
Student表记录如图所示
图片 1
实行下列语句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--声明student表的游标stu
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor--移动该记录指针
WHILE @@FETCH_STATUS=0--@@FETCH_STATUS用于保存FETCH操作的结束信息,=0表示有记录检索成功
BEGIN
FETCH NEXT FROM stu_cursor--游标指针移动到下一条记录
END
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源

结果如图所示
图片 2

2.1.游标定义的参数LOCAL和GLOBAL

游标定义参数LOCAL表示该游标只可以作用于此番批管理或函数或存储进程。游标定义参数GLOBAL表示该游标能够作用于大局。
实践下列语句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

实行结果如下
图片 3
语句中,证明了一个student表的游标stu_cursor,在开采游标时提示游标不设有。因为该游标参数是LOCAL,只好成效于前段时间批处理语句中,而张开游标语句和注明语句不在八个批管理中。倘若去掉第一个GO,使三个语句在同三个批管理中,就会面面俱到实行不会报错。
施行下列语句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

推行结果:命令已成功达成
和LOCAL参数对比,GOLBAL参数设置游标功用于大局,因此OPEN和DECLARE语句不在同多少个批管理中依然可以成功实施。

1.触发器

触发器是一种非常的积累进度,与表紧凑关系。

2.2.游标分为游标变量和游标类型

正如列语句

--语句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值
GO

--语句2
DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursor
SET @stu_cursor=CURSOR FOR--给该变量赋值
SELECT * FROM student

在语句第11中学央机关单位接注明了四个游标并赋值,而语句第22中学表明了游标类型的变量@stu_cursor,然后给该变量赋值。那二者是见仁见智的。

1.1.DDL触发器

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

  • 防卫对数据库框架结构实行改动
  • 梦想数据库中生出一些景况以响应数据库架构中的退换
  • 要记录数据库架构中的改动或事件

2.3.游标参数FO奥迪Q3WA昂科雷D_ONLY和SCROLL

FORWARD_ONLY参数设置游标只可以从结果集的最早向截止方向读取,使用FETCH语句时只能用NEXT,而SCROLL参数设置游标能够从结果集的任意方向,猖狂地方移动。如下列语句

--语句1,默认FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--语句2,FORWARD_ONLY参数,FETCH时只能从开始往结束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--语句3,SCROLL参数,FETCH时可以从任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO

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表,多少个表中不会有重复行。

2.4.游标的简要利用

示例2:将student表中stu_enter_score大于600分的学习者都减去100分
Student表中的数据如图所示
图片 4
实行下列语句

--游标的简单应用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
图片 5

1.3.创设触发器

3.积存进程

存款和储蓄进程是一组用于达成一定效率的语句集,经过编写翻译后存款和储蓄在数据库中。在SQL
Server 二零一零中,既可以够用T-SQL编写存款和储蓄进度,也得以用CLLacrosse编写存款和储蓄进程。

1.3.1.创建DML触发器

3.1.顾客定义的寄放进程

该种存款和储蓄进程是指封装了可选择代码的模块可能经过,有2类别型:T-SQL存款和储蓄进度和CL揽胜存款和储蓄进程。
T-SQL存款和储蓄进程是指保存的T-SQL语句集合
CL凯雷德存款和储蓄进程是指对Microsoft .NET Framework公共语言运营时(CL凯雷德)方法的引用

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表中的数据如图所示
图片 6
游标示例2:对student表中还未分班的学习者进行分班
Student表中的数据如图所示
图片 7
其中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表的数目
图片 8
Class_student表的数额
图片 9
至此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
图片 10
推行存储进程

EXEC dbo.student_division

结果如图所示
Student表的多少(分班状态都为true了)
图片 11
Class_student表的数据
图片 12

3.2.扩展存款和储蓄进度

恢宏存款和储蓄进度是指能够动态加载和周转的DLL,允许采取编制程序语言(如C语言)创制和煦的外表例程。扩充存款和储蓄进度平素在SQL
Server 二〇〇八的实例的地点空间中运维,能够利用SQL
Server扩充存款和储蓄进度API实现编程。

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表的数据如图所示
图片 13
Class_student表的数目如图所示
图片 14
奉行下列语句

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

student表来看,唯有入学编号为2018001120180012的学习者成绩被删除。该操作激活了delete_student触发器
Class_student表的数额如图所示
图片 15
入学编号为2018001120180012的学员分班新闻已经从class_student表中自行删除。

3.3.种类存款和储蓄进度

系统存储进程是指储存在源数据库中,以sp开头的贮存进程,出今后各类系统定义数据库和客商定义数据库的sys架构中。

1.3.1.3.UPDATE触发器

当针对对象数据库运转UPDATE言辞时就能够激活UPDATE触发器。对UPDATE触发器来讲,临时表INSERTEDDELETED还是有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来规定是或不是更新了多行和怎么着奉行触发器动作。
Student表的数目如图所示
图片 16
Class_student表的数额如图所示
图片 17
示例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表的多少如图所示
图片 18

注: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表的数码如图所示
图片 19
Class_student表的多少如图所示
图片 20
奉行下列语句新建触发器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表的多寡如图所示
图片 21
Class_student表的数量如图所示
图片 22
我们再来验证update_stu_no_batch触发器周旋异单行stu_no数量是或不是管用。将student表class_student表的数量改回原本的旗帜,然后实行下列语句

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

Class_student表的数额如图所示
图片 23

注:在将表数据改成原本的理所当然时,直接在编写前200行中操作还是用T-SQL言辞操作,对student表数据操作,不成功的话要思考受键和自律的影响,对class_student表数据操作,不成功的话要思量受触发器影响。

3.3.1.成立存款和储蓄进度准则

在统一企图和创设存款和储蓄进度时,应该满意一定的约束和准则。

  • CREATE
    PROCEDURE定义本人能够富含私下数量和档次的SQL语句,但下表中的语句除了那些之外。不可能在存储进程的另外岗位接纳那几个讲话。
  • 能够引用在联合存款和储蓄进程中创制的靶子,只要援用时已创制了该对象
  • 能够在存款和储蓄进度内引用不时表
  • 假诺在存储进程中开创了本地一时表,该临时表仅为该存储进度而留存,退出该存款和储蓄进程后,该有的时候表会消失
  • 万一施行的囤积进度调用了另一个储存进程,被调用的存款和储蓄进程能够访谈第二个存款和储蓄进程的全体目的,饱含不时表
  • 假诺实施对长途SQL Server
    二〇〇八实例实行转移的长距离存储进程,那一个改造将不能够被回滚。远程存款和储蓄进程不插足事务管理
  • 仓储进度中的参数的最大数据为2100
  • 积攒进程中的局地变量的最大额仅受可用内部存款和储蓄器的范围
  • 凭借可用内部存款和储蓄器的分歧,存款和储蓄进度最大可达128MB
语句 语句 语句
CREATE AGGREGATE CREATE RULE CREATE DEFAULT
CREATE SCHEMA CREATE(ALTER) FUNCTION CREATE(ALTER) TRIGGER
CREATE(ALTER) PROCEDURE CREATE(ALTER) VIEW SET PARSEONLY
SET SHOWPLAN_ALL SET SHOWPLAN_TEXT SET SHOWPLAN_XML
USE database_name

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够钦命实践触发器,并非执行触发SQL言辞,进而屏蔽原本的SQL讲话,而转向实践触发器内部的说话。各类表也许视图只好有1个INSTEAD
OF
触发器。INSTEAD
OF
触发器的表征是,能够使作为触发条件的SQL语句不进行。
Membership表的数额如图所示
图片 24
Call_slip表的多寡如图所示
图片 25
示例7:对LibraryManagement数据库里的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'

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

结果如图所示
图片 29
Membership表的数量如图所示
图片 30
示例9:对LibraryManagement数据Curry的call_slip表写一个防超借触发器,贰个读者的未还图书最三只好有5本,凌驾不能够再借(这里照旧针对批量甩卖多少成立触发器)
Call_slip表的数额如图所示
图片 31
实践下列语句创设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','未归还')

结果如图所示
图片 32
Call_slip表的数码如图所示,红框里是新插入的数额
图片 33

3.3.2.范围存款和储蓄进度内的名号

在存款和储蓄进度内,要是用于语句的对象未有界定架构,则架构将默以为该存款和储蓄进程的框架结构。假诺创立该存款和储蓄进程的客户并未有限定INSERT,SELECT,UPDATE或DELETE语句中引用的表名或试图名,则私下认可意况下通过该存款和储蓄进度举办的看望将倍受该进程创立者权限的范围。假若有其余顾客要运用存款和储蓄进程,则持有用于数据定义语言(DDL)的口舌(如CREATE,ALTERAV4,EXECUTE,DROP,DBCC或动态SQL语句)的对象名应当用该目的架构的名目来界定。

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

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

注:EVENTDATA()可在触发器内部接纳,再次回到有关数据库和服务器事件的新闻,以XML格式返回。只有一直在DDL或登陆触发器内部引用EVENTDATA时,EVENTDATA才会回来数据。假使EVENTDATA由其余例程调用(纵然那么些例程由DDL或登入触发器进行调用),将赶回
NULL

3.3.3.加密存款和储蓄进程的定义

若果要创造存款和储蓄进程并保管其余客商不恐怕查看该存款和储蓄进度的定义,则足以行使WITH
ENCRubiconYPTION,那样,进程定义将以不足读的方式积存。

1.3.3.嵌套触发器

3.3.4.SET语句选项

当创立或然改换T-SQL存款和储蓄进程后,数据库引擎将保留SET
QUOTED_IDENTIFIER和SET
ANSI_NULLS的安装,实行存款和储蓄进程时将动用这一个原来设置而忽略任何顾客端会话的ET
QUOTED_IDENTIFIER和SET
ANSI_NULLS设置。其余SET选项在开创或更改存款和储蓄进程后不保留。

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

DELETE FROM course WHERE course_id='0013'

Course表的多寡如图所示
图片 39
Teacher_course表的数量如图所示
图片 40
Course_selection表的数目如图所示
图片 41
有着有关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表的数额如图所示
图片 42
Teacher_course表的多少如图所示
图片 43
Course_selection表的数据如图所示
图片 44
以删除0012号教师路易为例,0012号教师教学0013号课程,且teacher_course表中并无别的助教授课0013号课程,遵照逻辑要刨除teacher_course表中0012号教授的所教课程记录和course_selection表中颇具0013号课程的选课记录。推行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

Teacher表的数量如图所示
图片 45
Teacher_course表的数目如图所示
图片 46
Course_selection表的数额如图所示
图片 47
测量检验结果准确
参照上边的数目,继续测量试验另一种情状,以删除0011号教师卢含笑为例,0011号教授讲课0012号课程,在teacher_course表中还应该有任何老师执教该科目,由此嵌套触发器会删除teacher_course表中有关0011号教授执教课程记录,但不会删除course_selection表中关于0012号课程的选课记录。推行下列语句

DELETE FROM teacher WHERE teacher_id='0011'
GO

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

3.4.利用存款和储蓄进度

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触发器

结果如图所示
图片 52
进行下列语句

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

结果如图所示
图片 53

3.4.1.开立存款和储蓄进程

示例3:将示例2用存款和储蓄进程完结
Student表的数目如图所示
图片 54
实行下列语句

CREATE PROCEDURE alter_data
@a int--参数
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=@a
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

结果如图所示
图片 55

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

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

3.4.2.查看存款和储蓄进度

能够通过应用系统存款和储蓄进度照旧目录视图查看存款和储蓄进程的定义

1.3.4.递归触发器

3.4.2.1.图形化分界面

如下图
图片 56

1.3.4.1.递归触发器

触发器被激活,改造了表中多少,这种转移又激活了它本身,这种触发器被称呼递归触发器。数据库制造时暗中同意递归触发器禁用。但足以行使ALTER
DATABASE
慎选来启用它。递归触发器启用的先决条件是嵌套触发器必需是启用情状,如若嵌套触发器禁止使用,不管递归触发器的布局是何许都将被剥夺。而在递归触发器中,inserted表和deleted表都只含有被上一回触发器影响的行数据。
递归触发器有以下三种分裂品类(那边未有确切的选拔示范可举,先不及如了)

3.4.2.2.系统存储进度sp_helptext查看存款和储蓄进度定义

实施下列语句

EXEC sp_helptext 'alter_data'

结果如图所示
图片 57

1.3.4.2.一直递归

一贯递归触发器是指任何递归进度独有它本身八个触发器的参加。本身激活了投机。

3.4.2.3.系统存款和储蓄进程sp_depends查看存款和储蓄进度有关信息

实施下列语句

EXEC sp_depends 'alter_data'

结果如图所示
图片 58

1.3.4.3.间接递归

直接递归触发器是指任何递归进程有三个触发器参加,比如A激活B,B激活C,C激活A。能够当做是递归和嵌套的整合。
选拔递归触发器时索要潜心以下几点:
递归触发器很复杂,须要通过有系统的统一希图和百科测验
在放肆点的数据修改都会激活递归触发器。只可以按触发器被激活的一定顺序更新表。
享有触发器一齐构成三个大事务,率性触发器的率性地方上的ROLLBACK言语都将撤废全数数据的输入,全数数据均被擦除。
触发器最多只好递归16层,一旦有第14个触发器参预进来,结果与ROLLBACK命令同样,全体数据都将被擦除

3.4.2.4.目录视图查看存款和储蓄进程

实施下列语句

SELECT * FROM sys.procedures

结果如图所示
图片 59

1.3.4.4.启用递归触发器

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

3.4.3.修改存款和储蓄进程

ALTER
PROCEDURE
语句修改存款和储蓄进程,只需将上边示例中的CREATE修改成ALTEENCORE运维就行了。

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

3.4.4.去除存款和储蓄过程

施行下列语句删除存款和储蓄进程

DROP PROCEDURE alter_data

发表评论

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

网站地图xml地图
Copyright @ 2010-2019 韦德国际手机网站 版权所有