《SQL Server 二零零六从入门到理解》–201807一7

XML查询技术

XML文档以二个纯文本的款式存在,首要用以数据存款和储蓄。不但有利于用户读取和利用,而且使修改和掩护变得更便于。

目录

XML数据类型

XML是SQL
Server中置放的数据类型,可用以SQL语句或然当做存款和储蓄进程的参数。用户能够间接在数据库中储存、查询和管理XML文件。XML数据类型还可以保留整个XML文书档案。XML数据类型和别的数据类型不存在根本上的异样,能够把它用在其他1般SQL数据类型能够使用的地点。
示例1:创造三个XML变量并用XML填充

DECLARE @doc XML
SELECT @doc='<Team name="Braves" />';

示例2:创制XML数据类型列

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column1));

在上边的演示中,column贰列是XML数据类型列。
示例3:不可能将XML数据类型列设置为主键或外键

CREATE TABLE t1(
column1 INT,
column2 XML,
CONSTRAINT pk_column1 PRIMARY KEY(column2));

实践上边包车型地铁代码,报错如下:
消息1919,级别16,状态1,第1 行
表’t壹’ 中的列’column二’ 的品类不能够用作索引中的键列。
消息1750,级别16,状态0,第1 行
没辙创造约束。请参阅前边的荒谬音讯。
XML数据类型的施用限制
唯有STRubiconING数据类型才能转换到XML。
XML列不能够运用于GROUP BY语句中
XML数据类型存款和储蓄的数量不可能超过贰GB
XML数据类型字段不能被设置成主键也许外键或称为其1部分。
Sql_variant数据类型字段的使用不可能把XML数据类型作为种子品种。
XML列不可能钦定为唯一的。
COLLATE子句不能够被选取在XML列上。
存储在数据库中的XML仅扶助128级的层次。
表中最对只好拥有36个XML列。
XML列无法投入到规则中。
唯一可使用于XML列的放置标量函数是ISNULL和COALESCE。
具备XML数据类型列的表不可能有叁个超过一伍列的主键。

类型化的XML和非类型化的XML

能够创制xml类型的变量,参数和列,也许将XML架构集合和xml类型的变量、参数或列关联,那种意况下,xml数据类型实例称之为类型化xml实例。不然XML实例称为非类型化的实例。

1.触发器

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

XML数据类型方法

XML数据类型共有五种艺术
query():执行三个XML查询并重临查询结果(重回一个XML数据类型)。
示例4

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SELECT @xmlDoc.query('/students/class/student') AS test
--用query()查询@xmlDoc变量实例中标签<student>的子元素

询问结果如图所示
图片 1
点击查询结果
图片 2
如想查询标签

DECLARE @addr XML--声明一个XML类型变量@addr
SET @addr='/students/class/student'
SELECT @addr.exist('/students/class="江苏"') AS 返回值

结果如图所示
图片 3

注:exsit()方法的参数不必做精显明位

Value():总计1个查询并从XML中回到三个大致的值(只可以回到单个值,且该值为非XML数据类型)。
Value()方法有1个参数XQuery和SQLType,XQuery参数表示命令要从XML实例之中查询数据的具体地方,SQLType参数表示value()方法再次回到的值的首要选拔数据类型。
示例6

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
DECLARE @classID INT--声明INT类型的变量@classID
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'--将XML实例分配给变量@xmlDoc
SET @classID=@xmlDoc.value('(/students/class/@NO)[1]','INT')
--将value()方法返回值赋值给变量@classID
SELECT @classID AS classID

查询结果如图所示
图片 4

注:SQLType无法是XML数据类型,公共语言运转时(CLOdyssey)用户定义类型,image,text,ntext或sql_variant数据类型,但足以是用户自定义数据类型SQL。

Modify():在XML文书档案的适合地点执行二个修改操作。它的参数XML_DML代表一串字符串,依照此字符串说明式来更新XML文书档案的内容。
示例7:在@xmlDoc的实例中,元素

