基于CTE设计的多行记录更新触发器应用研究

邓景顺



基于CTE设计的多行记录更新触发器应用研究

邓景顺

(山西大同大学数学与计算机学院，山西大同 037009)

使用SQL Server触发器可以级联更新数据库中相关表记录，现有文献中，对SQL Server触发器及应用案例的描述，多数只针对数据表中一个记录更新所激发的触发器，不能适于多行记录更新激发的触发器。在SQL Server触发器的更新语句中使用CTE，可以减少中间数据和临时数据表的存储管理，设计更简练，执行效率更高。本文通过SQLServer开发实例，改进算法，使用CTE，设计了针对多行记录更新的DML触发器，级联更新相关数据表，实现数据完整性，适用性和实用性较强。

SQL Server；触发器；多行记录更新；CTE；完整性

0 引言

CTE（Common Table Expression 公用表表达式）是从SQL Server2005开始使用的一项功能。CTE用于存储一个临时的结果集，在SELECT、INSERT、DELETE、UPDATE语句使用时都可以建立一个CTE。CTE相当于一个临时表，它的生命周期在该批处理语句执行完后就结束。

CTE语法格式：

WITH <公用表表达式>[(列名[,…])]

AS (CTE查询定义)

SELECT|INSERT|DELETE|UPDATE

其中UPDATE的语法格式为：

UPDATE 表名SET列名=表达式[，…]

[FROM 数据集[，…]][ WHERE 查询条件]

FROM子句中的数据集可以使用公用表表达式[1]。

结合约束和规则，使用触发器可以实现复杂的数据业务，执行复杂的数据约束，级联修改数据库中的所有相关表，能更有效地保证数据完整性[2-3]。

多行记录更新是指一个更新语句或操作，向数据表中插入、删除或更新多个记录，现有文献的触发器设计多针对一个记录更新设计，适于多个记录更新的触发器设计案例很少。将CTE用于触发器的数据更新语句中，利用CTE的数据集，进行中间数据的处理，语句更加清晰简练，设计过程更加方便，触发器执行效率更高。本文重点在于，在SQLServer开发实例中，使用CTE设计DML（Data Manipulation Language）触发器，进行数据表记录的级联更新，实现多行记录更新的数据完整性[4-5]。

1 模型描述

本文涉及一个教学管理系统，其中数据表有学生表XSB，专业表ZYB，课程表KCB和成绩表CJB，数据库结构及数据表间相互关系如图1[6-8]。

图1 数据库关系图

专业表中每个专业的人数根据学生表中学生的专业统计确定。

学生表中每个学生的总学分由学生所修所有课程的成绩和学分确定，课程成绩60分以上才能取得该课程学分。

系统要求，设计DML触发器，实现以下功能：

当学生表中添加删除部分学生记录、修改部分学生专业时，能自动调整专业表中专业人数。

当成绩表中添加删除部分学生选课成绩、修改部分学生课程成绩时，能自动调整学生表中涉及到的学生总学分。

当课程表中某些课程的学分发生变化时，能自动调整学生表中选修课程学生的总学分[9-10]。

2 初始化数据库

初始化学生表中每个学生的总学分和专业表中各专业的人数，经过初始化后的数据库是完整的，在以后的触发器设计中，对总学分和专业人数，采用数据增量的方式进行数据更新。

初始化学生表XSB中每个学生的总学分

UPDATE XSB SET 总学分=0;

WITH NEWXF(学号, 总学分)

AS

(SELECT 学号,总学分=SUM(学分)

FROM CJB JOIN KCB

ON KCB.课程号=CJB.课程号

WHERE 成绩>=60

GROUP BY 学号)

UPDATE XSB SET 总学分=NEWXF.总学分

FROM NEWXF

WHERE XSB.学号=NEWXF.学号

初始化专业表ZYB各专业人数，

UPDATE ZYB SET 人数=0;

WITH NEWZY (专业号, 人数)

AS

(SELECT 专业号, 人数=COUNT(专业号)

FROM XSB

GROUP BY 专业号)

UPDATE ZYB SET 人数=NEWZY.人数

