基于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