DECLARE @xmlDoc XML--声明XML类型的变量@xmlDoc
SET @xmlDoc='<students>
    <class name="数学" NO="8501">
        <student>
            <name>李林</name>
            <sex>男</sex>
            <age>16</age>
            <address>江苏</address>
        </student>
    </class>
</students>'
SELECT @xmlDoc AS '插入节点前信息'
SET @xmlDoc.modify('insert <学历>本科</学历> after (students/class/student/age)[1]')
SELECT @xmlDoc AS '插入节点后信息'

查询结果插入节点后音信如图所示
图片 5

注:modify()方法的参数中insert和别的重点字必须小写,不然会报错

Nodes():允许把XML分解到二个表结构中。此方法将XML数据类型实例拆分为关周详据,并赶回包含原始XML数据的行集。
示例8:依旧用@locat参数的实例来演示

DECLARE @locat XML--声明XML变量@locat
SET @locat=
'<root>
    <location locationID="8">
        <step>8的步骤</step>
        <step>8的步骤</step>
        <step>8的步骤</step>
    </location>
    <location locationID="9">
        <step>9的步骤</step>
        <step>9的步骤</step>
        <step>9的步骤</step>
    </location>
    <location locationID="10">
        <step>10的步骤</step>
        <step>10的步骤</step>
        <step>10的步骤</step>
    </location>
    <location locationID="11">
        <step>11的步骤</step>
        <step>11的步骤</step>
        <step>11的步骤</step>
    </location>
</root>'--@locat变量的实例

SELECT T.Loc.query('.') AS result
FROM @locat.nodes('/root/location') T(Loc)
GO

查询结果如下图所示
图片 6

1.1.DDL触发器

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

  • 预防对数据库架构进行变更
  • 瞩望数据库中发出壹些情形以响应数据库框架结构中的更改
  • 要记录数据库架构中的更改或事件

XQuery简介

XQuery是一种查询语言,能够查询结构化或许半结构化的数额。SQL Server
200九中对XML数据类型提供了补助,可以存款和储蓄XML文书档案,然后接纳XQuery语言进行询问。

1.2.DML触发器

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

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

3个表中的八个同类DML触发器,允许用多个不等的操作来响应同一个修改语句
SQL Server
2008
为各样触发器创造了二个例外的表:INSERTED表和DELETED表。那是四个逻辑表,由系统来创建和保卫安全,用户无法对她们开始展览改动。它们存放在内部存款和储蓄器中,而不是在数据库中,并且组织与被DML触发器功能的表的布局同样。
INSERTED表中存放了由实施INSERTUPDATE语句而插入的拥有行,在进行INSERTUPDATE言辞时,新的即将同时被插入到触发器效能的表和INSERTED表中。INSERTED表中的行是触发器功能的表中央银行的副本。
DELETED表中存放了由举办DELETEUPDATE语句而除去的装有行,在实施DELETEUPDATE言辞时,被删除的即将由触发器效能的表中被活动到DELETED表,多少个表中不会有重复行。

FOR XML子句

因而在SELECT语句中选取FO帕杰罗XML子句能够把数据库表中的数据检索出来并生成XML格式。SQL Server
2010补助FOSportageXML的各类方式,分别是RAW情势,AUTO形式,EXPLICIT格局和PATH形式。

一.三.创设触发器

FOR XML RAW

将表转换来成分名称是row,属性名为列名恐怕列的小名。
示例9:将Student表转换为XML格式(FOWrangler XML RAW)
Student表的多少如图所示
图片 7
实施语句:

SELECT * FROM Student FOR XML RAW;

查询结果如图所示
图片 8
图片 9

1.3.1.创建DML触发器

FOR XML AUTO

应用表名称作为成分名称,使用列名称作为品质名称,SELECT关键字后边列的相继用于XML文书档案的层系。
示例10:将Student表转换为XML格式(FO奥迪Q5 XML AUTO)
履行语句:

SELECT * FROM Student FOR XML AUTO;

查询结果如图所示
图片 10
图片 11

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表中的数据如图所示
图片 12
游标示例二:对student表中还未分班的学习者开始展览分班
Student表中的数据如图所示
图片 13
其中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表的数目
图片 14
Class_student表的数额
图片 15
至此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和第1段用于给学员分班的代码中对@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
图片 16
施行存款和储蓄进程

EXEC dbo.student_division

结果如图所示
Student表的数目(分班状态都为true了)
图片 17
Class_student表的数码
图片 18

FOR XML EXPLICIT

允许用户显式地定义XML树的造型,不受AUTO形式中的种种限制。不可能将FO翼虎 XML
EXPLICIT直接用在SELECT子句中。
示例11:将xmlTest表转换为XML格式(FO本田UR-V XML EXPLICIT)
XmlTest表的多少如图所示
图片 19

SELECT DISTINCT 1 AS TAG,--指定顶级层级序号1
NULL AS PARENT,--该层级没有父级
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'--设置所有层级元素和属性命名,暂时不对这些元素赋值
--例如在“学生信息!3!总分!Element”格式中,学生信息是元素名,3表示该元素所处层级,总分表示属性名
--Element指出生成以属性单独为一行的XML格式
UNION ALL--层级之间用UNION ALL相连
SELECT DISTINCT 2 AS TAG,--指定二级层级序号2
1 AS PARENT,--父级序号是序号为1的层级
NULL,--在层级的代码中已列出了所有层级元素和属性命名,因此这里给元素和属性做赋值。这句语句对应层级代码中“NULL AS '班级信息!1!'”,说明我希望该元素作为独立成行的标签,没有赋值。
班级,--对层级中的“NULL AS '班级信息!2!班级'”赋值,将xmlTest表中的班级赋值给属性班级
班级类型,--对层级中的“NULL AS '班级信息!2!班级类型'”赋值,将xmlTest表中的班级赋值给属性班级类型
班主任,--同上
NULL,--这句语句开始对应的是层级的属性,因此在层级的代码中不做赋值,在下面层级的代码中做赋值
NULL,
NULL,
NULL
FROM xmlTest--指出上面赋值的数据源来自于xmlTest表
UNION ALL--各个层级之间用UNION ALL连接
SELECT 3 AS TAG,--指定3级层级序号3
2 AS PARENT,--父级是序号为2的层级
NULL,--对应层级的”NULL AS '班级信息!1!'“语句,不希望它有值,所以不做赋值
NULL,--这三个NULL对应层级的各个属性,在层级的代码中已经做过赋值,因此在这里不做赋值
NULL,
NULL,
学号,--对应层级1代码中的层级3属性,在层级代码3中进行赋值
学生姓名,
性别,
年级总分
FROM xmlTest
FOR XML EXPLICIT;--将上述查询转换为XML,不能漏掉,否则结果会以表格形式显示

询问结果如图所示
图片 20
图片 21
在结果图中我们发现,红框中三个班级新闻列在同步,而具有学员都列在高壹3班下,那不是我们想要的结果,我们盼望每一个班级对应本身的学员。那么哪些缓解此类题材吧,那关系到排序。

注:假诺层级中有多少个数据完全重复,能够在该层级对应的代码前加DISTINCT关键字去除重复成分。

首先删除代码行末的FO奥迪Q7 XML
EXPLICIT语句,仅仅执行剩下的有个别,使结果以表格情势显示,那么结果如下
图片 22
这些表格每行的次第也意味着了该表格转化为XML文书档案后内容显示顺序。图中层级2(TAG=贰)的几行,地点都在协同,那也正是干吗层级三的拥有数据都在高13班上面了。大家须求对表格每行的一一举办调整,使学生所在行根据xmlTest表中的数据逻辑分散在班级行之下。可是根据地点的表格发现,不管依据什么字段排序,都不大概高达效果。
科学代码如下