FROM NEWZY

WHERE ZYB.专业号=NEWZY.专业号

3 触发器设计

3.1 学生表XSB上的触发器设计

建立学生表上的触发器，当学生表中添加删除部分学生、修改部分学生专业时，能自动调整专业表中专业人数。

（1）学生表XSB上的插入触发器设计

CREATE TRIGGER XSB_INSERT

ON XSB AFTER INSERT

AS

BEGIN

WITH ADDXS(专业号, 增加人数)

AS

(SELECT inserted.专业号, COUNT(专业号)

FROM inserted

GROUP BY inserted.专业号)

UPDATE ZYB SET 人数=人数+增加人数

FROM ADDXS

WHERE ZYB.专业号=ADDXS.专业号

END

（2）学生表XSB上的删除触发器设计

CREATE TRIGGER XSB_DELETE

ON XSB AFTER DELETE

AS

BEGIN

WITH DELXS(专业号, 减少人数)

AS

(SELECT deleted.专业号, COUNT(专业号)

FROM deleted

GROUP BY deleted.专业号)

UPDATE ZYB SET 人数=人数-减少人数

FROM DELXS

WHERE ZYB.专业号=DELXS.专业号

END

（3）学生表XSB上的专业更新触发器设计

CREATE TRIGGER XSB_UPDATE

ON XSB AFTER UPDATE

AS

BEGIN

WITH DELXS(专业号, 减少人数)

AS

(SELECT deleted.专业号, COUNT(专业号)

FROM deleted

GROUP BY deleted.专业号)

UPDATE ZYB SET 人数=人数-减少人数

FROM DELXS

WHERE ZYB.专业号=DELXS.专业号 ;

WITH ADDXS(专业号, 增加人数)

AS

(SELECT inserted.专业号, COUNT(专业号)

FROM inserted

GROUP BY inserted.专业号)

UPDATE ZYB SET 人数=人数+增加人数

FROM ADDXS

WHERE ZYB.专业号=ADDXS.专业号

END

3.2 成绩表CJB上的触发器设计

建立成绩表上的触发器，当成绩表中添加删除部分学生选课成绩、修改部分学生课程成绩时，能自动调整学生表中涉及到的学生总学分。

（1）成绩表CJB上的插入触发器设计

CREATE TRIGGER CJB_INSERT

ON CJB AFTER INSERT

AS

BEGIN

WITH ADDCJ(学号, 增加学分)

AS

(SELECT inserted.学号, SUM(学分)

FROM inserted INNER JOIN KCB

ON KCB.课程号=inserted.课程号

WHERE 成绩>=60

GROUP BY inserted.学号)

UPDATE XSB SET 总学分=总学分+增加学分

FROM ADDCJ

WHERE XSB.学号=ADDCJ.学号

END

（2）成绩表CJB上的删除触发器设计

CREATE TRIGGER CJB_DELETE

ON CJB AFTER DELETE

AS

BEGIN

WITH DELCJ (学号, 减少学分)

AS

(SELECT deleted.学号, SUM(学分)

FROM deleted INNER JOIN KCB

ON KCB.课程号= deleted.课程号

WHERE 成绩>=60

GROUP BY deleted.学号)

UPDATE XSB SET 总学分=总学分-减少学分

FROM DELCJ

WHERE XSB.学号=DELCJ.学号

END

（3）成绩表CJB上的修改成绩触发器设计

CREATE TRIGGER CJB_UPDATE

ON CJB AFTER UPDATE

AS

BEGIN

WITH UPDATECJ(学号, 学分改变)

AS

(SELECT inserted.学号, SUM(

CASE

WHEN inserted.成绩>=60

AND deleted.成绩<60 THEN 1

WHEN inserted.成绩<60

AND deleted.成绩>=60 THEN -1

ELSE 0

END*KCB.学分)

FROM inserted INNER JOIN deleted

ON inserted.学号=deleted.学号

AND inserted.课程号=deleted.课程号

INNER JOIN KCB

ON inserted.课程号=KCB.课程号

GROUP by inserted.学号)

