SQL Server研習(xí)錄（25）——sql server觸發(fā)器、instered表和deleted表知識(shí)詳解及示例分析

• • 版權(quán)聲明
  • • 一、創(chuàng)建測(cè)試數(shù)據(jù)
    • 二、觸發(fā)器
    • • 1、基本知識(shí)
      • 2、觸發(fā)器的分類(lèi)
      • • （1）DML觸發(fā)器
        • （2）DDL觸發(fā)器
        • （3）登錄觸發(fā)器
      • 3、instered表和deleted表
    • 二、編寫(xiě)觸發(fā)器及示例
    • • 1、創(chuàng)建觸發(fā)器
      • • （1）基本語(yǔ)法
        • （2）delete觸發(fā)器示例
        • （3）update觸發(fā)器示例
        • （4）insert觸發(fā)器示例
      • 2、修改觸發(fā)器
      • 3、刪除觸發(fā)器

---

版權(quán)聲明

• 本文原創(chuàng)作者：清風(fēng)不渡
• 博客地址：https://blog.csdn.net/WXKKang

---

一、創(chuàng)建測(cè)試數(shù)據(jù)

??首先我們創(chuàng)建測(cè)試數(shù)據(jù)以幫助我們學(xué)習(xí)本篇內(nèi)容，共有兩張表：課程表（course）與學(xué)生表（student），其中學(xué)生表中的course_id字段為course的外鍵，代碼如下：

USE [demo]-- 創(chuàng)建course表 CREATE TABLE course(course_id varchar(50) PRIMARY KEY,course_name varchar(50) ) GO-- 創(chuàng)建student表 CREATE TABLE student(student_id varchar(50) PRIMARY KEY,student_name varchar(50),course_id varchar(50) FOREIGN KEY REFERENCES course(course_id) ) GO-- 插入數(shù)據(jù) INSERT INTO course (course_id,course_name) VALUES ('C001','語(yǔ)文'), ('C002','數(shù)學(xué)'), ('C003','英語(yǔ)') GOINSERT INTO student (student_id,student_name,course_id) VALUES ('S001','Lucy','C001'), ('S002','Jack','C002'), ('S003','Jane','C003'), ('S004','Jameson','C001') GO

二、觸發(fā)器

1、基本知識(shí)

??為了更好的強(qiáng)制業(yè)務(wù)規(guī)則和保證數(shù)據(jù)的完整性，sql server為我們提供了兩種機(jī)制，它們分別是約束和觸發(fā)器，本篇我們就來(lái)學(xué)習(xí)觸發(fā)器，在觸發(fā)器中，我們可以查詢(xún)其他表，也可以包含復(fù)雜的Transact-SQL語(yǔ)句，并且可以將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到嚴(yán)重錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)即自動(dòng)回滾
??說(shuō)到這里，我想到了數(shù)據(jù)庫(kù)中的另外一個(gè)東西——存儲(chǔ)過(guò)程，它們兩個(gè)真的是太像了，幾乎可以說(shuō)存儲(chǔ)過(guò)程可以做什么觸發(fā)器就可以做什么，所以我們也可以把觸發(fā)器稱(chēng)作是一種特殊的存儲(chǔ)過(guò)程，那么，既然它是特殊的存儲(chǔ)過(guò)程，那它到底特殊在哪里呢？觸發(fā)器與存儲(chǔ)過(guò)程最大的不同就是：它是與表事件（insert、delete、update）相關(guān)的存儲(chǔ)過(guò)程，它的執(zhí)行既不是由程序調(diào)用的，也不是由手工調(diào)用的，而是由事件來(lái)觸發(fā)的，這就是它的神奇之處，比如當(dāng)我們對(duì)一個(gè)表進(jìn)行操作（insert、delete、update）時(shí)就會(huì)激活它執(zhí)行，這就滿(mǎn)足了普通存儲(chǔ)過(guò)程所做不到的一些需求，是不是特別棒呢

2、觸發(fā)器的分類(lèi)

??sql server包括三種常規(guī)類(lèi)型的觸發(fā)器：DML觸發(fā)器、DDL觸發(fā)器和登錄觸發(fā)器

