亚洲免费av电影一区二区三区,日韩爱爱视频,51精品视频一区二区三区,91视频爱爱,日韩欧美在线播放视频,中文字幕少妇AV,亚洲电影中文字幕,久久久久亚洲av成人网址,久久综合视频网站,国产在线不卡免费播放

        ?

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

        2017-06-05 09:35:37鄧景順
        軟件 2017年4期
        關(guān)鍵詞:學(xué)號(hào)觸發(fā)器數(shù)據(jù)表

        鄧景順

        ?

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

        鄧景順

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

        使用SQL Server觸發(fā)器可以級(jí)聯(lián)更新數(shù)據(jù)庫中相關(guān)表記錄,現(xiàn)有文獻(xiàn)中,對(duì)SQL Server觸發(fā)器及應(yīng)用案例的描述,多數(shù)只針對(duì)數(shù)據(jù)表中一個(gè)記錄更新所激發(fā)的觸發(fā)器,不能適于多行記錄更新激發(fā)的觸發(fā)器。在SQL Server觸發(fā)器的更新語句中使用CTE,可以減少中間數(shù)據(jù)和臨時(shí)數(shù)據(jù)表的存儲(chǔ)管理,設(shè)計(jì)更簡練,執(zhí)行效率更高。本文通過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語句使用時(shí)都可以建立一個(gè)CTE。CTE相當(dāng)于一個(gè)臨時(shí)表,它的生命周期在該批處理語句執(zhí)行完后就結(jié)束。

        CTE語法格式:

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

        AS (CTE查詢定義)

        SELECT|INSERT|DELETE|UPDATE

        其中UPDATE的語法格式為:

        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ù)庫中的所有相關(guān)表,能更有效地保證數(shù)據(jù)完整性[2-3]。

        多行記錄更新是指一個(gè)更新語句或操作,向數(shù)據(jù)表中插入、刪除或更新多個(gè)記錄,現(xiàn)有文獻(xiàn)的觸發(fā)器設(shè)計(jì)多針對(duì)一個(gè)記錄更新設(shè)計(jì),適于多個(gè)記錄更新的觸發(fā)器設(shè)計(jì)案例很少。將CTE用于觸發(fā)器的數(shù)據(jù)更新語句中,利用CTE的數(shù)據(jù)集,進(jìn)行中間數(shù)據(jù)的處理,語句更加清晰簡練,設(shè)計(jì)過程更加方便,觸發(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和成績表CJB,數(shù)據(jù)庫結(jié)構(gòu)及數(shù)據(jù)表間相互關(guān)系如圖1[6-8]。

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

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

        學(xué)生表中每個(gè)學(xué)生的總學(xué)分由學(xué)生所修所有課程的成績和學(xué)分確定,課程成績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)成績表中添加刪除部分學(xué)生選課成績、修改部分學(xué)生課程成績時(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ù)庫

        初始化學(xué)生表中每個(gè)學(xué)生的總學(xué)分和專業(yè)表中各專業(yè)的人數(shù),經(jīng)過初始化后的數(shù)據(jù)庫是完整的,在以后的觸發(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 成績>=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 成績表CJB上的觸發(fā)器設(shè)計(jì)

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

        (1)成績表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 成績>=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)成績表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 成績>=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)成績表CJB上的修改成績觸發(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.成績>=60

        AND deleted.成績<60 THEN 1

        WHEN inserted.成績<60

        AND deleted.成績>=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 成績>=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é)語

        在觸發(fā)器設(shè)計(jì)中,針對(duì)多行記錄更新的觸發(fā)事件,當(dāng)然也適于一個(gè)記錄的更新,適用性更強(qiáng),結(jié)合約束和規(guī)則的使用,能更好的保證數(shù)據(jù)完整性;在數(shù)據(jù)更新語句UPDATE中使用CTE,不需要設(shè)置臨時(shí)數(shù)據(jù)表,簡化了中間數(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ù)庫中觸發(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ù)庫系統(tǒng)的選擇策略[J]. 軟件, 2014, 35(3): 192-193

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

        [7] 曾萍, 韋杰. 數(shù)據(jù)倉庫技術(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ù)庫的查詢優(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ù)庫系統(tǒng)課程教學(xué)體系研究與實(shí)施”(J2013072);山西大同大學(xué)科學(xué)研究項(xiàng)目“粒子群智能優(yōu)化算法研究”(2016K1)

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

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

        猜你喜歡
        學(xué)號(hào)觸發(fā)器數(shù)據(jù)表
        湖北省新冠肺炎疫情數(shù)據(jù)表
        黨員生活(2020年2期)2020-04-17 09:56:30
        我們來打牌
        基于列控工程數(shù)據(jù)表建立線路拓?fù)潢P(guān)系的研究
        嘗試親歷的過程,感受探究的快樂
        主從JK觸發(fā)器邏輯功能分析
        電子世界(2017年22期)2017-12-02 03:03:45
        使用觸發(fā)器,強(qiáng)化安全性
        學(xué)生學(xué)號(hào)的妙用
        圖表
        與56號(hào)說再見
        基于VSL的動(dòng)態(tài)數(shù)據(jù)表應(yīng)用研究
        河南科技(2014年24期)2014-02-27 14:19:25
        亚洲天码一区二区三区| 福利一区在线观看| 97色综合| 国产少妇高潮在线视频| 亚洲av综合色区无码专区桃色| 欧美成人午夜精品久久久 | 人妻少妇中文字幕乱码| 久久久久国色av∨免费看| 又色又爽又黄的视频网站| 青青草好吊色在线观看| 成人影院yy111111在线| 暖暖免费 高清 日本社区在线观看| 精品人妻av区乱码| 亚洲中文字幕精品视频| 国产肉体xxxx裸体137大胆| 免费人成毛片乱码| 国产精品一区二区三区蜜臀| 亚洲色拍拍噜噜噜最新网站| 亚洲韩日av中文字幕| 色88久久久久高潮综合影院| 色爱区综合五月激情| 久久久高清免费视频| 国产三级精品三级在线专区2| 五月丁香综合激情六月久久| 亚洲av无码成人yellow| 精品国产一区二区三区久久狼| 45岁妇女草逼视频播放| 99久久久无码国产精品6| 亚洲色AV性色在线观看| 亚洲国产精品久久性色av| 人妻激情偷乱视频一区二区三区| 开心婷婷五月激情综合社区| 亚洲AV专区一专区二专区三| 蜜桃av人妻精品一区二区三区| 最新亚洲人成网站在线观看| 亚洲国产麻豆综合一区| 老司机在线免费视频亚洲| 免费乱理伦片在线观看| 亚洲爆乳无码专区| 亚洲一区二区视频蜜桃| 爽爽影院免费观看|