基于CTE設(shè)計(jì)的多行記錄更新觸發(fā)器應(yīng)用研究

鄧景順

?

基于CTE設(shè)計(jì)的多行記錄更新觸發(fā)器應(yīng)用研究

鄧景順

(山西大同大學(xué)數(shù)學(xué)與計(jì)算機(jī)學(xué)院，山西大同 037009)

使用SQL Server觸發(fā)器可以級(jí)聯(lián)更新數(shù)據(jù)庫(kù)中相關(guān)表記錄，現(xiàn)有文獻(xiàn)中，對(duì)SQL Server觸發(fā)器及應(yīng)用案例的描述，多數(shù)只針對(duì)數(shù)據(jù)表中一個(gè)記錄更新所激發(fā)的觸發(fā)器，不能適于多行記錄更新激發(fā)的觸發(fā)器。在SQL Server觸發(fā)器的更新語(yǔ)句中使用CTE，可以減少中間數(shù)據(jù)和臨時(shí)數(shù)據(jù)表的存儲(chǔ)管理，設(shè)計(jì)更簡(jiǎn)練，執(zhí)行效率更高。本文通過(guò)SQLServer開發(fā)實(shí)例，改進(jìn)算法，使用CTE，設(shè)計(jì)了針對(duì)多行記錄更新的DML觸發(fā)器，級(jí)聯(lián)更新相關(guān)數(shù)據(jù)表，實(shí)現(xiàn)數(shù)據(jù)完整性，適用性和實(shí)用性較強(qiáng)。

SQL Server；觸發(fā)器；多行記錄更新；CTE；完整性

0 引言

CTE（Common Table Expression 公用表表達(dá)式）是從SQL Server2005開始使用的一項(xiàng)功能。CTE用于存儲(chǔ)一個(gè)臨時(shí)的結(jié)果集，在SELECT、INSERT、DELETE、UPDATE語(yǔ)句使用時(shí)都可以建立一個(gè)CTE。CTE相當(dāng)于一個(gè)臨時(shí)表，它的生命周期在該批處理語(yǔ)句執(zhí)行完后就結(jié)束。

CTE語(yǔ)法格式：

WITH <公用表表達(dá)式>[(列名[,…])]

AS (CTE查詢定義)

SELECT|INSERT|DELETE|UPDATE

其中UPDATE的語(yǔ)法格式為：

UPDATE 表名SET列名=表達(dá)式[，…]

[FROM 數(shù)據(jù)集[，…]][ WHERE 查詢條件]

FROM子句中的數(shù)據(jù)集可以使用公用表表達(dá)式[1]。

結(jié)合約束和規(guī)則，使用觸發(fā)器可以實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)業(yè)務(wù)，執(zhí)行復(fù)雜的數(shù)據(jù)約束，級(jí)聯(lián)修改數(shù)據(jù)庫(kù)中的所有相關(guān)表，能更有效地保證數(shù)據(jù)完整性[2-3]。

多行記錄更新是指一個(gè)更新語(yǔ)句或操作，向數(shù)據(jù)表中插入、刪除或更新多個(gè)記錄，現(xiàn)有文獻(xiàn)的觸發(fā)器設(shè)計(jì)多針對(duì)一個(gè)記錄更新設(shè)計(jì)，適于多個(gè)記錄更新的觸發(fā)器設(shè)計(jì)案例很少。將CTE用于觸發(fā)器的數(shù)據(jù)更新語(yǔ)句中，利用CTE的數(shù)據(jù)集，進(jìn)行中間數(shù)據(jù)的處理，語(yǔ)句更加清晰簡(jiǎn)練，設(shè)計(jì)過(guò)程更加方便，觸發(fā)器執(zhí)行效率更高。本文重點(diǎn)在于，在SQLServer開發(fā)實(shí)例中，使用CTE設(shè)計(jì)DML（Data Manipulation Language）觸發(fā)器，進(jìn)行數(shù)據(jù)表記錄的級(jí)聯(lián)更新，實(shí)現(xiàn)多行記錄更新的數(shù)據(jù)完整性[4-5]。

1 模型描述

本文涉及一個(gè)教學(xué)管理系統(tǒng)，其中數(shù)據(jù)表有學(xué)生表XSB，專業(yè)表ZYB，課程表KCB和成績(jī)表CJB，數(shù)據(jù)庫(kù)結(jié)構(gòu)及數(shù)據(jù)表間相互關(guān)系如圖1[6-8]。

圖1 數(shù)據(jù)庫(kù)關(guān)系圖