（1）DML觸發(fā)器

??當(dāng)數(shù)據(jù)庫(kù)中表里面的數(shù)據(jù)發(fā)生變化時(shí)，例如進(jìn)行insert、update、delete操作時(shí)，如果我們對(duì)該表創(chuàng)建了對(duì)應(yīng)的觸發(fā)器，那么對(duì)應(yīng)的觸發(fā)器在數(shù)據(jù)發(fā)生對(duì)應(yīng)變化的時(shí)候就會(huì)自動(dòng)執(zhí)行。DML觸發(fā)器的主要作用為：強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，以及擴(kuò)展sql server的約束，默認(rèn)值等。因?yàn)榧s束只能約束同一個(gè)表中的數(shù)據(jù)，而我們?cè)谟|發(fā)器中可以執(zhí)行任意sql語(yǔ)句，當(dāng)然可以將其他表中想約束的任意字段與本表中相對(duì)應(yīng)的字段聯(lián)合在一起來(lái)約束
??DML觸發(fā)器分為：
??1、after觸發(fā)器（執(zhí)行對(duì)應(yīng)語(yǔ)句之后觸發(fā)）：insert觸發(fā)器、update觸發(fā)器以及delete觸發(fā)器——只能定義在表上
??2、instead of（執(zhí)行之前觸發(fā)）：定義了instead of觸發(fā)器則表示并不執(zhí)行其定義的操作（insert、update、delete）而僅是執(zhí)行觸發(fā)器本身——可以在表上定義，也可以在視圖上定義

（2）DDL觸發(fā)器

??在sql server 2005中新增了DDL觸發(fā)器，它主要用于審核與規(guī)范對(duì)數(shù)據(jù)庫(kù)表中表、觸發(fā)器、視圖等結(jié)構(gòu)上的操作，比如在修改表、新增表、創(chuàng)建列、修改列等可以影響數(shù)據(jù)庫(kù)結(jié)構(gòu)發(fā)生變化的時(shí)候執(zhí)行的，我們主要是用它來(lái)記錄數(shù)據(jù)庫(kù)的修改過(guò)程，以及限制程序員對(duì)數(shù)據(jù)庫(kù)的修改，比如可以限制不允許刪除某些指定的表等

（3）登錄觸發(fā)器

??登錄觸發(fā)器是為了響應(yīng)Login事件而激發(fā)的存儲(chǔ)過(guò)程，與sql server示例建立用戶(hù)會(huì)話(huà)時(shí)將引發(fā)此事件，登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶(hù)會(huì)話(huà)實(shí)際建立之前激發(fā)。因此，來(lái)自觸發(fā)器內(nèi)部且通常將到達(dá)用戶(hù)的所有信息（例如錯(cuò)誤消息和來(lái)自print語(yǔ)句的消息）會(huì)傳送到sql server錯(cuò)誤日志。如果身份驗(yàn)證失敗，將不再激發(fā)登錄觸發(fā)器

3、instered表和deleted表

??觸發(fā)器有兩個(gè)特殊的表：instered表和deleted表，這兩張是邏輯表也是虛表。由系統(tǒng)在內(nèi)存中創(chuàng)建者兩張表，不會(huì)存儲(chǔ)在數(shù)據(jù)庫(kù)中。而且兩張表的都是只讀的，只能讀取數(shù)據(jù)而不能修改數(shù)據(jù)。這兩張表的結(jié)果總是與被該觸發(fā)器應(yīng)用的表的結(jié)構(gòu)相同，當(dāng)觸發(fā)器完成工作后，這兩張表就會(huì)被刪除。Inserted表的數(shù)據(jù)是插入或是修改后的數(shù)據(jù)，而deleted表的數(shù)據(jù)是更新前的或是刪除的數(shù)據(jù)，它們具體存儲(chǔ)的數(shù)據(jù)與對(duì)應(yīng)的表數(shù)據(jù)操作如下：

表操作Inserted邏輯表Deleted邏輯表

新增記錄（insert）	存放增加的記錄	無(wú)
修改記錄（update）	存放更新后的記錄	存放更新前的記錄
刪除記錄（delete）	無(wú)	存放被刪除的記錄