UPDATE XSB SET 总学分=总学分+学分改变

FROM UPDATECJ

WHERE XSB.学号= UPDATECJ.学号

END

3.3 课程表KCB上的更新触发器设计

建立课程表上的触发器，当课程表中某些课程的学分发生变化时，能自动调整学生表中选修课程学生的总学分。

CREATE TRIGGER KCB_UPDATE

ON KCB AFTER UPDATE

AS

BEGIN

WITH CHANGEXF(学号, 学分差)

AS

(SELECT 学号, SUM(inserted.学分-deleted.学分)

FROM inserted INNER JOIN deleted

ON inserted.课程号=deleted.课程号

INNER JOIN CJB

ON inserted.课程号=CJB.课程号

WHERE 成绩>=60

GROUP BY 学号)

UPDATE XSB SET 总学分=总学分+ CHANGEXF.学分差

FROM CHANGEXF

WHERE XSB.学号=CHANGEXF.学号

END

4 结语

在触发器设计中，针对多行记录更新的触发事件，当然也适于一个记录的更新，适用性更强，结合约束和规则的使用，能更好的保证数据完整性；在数据更新语句UPDATE中使用CTE，不需要设置临时数据表，简化了中间数据和临时数据的存储和管理。本文所涉及的方法在教学和实践应用中效果较好，希望能进一步交流。

[1] 郑阿奇. SQL Server实用教程(第4版)[M]. 北京: 电子工业出版社, 2014: 65-110

[2] 刘艳春. 运用 SQL Server 开发软件参照完整性实现方法[J]. 计算机技术与发展, 2013, 23(6): 117-121

[3] 程志梅, 邱霞明, 王晓燕. SQL Server2000数据库中触发器的妙用[J]. 计算机应用与软件, 2009, 26(3): 188-189

[4] 邓景顺, 黄杰. SQL Server中多行数据更新的触发器应用研究[J]. 山西大同大学学报(自然科学版), 2010, 26(2): 5-7

[5] 姜晓洁. 探讨软件开发中文件或数据库系统的选择策略[J]. 软件, 2014, 35(3): 192-193

[6] 孔琳俊, 曹超. 浙江省高校教育信息化管理体制现状及其对策研究[J]. 软件, 2012, 33(8): 125-127

[7] 曾萍, 韦杰. 数据仓库技术在高校信息化建设中的应用研究[J]. 软件, 2014, 35(5): 108-110

[8] 陆美玲, 于俊乐. 基于B/S 模式的学生管理系统的设计[J]. 软件, 2013, 34(11): 55-56

[9] 沈黎. 触发器在教务管理系统中的应用[J]. 西南师范大学学报(自然科学版), 2013, 38(5): 88-91

[10] 徐安令. SQL Server 数据库的查询优化[J]. 软件, 2014, 35(2): 88-89

The Application Research on Multiple Records Update Trigger Based on CTE Design

DENG Jing-shun

(School of Mathematics and Computer Science, Shanxi Datong University, Datong Shanxi, 037009)

The records of related tables are cascading updated by using SQL Server trigger in the database. In the existing literature, a record in the table instead of multiple records is generally updated for the cases of SQL Server triggers and the application. The CTE is used to update statement in the SQL Server triggers, which can reduce the storage and management of intermediate data and temporary tables. It results in the more concise design and the higher execution efficiency. In this paper, DML trigger with multiple records update is designed by using development examples of SQLServer, improved algorithm and the CTE. It can cascade update related data table, guarantee data integrity and have strong applicability and practicability.

SQL Server; Trigger; The update of multiple records; CTE; Integrity

TP392

A

10.3969/j.issn.1003-6970.2017.04.006

山西省高等学校教学改革项目“基于实践能力培养的数据库系统课程教学体系研究与实施”(J2013072)；山西大同大学科学研究项目“粒子群智能优化算法研究”(2016K1)

邓景顺(1964-)，男，副教授，主要研究方向为数据库应用。

本文著录格式：邓景顺. 基于CTE设计的多行记录更新触发器应用研究[J]. 软件，2017，38（4）：32-35