專業(yè)表中每個(gè)專業(yè)的人數(shù)根據(jù)學(xué)生表中學(xué)生的專業(yè)統(tǒng)計(jì)確定。

學(xué)生表中每個(gè)學(xué)生的總學(xué)分由學(xué)生所修所有課程的成績(jī)和學(xué)分確定，課程成績(jī)60分以上才能取得該課程學(xué)分。

系統(tǒng)要求，設(shè)計(jì)DML觸發(fā)器，實(shí)現(xiàn)以下功能：

當(dāng)學(xué)生表中添加刪除部分學(xué)生記錄、修改部分學(xué)生專業(yè)時(shí)，能自動(dòng)調(diào)整專業(yè)表中專業(yè)人數(shù)。

當(dāng)成績(jī)表中添加刪除部分學(xué)生選課成績(jī)、修改部分學(xué)生課程成績(jī)時(shí)，能自動(dòng)調(diào)整學(xué)生表中涉及到的學(xué)生總學(xué)分。

當(dāng)課程表中某些課程的學(xué)分發(fā)生變化時(shí)，能自動(dòng)調(diào)整學(xué)生表中選修課程學(xué)生的總學(xué)分[9-10]。

2 初始化數(shù)據(jù)庫(kù)

初始化學(xué)生表中每個(gè)學(xué)生的總學(xué)分和專業(yè)表中各專業(yè)的人數(shù)，經(jīng)過(guò)初始化后的數(shù)據(jù)庫(kù)是完整的，在以后的觸發(fā)器設(shè)計(jì)中，對(duì)總學(xué)分和專業(yè)人數(shù)，采用數(shù)據(jù)增量的方式進(jìn)行數(shù)據(jù)更新。

初始化學(xué)生表XSB中每個(gè)學(xué)生的總學(xué)分

UPDATE XSB SET 總學(xué)分=0;

WITH NEWXF(學(xué)號(hào), 總學(xué)分)

AS

(SELECT 學(xué)號(hào),總學(xué)分=SUM(學(xué)分)

FROM CJB JOIN KCB

ON KCB.課程號(hào)=CJB.課程號(hào)

WHERE 成績(jī)>=60

GROUP BY 學(xué)號(hào))

UPDATE XSB SET 總學(xué)分=NEWXF.總學(xué)分

FROM NEWXF

WHERE XSB.學(xué)號(hào)=NEWXF.學(xué)號(hào)

初始化專業(yè)表ZYB各專業(yè)人數(shù)，

UPDATE ZYB SET 人數(shù)=0;

WITH NEWZY (專業(yè)號(hào), 人數(shù))

AS

(SELECT 專業(yè)號(hào), 人數(shù)=COUNT(專業(yè)號(hào))

FROM XSB

GROUP BY 專業(yè)號(hào))

UPDATE ZYB SET 人數(shù)=NEWZY.人數(shù)

FROM NEWZY

WHERE ZYB.專業(yè)號(hào)=NEWZY.專業(yè)號(hào)

3 觸發(fā)器設(shè)計(jì)

3.1 學(xué)生表XSB上的觸發(fā)器設(shè)計(jì)

建立學(xué)生表上的觸發(fā)器，當(dāng)學(xué)生表中添加刪除部分學(xué)生、修改部分學(xué)生專業(yè)時(shí)，能自動(dòng)調(diào)整專業(yè)表中專業(yè)人數(shù)。

（1）學(xué)生表XSB上的插入觸發(fā)器設(shè)計(jì)

CREATE TRIGGER XSB_INSERT

ON XSB AFTER INSERT

AS

BEGIN

WITH ADDXS(專業(yè)號(hào), 增加人數(shù))

AS

(SELECT inserted.專業(yè)號(hào), COUNT(專業(yè)號(hào))

FROM inserted

GROUP BY inserted.專業(yè)號(hào))

UPDATE ZYB SET 人數(shù)=人數(shù)+增加人數(shù)

FROM ADDXS

WHERE ZYB.專業(yè)號(hào)=ADDXS.專業(yè)號(hào)

END

（2）學(xué)生表XSB上的刪除觸發(fā)器設(shè)計(jì)

CREATE TRIGGER XSB_DELETE

ON XSB AFTER DELETE

AS

BEGIN

WITH DELXS(專業(yè)號(hào), 減少人數(shù))

AS

(SELECT deleted.專業(yè)號(hào), COUNT(專業(yè)號(hào))

FROM deleted

GROUP BY deleted.專業(yè)號(hào))

UPDATE ZYB SET 人數(shù)=人數(shù)-減少人數(shù)