SELECT DISTINCT 1 AS TAG,
NULL AS PARENT,
NULL AS '班级信息!1!',
NULL AS '班级信息!2!班级',
NULL AS '班级信息!2!班级类型',
NULL AS '班级信息!2!班主任',
NULL AS '学生信息!3!学号!Element',
NULL AS '学生信息!3!学生姓名!Element',
NULL AS '学生信息!3!性别!Element',
NULL AS '学生信息!3!总分!Element'
UNION ALL
SELECT DISTINCT 2 AS TAG,
1 AS PARENT,
NULL,
班级,
班级类型,
班主任,
NULL,
NULL,
NULL,
NULL
FROM xmlTest
UNION ALL
SELECT 3 AS TAG,
2 AS PARENT,
NULL,
班级,
班级类型,
班主任,
学号,
学生姓名,
性别,
年级总分
FROM xmlTest
ORDER BY [班级信息!2!班级],[学生信息!3!学号!Element]
FOR XML EXPLICIT;

对待第二次代码,大家发现上面的代码不止在行末对数据按成分属性实行了排序,还在赋值的代码中装有变更。在层级一代码中全然未有更改,因为层级一的代码功效是安装XML格式的,对数码排序未有影响。在底下多少个层级的赋值部分,各样层级的代码中都对上边多少个层级的因素重复赋值,那样做使结果的报表中不再有那么多属性值是NULL,能够一本万利排序。最终再根据元素[班级新闻!2!班级]和[学生消息!3!学号!Element]排序。让我们看看结果什么。
运作方面包车型客车代码,但不运维FO卡宴 XML
EXPLICIT语句,看看表格中多少内容和行顺序是还是不是改变
图片 23
如图所示,发现行反革命数据和学员数量的顺序呈现正确。运转具有代码获得XML文书档案,结果如图所示
图片 24
鉴于XML文书档案内容过长,不贴图了,直接复制全数XML内容展现一下。

<班级信息>
  <班级信息 班级="高一1班" 班级类型="创新班" 班主任="李玉虎">
    <学生信息>
      <学号>20180101</学号>
      <学生姓名>李华</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180103</学号>
      <学生姓名>孙丽</学生姓名>
      <性别>女</性别>
      <总分>3.390000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180108</学号>
      <学生姓名>吴伟</学生姓名>
      <性别>男</性别>
      <总分>5.280000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一2班" 班级类型="重点班" 班主任="姜杰">
    <学生信息>
      <学号>20180102</学号>
      <学生姓名>张三</学生姓名>
      <性别>男</性别>
      <总分>6.270000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180104</学号>
      <学生姓名>袁康</学生姓名>
      <性别>男</性别>
      <总分>4.820000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180106</学号>
      <学生姓名>赵四</学生姓名>
      <性别>男</性别>
      <总分>5.680000000000000e+002</总分>
    </学生信息>
  </班级信息>
  <班级信息 班级="高一3班" 班级类型="提高班" 班主任="师从光">
    <学生信息>
      <学号>20180105</学号>
      <学生姓名>王婷</学生姓名>
      <性别>女</性别>
      <总分>7.610000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180107</学号>
      <学生姓名>周其</学生姓名>
      <性别>女</性别>
      <总分>3.480000000000000e+002</总分>
    </学生信息>
    <学生信息>
      <学号>20180109</学号>
      <学生姓名>甄诚</学生姓名>
      <性别>女</性别>
      <总分>7.020000000000000e+002</总分>
    </学生信息>
  </班级信息>
</班级信息>

将方面包车型大巴结果相比较一下原始xmlTest表,看看种种班级和它下属学生的层级关系是不是有误。

