共計(jì) 4115 個(gè)字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。

如何分析 SQL Server 數(shù)據(jù)庫(kù)觸發(fā)器概念、原理及案例，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

概述

觸發(fā)器 (trigger) 是 SQL server   提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動(dòng)，而是由事件來(lái)觸發(fā)，當(dāng)對(duì)一個(gè)表進(jìn)行操作 ( insert，delete，update) 時(shí)就會(huì)激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。

一、觸發(fā)器的優(yōu)點(diǎn)

1. 觸發(fā)器是自動(dòng)的。當(dāng)對(duì)表中的數(shù)據(jù)做了任何修改之后立即被激活。

2. 觸發(fā)器可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表進(jìn)行層疊修改。

3. 觸發(fā)器可以強(qiáng)制限制。這些限制比用 CHECK 約束所定義的更復(fù)雜。與 CHECK 約束不同的是，觸發(fā)器可以引用其他表中的列。

二、觸發(fā)器的分類

SqlServer 包括三種常規(guī)類型的觸發(fā)器：DML 觸發(fā)器、DDL 觸發(fā)器和登錄觸發(fā)器。

1.DML(數(shù)據(jù)操作語(yǔ)言,Data Manipulation Language)觸發(fā)器

DML 觸發(fā)器是一些附加在特定表或視圖上的操作代碼，當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)操作語(yǔ)言事件時(shí)執(zhí)行這些操作。SqlServer 中的 DML 觸發(fā)器有三種：

insert 觸發(fā)器: 向表中插入數(shù)據(jù)時(shí)被觸發(fā);

delete 觸發(fā)器：從表中刪除數(shù)據(jù)時(shí)被觸發(fā);

update 觸發(fā)器：修改表中數(shù)據(jù)時(shí)被觸發(fā)。

當(dāng)遇到下列情形時(shí)，應(yīng)考慮使用 DML 觸發(fā)器：

通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改

防止惡意或者錯(cuò)誤的 insert、update 和 delete 操作，并強(qiáng)制執(zhí)行 check 約束定義的限制更為復(fù)雜的其他限制。

評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異才去措施。

2.DDL(數(shù)據(jù)定義語(yǔ)言,Data Definition Language)觸發(fā)器

DDL 觸發(fā)器是當(dāng)服務(wù)器或者數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言 (主要是以 create,drop,alter 開(kāi)頭的語(yǔ)句) 事件時(shí)被激活使用，使用 DDL 觸發(fā)器可以防止對(duì)數(shù)據(jù)架構(gòu)進(jìn)行的某些更改或記錄數(shù)據(jù)中的更改或事件操作。

3. 登錄觸發(fā)器

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

三、觸發(fā)器的工作原理

[DELETE, INSERT, UPDATE]對(duì)應(yīng)為當(dāng)執(zhí)行刪除、插入、更新三種動(dòng)作時(shí)被觸發(fā)

執(zhí)行 insert 時(shí)，獲取插入后的數(shù)據(jù)，可用 select*from inserted 讀取

執(zhí)行 delete 時(shí)，獲取刪除前的數(shù)據(jù)，可用 select*from deleted 讀取

執(zhí)行 update 時(shí)，獲取更新前的數(shù)據(jù)，可用 select*from deleted 讀取，獲取更新后數(shù)據(jù)，可用 select*from  inserted 讀取

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

1、創(chuàng)建觸發(fā)器的語(yǔ)法：

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL 語(yǔ)句  GO --with encryption  表示加密觸發(fā)器定義的 sql 文本  --delete,insert,update 指定觸發(fā)器的類型

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

-- 創(chuàng)建 insert 觸發(fā)器  create trigger trig_insert on student after insert as begin if object_id(N student_sum ,N U) is null-- 判斷 student_sum 表是否存在  create table student_sum(stuCount int default(0));-- 創(chuàng)建存儲(chǔ)學(xué)生人數(shù)的 student_sum 表  declare @stuNumber int; select @stuNumber = count(*)from student; if not exists (select * from student_sum)-- 判斷表中是否有記錄  insert into student_sum values(0); update student_sum set stuCount =@stuNumber; -- 把更新后總的學(xué)生數(shù)插入到 student_sum 表中  end -- 測(cè)試觸發(fā)器 trig_insert-- 功能是向 student 插入數(shù)據(jù)的同時(shí)級(jí)聯(lián)插入到 student_sum 表中，更新 stuCount -- 因?yàn)槭呛笥|發(fā)器，所以先插入數(shù)據(jù)后，才觸發(fā)觸發(fā)器 trig_insert; insert into student(stu_name,stu_gender,stu_age)values(呂布 , 男 ,30); select stuCount  學(xué)生總?cè)藬?shù)  from student_sum; insert into student(stu_name,stu_gender,stu_age)values(貂蟬 , 女 ,30); select stuCount  學(xué)生總?cè)藬?shù)  from student_sum; insert into student(stu_name,stu_gender,stu_age)values(曹阿瞞 , 男 ,40); select stuCount  學(xué)生總?cè)藬?shù)  from student_sum;

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