FROM DELXS

WHERE ZYB.專業(yè)號(hào)=DELXS.專業(yè)號(hào)

END

（3）學(xué)生表XSB上的專業(yè)更新觸發(fā)器設(shè)計(jì)

CREATE TRIGGER XSB_UPDATE

ON XSB AFTER UPDATE

AS

BEGIN

WITH DELXS(專業(yè)號(hào), 減少人數(shù))

AS

(SELECT deleted.專業(yè)號(hào), COUNT(專業(yè)號(hào))

FROM deleted

GROUP BY deleted.專業(yè)號(hào))

UPDATE ZYB SET 人數(shù)=人數(shù)-減少人數(shù)

FROM DELXS

WHERE ZYB.專業(yè)號(hào)=DELXS.專業(yè)號(hào) ;

WITH ADDXS(專業(yè)號(hào), 增加人數(shù))

AS

(SELECT inserted.專業(yè)號(hào), COUNT(專業(yè)號(hào))

FROM inserted

GROUP BY inserted.專業(yè)號(hào))

UPDATE ZYB SET 人數(shù)=人數(shù)+增加人數(shù)

FROM ADDXS

WHERE ZYB.專業(yè)號(hào)=ADDXS.專業(yè)號(hào)

END

3.2 成績(jī)表CJB上的觸發(fā)器設(shè)計(jì)

建立成績(jī)表上的觸發(fā)器，當(dāng)成績(jī)表中添加刪除部分學(xué)生選課成績(jī)、修改部分學(xué)生課程成績(jī)時(shí)，能自動(dòng)調(diào)整學(xué)生表中涉及到的學(xué)生總學(xué)分。

（1）成績(jī)表CJB上的插入觸發(fā)器設(shè)計(jì)

CREATE TRIGGER CJB_INSERT

ON CJB AFTER INSERT

AS

BEGIN

WITH ADDCJ(學(xué)號(hào), 增加學(xué)分)

AS

(SELECT inserted.學(xué)號(hào), SUM(學(xué)分)

FROM inserted INNER JOIN KCB

ON KCB.課程號(hào)=inserted.課程號(hào)

WHERE 成績(jī)>=60

GROUP BY inserted.學(xué)號(hào))

UPDATE XSB SET 總學(xué)分=總學(xué)分+增加學(xué)分

FROM ADDCJ

WHERE XSB.學(xué)號(hào)=ADDCJ.學(xué)號(hào)

END

（2）成績(jī)表CJB上的刪除觸發(fā)器設(shè)計(jì)

CREATE TRIGGER CJB_DELETE

ON CJB AFTER DELETE

AS

BEGIN

WITH DELCJ (學(xué)號(hào), 減少學(xué)分)

AS

(SELECT deleted.學(xué)號(hào), SUM(學(xué)分)

FROM deleted INNER JOIN KCB

ON KCB.課程號(hào)= deleted.課程號(hào)

WHERE 成績(jī)>=60

GROUP BY deleted.學(xué)號(hào))

UPDATE XSB SET 總學(xué)分=總學(xué)分-減少學(xué)分

FROM DELCJ

WHERE XSB.學(xué)號(hào)=DELCJ.學(xué)號(hào)

END

（3）成績(jī)表CJB上的修改成績(jī)觸發(fā)器設(shè)計(jì)

CREATE TRIGGER CJB_UPDATE

ON CJB AFTER UPDATE

AS

BEGIN

WITH UPDATECJ(學(xué)號(hào), 學(xué)分改變)

AS

(SELECT inserted.學(xué)號(hào), SUM(

CASE

WHEN inserted.成績(jī)>=60

AND deleted.成績(jī)<60 THEN 1

WHEN inserted.成績(jī)<60

AND deleted.成績(jī)>=60 THEN -1

ELSE 0

END*KCB.學(xué)分)

FROM inserted INNER JOIN deleted

ON inserted.學(xué)號(hào)=deleted.學(xué)號(hào)

AND inserted.課程號(hào)=deleted.課程號(hào)

INNER JOIN KCB

ON inserted.課程號(hào)=KCB.課程號(hào)

GROUP by inserted.學(xué)號(hào))

UPDATE XSB SET 總學(xué)分=總學(xué)分+學(xué)分改變

FROM UPDATECJ

WHERE XSB.學(xué)號(hào)= UPDATECJ.學(xué)號(hào)

END

3.3 課程表KCB上的更新觸發(fā)器設(shè)計(jì)