注:写FORAV肆 XML
EXPLICIT代码要注意,层级一的代码中先安装层级结构,不要先急着赋值。在上面层级的代码中对层级第11中学的代码进行赋值,最棒重复赋值,不然就会现出文中的排序难题。若是有个别层级出现重复数据,在该层级的代码前加DISTINCT关键字。化解排序难点最佳的不贰诀窍是对各类层级的习性重复赋值并在结尾用O奥迪Q3DER
BY按层级属性排序。

有心人调查地点的XML文书档案,发现总分属性的值是个float类型,要把它转换到int,只须求把层级三中对总分的赋值代码改成CAST(年级总分
AS int)
图片 25

1.3.1.2.DELETE触发器

当针对对象数据库运维DELETE话语时就会激活DELETE触发器。用户直接运行DELETE说话和选取DELETE触发器又有所不一致,当激活DELETE触发器后,从受触发器影响的表中删除的行会被停放在2个奇特的一时半刻表——DELETED表中。DELETED表还同意引用由开首化DELETE语句发生的日记数据。
DELETE触发器被激活时,必要思量以下几点

  • 当某行被添加到DELETED表中时就不设有于数据库表,由此数据库表和DELETED表不也许存在相同行。
  • 系统活动创造DELETED表时,空间从内部存储器中分配。DELETED表被储存在高速缓存中。
  • DELETE操作定义的触发器并不履行TRUNCATE
    TABLE
    言语,原因在于日志不记录TRUNCATE TABLE语句。

示例4:为student表定义1个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表的多少如图所示
图片 26
Class_student表的多寡如图所示
图片 27
实行下列语句

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

student表来看,唯有入学编号为2018001120180012的学童战绩被去除。该操作激活了delete_student触发器
Class_student表的数据如图所示
图片 28
入学编号为2018001120180012的学习者分班消息已经从class_student表中机动删除。

FOR XML PATH

PATH形式提供了1种较简单的措施来混合成分及性能。在PATH格局中,列名或列小名被看成XPATH表明式来处理,那么些表明式钦命了怎么着将值映射到XML中。私下认可意况下,PATH情势为每一样自动生成

1.3.1.3.UPDATE触发器

当针对对象数据库运转UPDATE讲话时就会激活UPDATE触发器。对UPDATE触发器来说,临时表INSERTEDDELETED1如既往有效。UPDATE触发器被激活时,原始行被移入DELETED表中,更新行被移入到INSERTED表中。触发器检查DELETED表和INSERTED表以及被更新的表,来明确是或不是更新了多行和怎么执行触发器动作。
Student表的数额如图所示
图片 29
Class_student表的多少如图所示
图片 30
示例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表的多寡如图所示
图片 31

注: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表的数目如图所示
图片 32
Class_student表的数额如图所示
图片 33
施行下列语句新建触发器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表的数额如图所示
图片 34
Class_student表的多寡如图所示
图片 35
我们再来验证update_stu_no_batch触发器争执异单行stu_no数量是还是不是有效。将student表class_student表的多寡改回原来的指南,然后实施下列语句

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

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

注:在将表数据改成原来的规范时,间接在编辑前200行中操作依旧用T-SQL说话操作,对student表数据操作,不成事的话要思索受键和封锁的震慑,对class_student表数据操作,不成事的话要考虑受触发器影响。

尚无称谓的列

上边介绍1种简易的FO牧马人 XML PATH应用措施

SELECT 2+3 FOR XML PATH;--将2+3的值转换成xml格式

询问结果如图所示
图片 37

注:如若提供了空字符串FOPAJERO XML PATH(‘’)则不会扭转任何因素。

SELECT 2+3 FOR XML PATH('');--将2+3的值转换成xml格式并去掉<row>

询问结果如图所示
图片 38
示例12:利用xmlTest表和mainTeacher表查询出xmlTest表中成绩>=700分的学员的班主管音信和学生消息,并转载成XML格式
XmlTest表数据如下图所示
图片 39
MainTeacher表数据如下图所示
图片 40
实践上边包车型地铁口舌

SELECT xmlTest.学号 AS '学生信息/@学号',--@符号表示该名称为属性名,斜杠表示子层级
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result');--将根目录名改为result