??為什么我們?cè)趯?duì)表中的數(shù)據(jù)執(zhí)行更新記錄的時(shí)候，instered表和deleted表中都會(huì)有數(shù)據(jù)呢？因?yàn)槲覀冊(cè)趯?duì)表中數(shù)據(jù)進(jìn)行更新的操作，實(shí)際上是先刪除這條記錄，然后在新增一條記錄，因?yàn)檫@樣，所以instered表和deleted表中都會(huì)有數(shù)據(jù)

二、編寫(xiě)觸發(fā)器及示例

??現(xiàn)在，我們就結(jié)合示例來(lái)學(xué)習(xí)一下觸發(fā)器的具體操作

1、創(chuàng)建觸發(fā)器

（1）基本語(yǔ)法

??創(chuàng)建觸發(fā)器的語(yǔ)法如下：

CREATE TRIGGER [觸發(fā)器名稱(chēng)] ON [表名稱(chēng)]FOR UPDATE -- 或DELETE、或INSERT AS --Transact-SQL(業(yè)務(wù)邏輯代碼)

（2）delete觸發(fā)器示例

??現(xiàn)在我們根據(jù)上面的測(cè)試數(shù)據(jù)來(lái)創(chuàng)建一個(gè)觸發(fā)器，已知student表中的course_id為course表的外鍵，那么我們要實(shí)現(xiàn)這樣一個(gè)需求，當(dāng)學(xué)生表（student）中沒(méi)有學(xué)生學(xué)習(xí)某一門(mén)課程的時(shí)候，我們將這門(mén)課程自動(dòng)刪除。這時(shí)，我們就需要?jiǎng)?chuàng)建一個(gè)delete觸發(fā)器，代碼如下：

-- 創(chuàng)建studnet表的觸發(fā)器 CREATE TRIGGER [dbo].[student_delete] ON [dbo].[student] FOR DELETEASDECLARE @course_id VARCHAR(50) SELECT @course_id = course_id FROM deleted IF EXISTS (SELECT 1 FROM student WHERE course_id = @course_id) BEGINPRINT 'student表中存在學(xué)習(xí)該課程的學(xué)生' END ELSE BEGINPRINT 'student表中不存在學(xué)習(xí)該課程的學(xué)生' DELETE course where course_id = @course_idPRINT 'course表中相關(guān)數(shù)據(jù)已刪除' END

??現(xiàn)在我們對(duì)此觸發(fā)器做一個(gè)測(cè)試，我們從上面的測(cè)試數(shù)據(jù)知道，Lucy和Jameson都學(xué)習(xí)了語(yǔ)文這門(mén)課程（course_id為“C001”），現(xiàn)在Lucy由于轉(zhuǎn)班去了別的班級(jí)，我們需要將此學(xué)生的信息刪除，代碼如下：

delete student where student_id = 'S001'

??執(zhí)行結(jié)果如下：

??我們知道，有兩名學(xué)生學(xué)習(xí)了語(yǔ)文，現(xiàn)在一位同學(xué)不在這個(gè)班級(jí)了，但是還有一名學(xué)生在學(xué)習(xí)語(yǔ)文這門(mén)課程，按照我們的預(yù)想，course表中課程名為語(yǔ)文的這條記錄不應(yīng)該被刪除，現(xiàn)在我們來(lái)驗(yàn)證我們的預(yù)想，執(zhí)行以下代碼：

SELECT * FROM student SELECT * FROM course

??我們可以看到Lucy的記錄刪掉了，但是語(yǔ)文這門(mén)課程還在，結(jié)果與我們預(yù)想的一樣
??后來(lái)，Jamson因?yàn)榭剂似叽味紱](méi)有考過(guò)語(yǔ)文，心灰意冷的連夜買(mǎi)站票離開(kāi)了這個(gè)城市，我們需要將他的記錄也清除掉，代碼如下：

delete student where student_id = 'S004'

??執(zhí)行結(jié)果如下：

??可以看出，課程表中相關(guān)的內(nèi)容也被刪除掉了，我們可以驗(yàn)證一下，查詢(xún)結(jié)果如下：