建立課程表上的觸發(fā)器，當(dāng)課程表中某些課程的學(xué)分發(fā)生變化時(shí)，能自動(dòng)調(diào)整學(xué)生表中選修課程學(xué)生的總學(xué)分。

CREATE TRIGGER KCB_UPDATE

ON KCB AFTER UPDATE

AS

BEGIN

WITH CHANGEXF(學(xué)號(hào), 學(xué)分差)

AS

(SELECT 學(xué)號(hào), SUM(inserted.學(xué)分-deleted.學(xué)分)

FROM inserted INNER JOIN deleted

ON inserted.課程號(hào)=deleted.課程號(hào)

INNER JOIN CJB

ON inserted.課程號(hào)=CJB.課程號(hào)

WHERE 成績(jī)>=60

GROUP BY 學(xué)號(hào))

UPDATE XSB SET 總學(xué)分=總學(xué)分+ CHANGEXF.學(xué)分差

FROM CHANGEXF

WHERE XSB.學(xué)號(hào)=CHANGEXF.學(xué)號(hào)

END

4 結(jié)語(yǔ)

在觸發(fā)器設(shè)計(jì)中，針對(duì)多行記錄更新的觸發(fā)事件，當(dāng)然也適于一個(gè)記錄的更新，適用性更強(qiáng)，結(jié)合約束和規(guī)則的使用，能更好的保證數(shù)據(jù)完整性；在數(shù)據(jù)更新語(yǔ)句UPDATE中使用CTE，不需要設(shè)置臨時(shí)數(shù)據(jù)表，簡(jiǎn)化了中間數(shù)據(jù)和臨時(shí)數(shù)據(jù)的存儲(chǔ)和管理。本文所涉及的方法在教學(xué)和實(shí)踐應(yīng)用中效果較好，希望能進(jìn)一步交流。

[1] 鄭阿奇. SQL Server實(shí)用教程(第4版)[M]. 北京: 電子工業(yè)出版社, 2014: 65-110

[2] 劉艷春. 運(yùn)用 SQL Server 開發(fā)軟件參照完整性實(shí)現(xiàn)方法[J]. 計(jì)算機(jī)技術(shù)與發(fā)展, 2013, 23(6): 117-121

[3] 程志梅, 邱霞明, 王曉燕. SQL Server2000數(shù)據(jù)庫(kù)中觸發(fā)器的妙用[J]. 計(jì)算機(jī)應(yīng)用與軟件, 2009, 26(3): 188-189

[4] 鄧景順, 黃杰. SQL Server中多行數(shù)據(jù)更新的觸發(fā)器應(yīng)用研究[J]. 山西大同大學(xué)學(xué)報(bào)(自然科學(xué)版), 2010, 26(2): 5-7

[5] 姜曉潔. 探討軟件開發(fā)中文件或數(shù)據(jù)庫(kù)系統(tǒng)的選擇策略[J]. 軟件, 2014, 35(3): 192-193

[6] 孔琳俊, 曹超. 浙江省高校教育信息化管理體制現(xiàn)狀及其對(duì)策研究[J]. 軟件, 2012, 33(8): 125-127

[7] 曾萍, 韋杰. 數(shù)據(jù)倉(cāng)庫(kù)技術(shù)在高校信息化建設(shè)中的應(yīng)用研究[J]. 軟件, 2014, 35(5): 108-110

[8] 陸美玲, 于俊樂. 基于B/S 模式的學(xué)生管理系統(tǒng)的設(shè)計(jì)[J]. 軟件, 2013, 34(11): 55-56

[9] 沈黎. 觸發(fā)器在教務(wù)管理系統(tǒng)中的應(yīng)用[J]. 西南師范大學(xué)學(xué)報(bào)(自然科學(xué)版), 2013, 38(5): 88-91

[10] 徐安令. SQL Server 數(shù)據(jù)庫(kù)的查詢優(yōu)化[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

山西省高等學(xué)校教學(xué)改革項(xiàng)目“基于實(shí)踐能力培養(yǎng)的數(shù)據(jù)庫(kù)系統(tǒng)課程教學(xué)體系研究與實(shí)施”(J2013072)；山西大同大學(xué)科學(xué)研究項(xiàng)目“粒子群智能優(yōu)化算法研究”(2016K1)

鄧景順(1964-)，男，副教授，主要研究方向?yàn)閿?shù)據(jù)庫(kù)應(yīng)用。

本文著錄格式：鄧景順. 基于CTE設(shè)計(jì)的多行記錄更新觸發(fā)器應(yīng)用研究[J]. 軟件，2017，38（4）：32-35