询问结果如下所示

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>

1.3.1.4.INSTEAD OF触发器

INSTEAD
OF
触发器能够钦赐执行触发器,而不是推行触发SQL言辞,从而屏蔽原来的SQL讲话,而转向执行触发器内部的言辞。每种表或然视图只好有一个INSTEAD
OF
触发器。INSTEAD
OF
触发器的风味是,能够使作为触发条件的SQL语句不执行。
Membership表的数目如图所示
图片 41
Call_slip表的数额如图所示
图片 42
示例7:对LibraryManagement数据Curry的membership表写1个防删除触发器,尚有借书未还的读者不恐怕被剔除
履行下列语句成立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'

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

结果如图所示
图片 46
Membership表的数码如图所示
图片 47
示例9:对LibraryManagement数据Curry的call_slip表写八个防超借触发器,2个读者的未还图书最两只好有5本,超出不能够再借(那里依旧针对批量甩卖多少创造触发器)
Call_slip表的多寡如图所示
图片 48
实践下列语句创造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','未归还')

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

TYPE命令

SQL Server帮助TYPE命令将FO中华V XML的询问结果作为XML数据类型重临。
示例13:还是是上面包车型大巴例子,将查询结果作为XML数据类型重返。

CREATE TABLE xmlType(xml_col XML);
--首先创建一个表xmlType,只有一列xml数据类型的xml_col
INSERT INTO xmlType
SELECT(--将上面的查询语句全部复制到括号中,末尾加上TYPE,表示将XML文档作为xml数据类型,并插入到表xmlType中
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE
);
SELECT * FROM xmlType;--查询xmlType表

查询结果如图所示
图片 51
双击打开查看XML

<result>
  <学生信息 学号="20180105" 姓名="王婷" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>
<result>
  <学生信息 学号="20180109" 姓名="甄诚" 班级="高一3班">
    <班主任信息>
      <姓名>师从光</姓名>
      <教师编号>83928182</教师编号>
      <性别>男</性别>
      <年龄>28</年龄>
      <联系电话>15963002120</联系电话>
    </班主任信息>
  </学生信息>
</result>

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

结果如图所示
图片 52
图片 53

注:EVENTDATA()可在触发器内部使用,再次回到有关数据库和服务器事件的信息,以XML格式重回。唯有一贯在DDL或登录触发器内部引用EVENTDATA时,EVENTDATA才会回到数据。假若EVENTDATA由别的例程调用(尽管这一个例程由DDL或登录触发器举行调用),将回到
NULL

FO奥德赛 XML的嵌套查询

示例14:在演示1贰的查询结果中查询班老董联系电话

SELECT (
SELECT xmlTest.学号 AS '学生信息/@学号',
xmlTest.学生姓名 AS '学生信息/@姓名',
xmlTest.班级 AS '学生信息/@班级',
mainTeacher.姓名 AS '学生信息/班主任信息/姓名',
mainTeacher.教师编号 AS '学生信息/班主任信息/教师编号',
mainTeacher.性别 AS '学生信息/班主任信息/性别',
mainTeacher.年龄 AS '学生信息/班主任信息/年龄',
mainTeacher.联系电话 AS '学生信息/班主任信息/联系电话'
FROM xmlTest,mainTeacher
WHERE xmlTest.年级总分>=700
AND xmlTest.班主任=mainTeacher.姓名
FOR XML PATH('result'),TYPE).query('result/学生信息/班主任信息/联系电话') AS '优秀教师联系方式';

SELECT里面依旧沿用了演示一3中被套用的代码,外面用了query方法,查询结果如下图所示
图片 54

<联系电话>15963002120</联系电话>
<联系电话>15963002120</联系电话>

一.三.叁.嵌套触发器

XML索引

鉴于XML数据类型最大可存款和储蓄二GB的数据,因而要求创设XML索引来优化查询品质。

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