用戶執(zhí)行 delete 操作，就會(huì)激活 delete 觸發(fā)器，從而控制用戶能夠從數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)記錄，觸發(fā) delete 觸發(fā)器后，用戶刪除的記錄會(huì)被添加到 deleted 表中，原來(lái)表的相應(yīng)記錄被刪除，所以在 deleted 表中查看刪除的記錄。

-- 創(chuàng)建 delete 觸發(fā)器  create trigger trig_delete on student after delete as begin select stu_id as  已刪除的學(xué)生編號(hào),stu_name stu_gender,stu_age from deleted end; -- 執(zhí)行一一條 delete 語(yǔ)句觸發(fā) trig_delete 觸發(fā)器  delete from student where stu_id=1;

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

update 觸發(fā)器是當(dāng)用戶在指定表上執(zhí)行 update 語(yǔ)句時(shí)被調(diào)用被調(diào)用，這種類型的觸發(fā)器用來(lái)約束用戶對(duì)數(shù)據(jù)的修改。update 觸發(fā)器可以執(zhí)行兩種操作：更新前的記錄存儲(chǔ)在 deleted 表中，更新后的記錄存儲(chǔ)在 inserted 表中。

-- 創(chuàng)建 update 觸發(fā)器  create trigger trig_update on student after update as begin declare @stuCount int; select @stuCount=count(*) from student; update student_sum set stuCount =@stuCount; select stu_id as  更新前學(xué)生編號(hào),stu_name as  更新前學(xué)生姓名  from deleted select stu_id as  更新后學(xué)生編號(hào),stu_name as  更新后學(xué)生姓名  from inserted end -- 創(chuàng)建完成，執(zhí)行一條 update 語(yǔ)句觸發(fā) trig_update 觸發(fā)器  update student set stu_name= 張飛  where stu_id=2;

五、管理觸發(fā)器

1. 查看觸發(fā)器

(1). 查看數(shù)據(jù)庫(kù)中所有的觸發(fā)器

-- 查看數(shù)據(jù)庫(kù)中所有的觸發(fā)器  use  數(shù)據(jù)庫(kù)名  go select * from sysobjects where xtype= TR

sysobjects 保存著數(shù)據(jù)庫(kù)的對(duì)象，其中 xtype 為 TR 的記錄即為觸發(fā)器對(duì)象。在 name 一列，我們可以看到觸發(fā)器名稱。

(2).sp_helptext 查看觸發(fā)器內(nèi)容

use  數(shù)據(jù)庫(kù)名  go exec sp_helptext  觸發(fā)器名稱

將會(huì)以表的樣式顯示觸發(fā)器內(nèi)容。

除了觸發(fā)器外，sp_helptext 還可以顯示 規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義函數(shù)、視圖的文本。

(3).sp_helptrigger 用于查看觸發(fā)器的屬性

sp_helptrigger 有兩個(gè)參數(shù)：第一個(gè)參數(shù)為表名; 第二個(gè)為觸發(fā)器類型，為 char(6) 類型，可以是  INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發(fā)器的屬性。

-- 查看數(shù)據(jù)庫(kù)中所有的觸發(fā)器  use  數(shù)據(jù)庫(kù)名  go select * from sysobjects where xtype= TR

use 數(shù)據(jù)庫(kù)名 goexec sp_helptrigger tableName

2. 禁用啟用觸發(fā)器

禁用:alter table 表名 disable trigger 觸發(fā)器名稱

啟用:alter table 表名 enable trigger 觸發(fā)器名稱

如果有多個(gè)觸發(fā)器，則各個(gè)觸發(fā)器名稱之間用英文逗號(hào)隔開(kāi)。

如果把“觸發(fā)器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發(fā)器。

3. 修改觸發(fā)器

-- 修改觸發(fā)器語(yǔ)法  ALTER TRIGGER trigger_name ON table_name [ WITH ENCRYPTION ] FOR {[DELETE][,][INSERT][,][UPDATE]} AS sql_statement;

4. 刪除觸發(fā)器

 -- 語(yǔ)法格式: DROP TRIGGER { trigger } [ ,...n ]  參數(shù): trigger:  要?jiǎng)h除的觸發(fā)器名稱  n: 表示可以刪除多個(gè)觸發(fā)器的占位符

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對(duì)丸趣 TV 的支持。