??確實(shí)，不僅刪除了student表中名為Jamson的記錄，也刪除了course表中課程名為語(yǔ)文的課程記錄，說(shuō)明我們的觸發(fā)器滿(mǎn)足了我們的需求

（3）update觸發(fā)器示例

??我們首先給student表中添加任課教師列（course_teacherName），然后為在表中的數(shù)據(jù)添加相關(guān)任課教師的名稱(chēng)，代碼如下：

ALTER TABLE course ADD course_teacherName varchar(50) GOUPDATE course SET course_teacherName = CASE WHEN course_id = 'C002' THEN '邢道榮' WHEN course_id = 'C003' THEN '潘鳳' END GO

??現(xiàn)在我們有這樣一個(gè)需求，就是課程的任課教師不允許修改，那么我們就需要?jiǎng)?chuàng)建一個(gè)對(duì)應(yīng)的update觸發(fā)器，代碼如下：

-- 創(chuàng)建studnet表的觸發(fā)器 CREATE TRIGGER [dbo].[course_update] ON [dbo].[course] FOR UPDATEASIF (UPDATE(course_teacherName)) BEGINraiserror('任課老師不允許修改！',16,1);rollback tran; END

??創(chuàng)建好了之后，我們?cè)賮?lái)更新任課教師的內(nèi)容，代碼如下：

UPDATE course SET course_teacherName = '許褚' WHERE course_id = 'C002'

??執(zhí)行結(jié)果如下：

??可以看到，這個(gè)觸發(fā)器滿(mǎn)足了我們的需求

（4）insert觸發(fā)器示例

??需求，我們需要在course表中添加更新時(shí)間字段（update_time），當(dāng)我們插入學(xué)生的時(shí)候，就要對(duì)相應(yīng)的該課程的時(shí)間進(jìn)行更新，代碼如下：

-- 為course表添加字段 ALTER TABLE course ADD update_time varchar(50) GO -- 創(chuàng)建student表的觸發(fā)器 CREATE TRIGGER [dbo].[course_insert] ON [dbo].[student] FOR insertASDECLARE @course_id varchar(50) SELECT @course_id = course_id FROM inserted UPDATE course SET update_time = CONVERT(VARCHAR(50),GETDATE(),21) WHERE course_id = @course_id PRINT '相應(yīng)課程的修改時(shí)間已更新'

??下面我們來(lái)測(cè)試一下，為student表中新添加一個(gè)學(xué)生，代碼如下：

INSERT INTO student VALUES ('S004','張三','C003')

??執(zhí)行結(jié)果如下：

??現(xiàn)在我們來(lái)看course表中對(duì)應(yīng)字段是否發(fā)生更新，查詢(xún)結(jié)果如下：

??可以看到，course表中對(duì)應(yīng)的字段發(fā)生了更新，滿(mǎn)足了我們的需求

2、修改觸發(fā)器

??修改觸發(fā)器我們既可以在SSMS管理工具中找到該表并展開(kāi)，然后展開(kāi)觸發(fā)器之后選中對(duì)應(yīng)的觸發(fā)器右鍵單擊修改，這樣就可以修改，或者可以使用T-SQL代碼修改，只需將關(guān)鍵字CREATE改為ALTER即可，代碼結(jié)構(gòu)：

ALTER TRIGGER [觸發(fā)器名稱(chēng)] ON [表名稱(chēng)]FOR UPDATE -- 或DELETE、或INSERT AS --Transact-SQL(業(yè)務(wù)邏輯代碼)

3、刪除觸發(fā)器

??同樣，如果需要?jiǎng)h除觸發(fā)器我們也可以在SSMS管理工具中找到該觸發(fā)器，然后右鍵刪除即可，也可以使用T-SQL代碼實(shí)現(xiàn)刪除，代碼如下：

drop trigger 觸發(fā)器名稱(chēng)

??還有像禁用、啟用等操作都可以通過(guò)SSMS管理工具去簡(jiǎn)單的實(shí)現(xiàn)，大家不妨試試喲

總結(jié)

以上是生活随笔為你收集整理的SQL Server研习录（25）——sql server触发器、instered表和deleted表知识详解及示例分析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。