若果贰个触发器在实施操作时引发了另2个触发器,而以此触发器又引发了下一个触发器,那么这么些触发器正是嵌套触发器。嵌套触发器在安装时就被启用,不过足以选用sp_configure积存进度禁止使用和重复启用嵌套。
DML触发器和DDL触发器最多能够嵌套3二层,能够透过nested
triggers
来安顿是或不是足以嵌套AFTER触发器,不过无论此设置哪些都能够嵌套INSTEAD
OF
触发器。如若嵌套触发器进入了无与伦比循环,该触发器将被截至,并且回滚整个事情。嵌套触发器具有多样用场,比如保留前一个触发器所影响的行的副本。
采取嵌套触发器时应该注意以下几点:

  • 暗中同意意况下,嵌套触发器配置选项开启。
  • 在同1个触发器事务中,叁个触发器不会被触发四回,触发器不会调用他协调来响应触发器中对同1个表的第一回革新
  • 出于触发器是3个事务,1旦嵌套中别的1层的触发器出现错误,将回滚整个业务。

示例11:有teacher_course表(教授所教学程表),course表(课程表)和course_selection表(学生选课表),写2个嵌套触发器,完毕课程撤除后,删除教授所教师程表中关于该科指标笔录,而老师所教学程表中该课程的记录被注销,导致该学科的学习者选课记录也做相应裁撤。
实践下列语句

--创建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表中的数据如图所示
图片 55
Teacher_course表中的数据如图所示
图片 56
Course_selection**表中的数据如图所示
图片 57
以课程00一三为例,执行下列语句

DELETE FROM course WHERE course_id='0013'

Course表的多少如图所示
图片 58
Teacher_course表的数据如图所示
图片 59
Course_selection表的数量如图所示
图片 60
怀有关于00一三科指标数额都被删去。嵌套触发器有效。

注:在触发器teacher_course_delete_batch中,我额外出席了二个论断,当teacher_course表中还有老师在教学那门科目时,全数关于那门课程的学生选课音讯都满不在乎删除。那样做在嵌套触发器里是多余的,删除1门科目,必然会删除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表的数据如图所示
图片 61
Teacher_course表的数目如图所示
图片 62
Course_selection表的数码如图所示
图片 63
以删除001二号教授路易为例,0012号助教教学00一3号课程,且teacher_course表中并无任何导师授课00一三号课程,遵照逻辑要去除teacher_course表中001贰号教师的所教课程记录和course_selection表中具备00一叁号课程的选课记录。执行下列语句

DELETE FROM teacher WHERE teacher_id='0012'

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

DELETE FROM teacher WHERE teacher_id='0011'
GO

结果如图所示
图片 67
Teacher表的数据如图所示
图片 68
Teacher_course表的数码如图所示
图片 69
Course_selection表的数额如图所示
图片 70

主XML索引

主XML索引对XML列中XML实例内的有着标记,值和路径实行索引。成立主XML索引时,相应XML列所在的表必须对该表的主键创造了聚集索引。

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

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

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

结果如图所示
图片 72

辅助XML索引

为了增加主XML索引的性质,能够成立协助XML索引。只有成立了主XML索引后才能创建匡助XML索引。辅助XML索引分三种:PATH,VALUES和PROPEMuranoTY援助XML索引。

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

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

为表中有些列创设索引,供给该列是XML数据类型。

ALTER TABLE Student
ADD xml_test XML;--对Student表添加一个XML数据类型字段xml_test
--对Student表的xml_test字段创建主XML索引,命名为学生信息表
CREATE PRIMARY XML INDEX 学生信息表
ON Student(xml_test)
GO
--对Student表的xml_test字段创建PATH辅助XML索引,记得写上主索引名
CREATE XML INDEX 辅助学生信息表
ON Student(xml_test)
USING XML INDEX 学生信息表 FOR PATH
GO

注:扶助索引的命名无法与主索引相同。

一.三.4.递归触发器

修改和删除索引(ALTEENVISION INDEX 和 DROP INDEX)
ALTER INDEX ALL ON Student--重建所有索引
REBUILD WITH(FILLFACTOR=80,SORT_IN_TEMPDB=ON,STATISTICS_NORECOMPUTE=ON);
--删除索引
DROP INDEX 学生信息表 ON Student
GO

注:删除主索引,与其连带的持有帮忙索引也会被删去。因而地点语句中剔除学生消息表索引后,帮助学生消息表索引也被剔除了。

1.三.4.一.递归触发器

触发器被激活,更改了表中数量,那种转移又激活了它本人,那种触发器被誉为递归触发器。数据库创制时默许递归触发器禁止使用。但足以行使ALTER
DATABASE
采用来启用它。递归触发器启用的先决条件是嵌套触发器必须是启用情形,如若嵌套触发器禁止使用,不管递归触发器的安插是什么都将被剥夺。而在递归触发器中,inserted表和deleted表都只包蕴被上3遍触发器影响的行数据。
递归触发器有以下二种不相同品种(那边未有适合的选择示范可举,先不举例了)

OPENXML函数

OPENXML是多个行集函数,用于检索XML文书档案。在试用OPENXML函数此前,一定要先用系统存款和储蓄进度sp_xml_preparedocument浅析文档,该存款和储蓄进度在分析完XML文书档案后会重回一个句柄,使用OPENXML检索文档时要将该句柄作为参数字传送给OPENXML。
示例15

--定义两个变量@Student和@StudentInfo
DECLARE @Student int
DECLARE @StudentInfo xml
--使用SET为@StudentInfo赋值
SET @StudentInfo='
<row>
<姓名>祝红涛</姓名>
<班级编号>2019382910</班级编号>
<成绩>89</成绩>
<籍贯>沈阳</籍贯>
</row>
'
--使用系统存储过程sp_xml_preparedocument分析由@Student变量表示的XML文档,将分析得到的句柄赋值给@Student变量
EXEC sp_xml_preparedocument @Student OUTPUT,@StudentInfo
--在SELECT语句中使用OPENXML函数返回行集中的指定数据
SELECT * FROM OPENXML(@Student,'/row',2)
WITH(
姓名 varchar(8),
班级编号 varchar(10),
成绩 int,
籍贯 varchar(20)
);

结果如图所示
图片 73
在上述语句中,sp_xml_preparedocument存款和储蓄进度语句用了二个参数,在那之中@Student是3个int型变量,该存款和储蓄进程会将句柄存款和储蓄在@Student变量中作为结果数据,@StudentInfo是三个XML类型的变量,存款和储蓄了就要实行分析的XML文书档案。
OPENXML函数的说话中,使用了1个参数,在那之中@Student代表已经因此sp_xml_preparedocument存款和储蓄进度分析的文书档案的句柄,’/row’使用XPath情势提供了二个路径,代表要回到XML文书档案中该路线下的数码行,二是八个可选数据参数,表示将这几个数量行以元素为大旨映射。

一.3.肆.二.直接递归

直白递归触发器是指任何递归进程唯有它自个儿三个触发器的到场。本人激活了祥和。

一.3.4.三.直接递归

直接递归触发器是指任何递归进程有多少个触发器参预,例如A激活B,B激活C,C激活A。能够当做是递归和嵌套的咬合。
选拔递归触发器时索要专注以下几点:
递归触发器很复杂,供给通过有系统的筹划和百科测试
在任意点的多少修改都会激活递归触发器。只可以按触发器被激活的特定顺序更新表。
装有触发器一起组成二个大事务,任意触发器的任意地方上的ROLLBACK言语都将撤消全体数据的输入,全数数据均被擦除。
触发器最三只好递归1六层,一旦有第1多少个触发器参与进去,结果与ROLLBACK命令①样,全体数据都将被擦除

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

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

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

发表评论

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

网站地图xml地图