SQLServer 觸發器入門

来源:http://www.cnblogs.com/lbczzvv/archive/2017/12/13/8031982.html
-Advertisement-
Play Games

閱讀目錄 一:觸發器的優點 二:觸發器的作用 三:觸發器的分類 四：觸發器的工作原理 五:創建觸發器 六:管理觸發器 概念： 觸發器（trigger）是SQL server 提供給程式員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動， ...


閱讀目錄

概念：

  觸發器（trigger）是SQL server 提供給程式員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用於加強數據的完整性約束和業務規則等。 觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 數據字典中查到。

觸發器和存儲過程的區別:

  觸發器與存儲過程的區別是運行方式的不同，觸發器不能執行EXECUTE語句調用，而是在用戶執行Transact-SQL語句時自動觸發執行而存儲過程需要用戶，應用程式或者觸發器來顯示地調用並執行。

回到頂部

一:觸發器的優點

 1.觸發器是自動的。當對錶中的數據做了任何修改之後立即被激活。

 2.觸發器可以通過資料庫中的相關表進行層疊修改。

 3.觸發器可以強制限制。這些限制比用CHECK約束所定義的更複雜。與CHECK約束不同的是，觸發器可以引用其他表中的列。

回到頂部

二:觸發器的作用

 觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性，它能夠對資料庫中的相關表進行級聯修改，提高比CHECK約束更複雜的的數據完整性，並自定義錯誤消息。觸發器的主要作用主要有以下接個方面：

  1. 強制資料庫間的引用完整性
  2. 級聯修改資料庫中所有相關的表，自動觸發其它與之相關的操作
  3. 跟蹤變化，撤銷或回滾違法操作，防止非法修改數據
  4. 返回自定義的錯誤消息，約束無法返回信息，而觸發器可以
  5. 觸發器可以調用更多的存儲過程
回到頂部

三:觸發器的分類

 SqlServer包括三種常規類型的觸發器：DML觸發器、DDL觸發器和登錄觸發器。

1.DML(數據操作語言,Data Manipulation Language)觸發器

 DML觸發器是一些附加在特定表或視圖上的操作代碼，當資料庫伺服器中發生數據操作語言事件時執行這些操作。SqlServer中的DML觸發器有三種：

  1. insert觸發器:向表中插入數據時被觸發；
  2. delete觸發器：從表中刪除數據時被觸發；
  3. update觸發器：修改表中數據時被觸發。

當遇到下列情形時，應考慮使用DML觸發器：

  1. 通過資料庫中的相關表實現級聯更改
  2. 防止惡意或者錯誤的insert、update和delete操作，並強制執行check約束定義的限制更為複雜的其他限制。
  3. 評估數據修改前後表的狀態，並根據該差異才去措施。

2.DDL(數據定義語言,Data Definition Language)觸發器

 DDL觸發器是當伺服器或者資料庫中發生數據定義語言(主要是以create,drop,alter開頭的語句)事件時被激活使用，使用DDL觸發器可以防止對數據架構進行的某些更改或記錄數據中的更改或事件操作。

3.登錄觸發器

    登錄觸發器將為響應 LOGIN 事件而激發存儲過程。與 SQL Server 實例建立用戶會話時將引發此事件。登錄觸發器將在登錄的身份驗證階段完成之後且用戶會話實際建立之前激發。因此，來自觸發器內部且通常將到達用戶的所有消息（例如錯誤消息和來自 PRINT 語句的消息）會傳送到 SQL Server 錯誤日誌。如果身份驗證失敗，將不激發登錄觸發器。

回到頂部

四：觸發器的工作原理

觸發器觸發時：

  1. 系統自動在記憶體中創建deleted表或inserted表；
  2. 只讀，不允許修改，觸發器執行完成後，自動刪除。

inserted表：

  1. 臨時保存了插入或更新後的記錄行；
  2. 可以從inserted表中檢查插入的數據是否滿足業務需求；
  3. 如果不滿足，則向用戶發送報告錯誤消息，並回滾插入操作。　

deleted表：

  1. 臨時保存了刪除或更新前的記錄行；
  2. 可以從deleted表中檢查被刪除的數據是否滿足業務需求；
  3. 如果不滿足，則向用戶報告錯誤消息，並回滾插入操作。

inserted表和deleted表對照：　

修改操作記錄 inserted表 deleted表
增加(insert)記錄 存放新增的記錄 ............
刪除(deleted)記錄 .............. 存放被刪除的記錄
修改(update)記錄 存放更新後的記錄 存放更新前的記錄

 

 

 

回到頂部

五:創建觸發器

 創建觸發器的語法： 

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

 準備測試數據:

複製代碼 
--創建學生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)
複製代碼

1.創建insert觸發器

複製代碼 
--創建insert觸發器
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));--創建存儲學生人數的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; --把更新後總的學生數插入到student_sum表中
end
複製代碼 複製代碼 
--測試觸發器trig_insert-->功能是向student插入數據的同時級聯插入到student_sum表中，更新stuCount
--因為是後觸發器，所以先插入數據後，才觸發觸發器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('呂布','男',30);
select stuCount 學生總人數 from student_sum;    
insert into student(stu_name,stu_gender,stu_age)values('貂蟬','女',30);            
select stuCount 學生總人數 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞞','男',40);                
select stuCount 學生總人數 from student_sum;
複製代碼

執行上面的語句後，結果如下圖所示：

 既然定義了學生總數表student_sum表是向student表中插入數據後才計算學生總數的，所以學生總數表應該禁止用戶向其中插入數據

複製代碼 
--創建insert_forbidden,禁止用戶向student_sum表中插入數據
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向該表中插入記錄，操作被禁止',1,1)--raiserror 是用於拋出一個錯誤
rollback transaction
end
複製代碼 
--觸發觸發器insert_forbidden
insert student_sum (stuCount) values(5);

結果如下：

 2.創建delete觸發器

　　用戶執行delete操作，就會激活delete觸發器，從而控制用戶能夠從資料庫中刪除數據記錄，觸發delete觸發器後，用戶刪除的記錄會被添加到deleted表中，原來表的相應記錄被刪除，所以在deleted表中查看刪除的記錄。

複製代碼 
--創建delete觸發器
create trigger trig_delete
on student 
after delete
as
begin
    select stu_id as 已刪除的學生編號,stu_name stu_gender,stu_age
    from deleted
end;
複製代碼 
--執行一一條delete語句觸發trig_delete觸發器
delete from student where stu_id=1;

結果如下：

 3.創建UPDATE觸發器

　　update觸發器是當用戶在指定表上執行update語句時被調用被調用，這種類型的觸發器用來約束用戶對數據的修改。update觸發器可以執行兩種操作：更新前的記錄存儲在deleted表中，更新後的記錄存儲在inserted表中。

複製代碼 
--創建update觸發器
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 更新前學生編號,stu_name as 更新前學生姓名 from deleted
    select stu_id as 更新後學生編號,stu_name as 更新後學生姓名 from inserted
end
複製代碼 
--創建完成，執行一條update語句觸發trig_update觸發器
update student set stu_name='張飛' where stu_id=2;

 4.創建替代觸發器

　　與前面介紹的三種after觸發器不同，SqlServer伺服器在執行after觸發器的sql代碼後，先建立臨時的inserted表和deleted表，然後執行代碼中對資料庫操作，最後才激活觸發器中的代碼。而對於替代(instead of)觸發器,SqlServer伺服器在執行觸發instead of 觸發器的代碼時，先建立臨時的inserted表和deleted表，然後直接觸發instead of觸發器，而拒絕執行用戶輸入的DML操作語句。

複製代碼 
--創建instead of 觸發器 
create trigger trig_insteadOf
on student 
instead of insert
as 
begin
    declare @stuAge int;
    select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
    select '插入年齡錯誤' as '失敗原因'
end
複製代碼

創建完成，執行一條insert語句觸發觸發器trig_insteadOf

5.嵌套觸發器介紹

　如果一個觸發器在執行操作時調用了另外一個觸發器，而這個觸發器又接著調用了下一個觸發器，那麼就形成了嵌套觸發器。嵌套觸發器在安裝時就被啟用，但是可以使用系統存儲過程sp_configure禁用和重新啟用嵌套觸發器。

 

　　嵌套觸發器不一定要形成一個環，它可以 T1->T2->T3...這樣一直觸發下去，最多允許嵌套 32 層。如果嵌套的次數超過限制，那麼該觸發器將被終止，並回滾整個事務，使用嵌套觸發器需要註意以下幾點：

  • 預設情況下，嵌套觸發器配置選項是開啟的。
  • 在同一個觸發器事務中，一個嵌套觸發器不能被觸發兩次。
  • 由於觸發器是一個事務，如果在一系列嵌套觸發器的任意層次中發生錯誤，則整個事物都將取消，而且所有數據回滾。

嵌套是用來保持整個資料庫的完整性的重要功能，但有時可能需要禁用嵌套，如果禁用了嵌套，那麼修改一個觸發器的實現不會再觸發該表上的任何觸發器。在下述情況下，需要禁用嵌套觸發器：

  • 嵌套觸發要求複雜而有理論的設計，級聯修改可能會修改用戶不想涉及的數據。
  • 在一系列嵌套觸發器中的任意點的時間修改操作都會觸發一些觸發器，儘管這時資料庫提供很強的保護功能，但如果以特定的順序更新表，就會產生問題。

使用下列語句禁用嵌套和再次啟用嵌套：

--禁用嵌套
exce sp_configure 'nested triggers',0;
--啟用嵌套
exce sp_configure 'nested triggers',1;

6.遞歸觸發器

　　觸發器的遞歸是指一個觸發器從其內部再一次激活該觸發器，例如update操作激活的觸發器內部還有一條數據表的更新語句，那麼這個更新語句就有可能激活這個觸發器本身，當然，這種遞歸的觸發器內部還會有判斷語句，只有一定情況下才會執行那個T_SQL語句，否則就成為無線調用的死迴圈了。

SqlServer中的遞歸觸發器包括兩種：直接遞歸和間接遞歸。

  • 直接遞歸：觸發器被觸發後並執行一個操作，而該操作又使用一個觸發器再次被觸發。
  • 間接遞歸：觸發器被觸發並執行一個操作，而該操作又使另一個表中的某個觸發器被觸發，第二個觸發器使原始表得到更新，從而再次觸發第一個觸發器。

預設情況下，遞歸觸發器選項是禁用的。遞歸觸發器最多只能遞歸16層，如果遞歸中的第16個觸發器激活了第17個觸發器，則結果與發佈的rollback命令一樣，所有數據都將回滾。 

我們舉例解釋如下，假如有表1、表2名稱分別為 T1、T2，在 T1、T2 上分別有觸發器 G1、G2。

  • 間接遞歸：對 T1 操作從而觸發 G1，G1 對 T2 操作從而觸發 G2，G2 對 T1 操作從而再次觸發 G1...
  • 直接遞歸：對 T1 操作從而觸發 G1，G1 對 T1 操作從而再次觸發 G1... 

設置直接遞歸：

預設情況下是禁止直接遞歸的，要設置為允許有兩種方法：

  • T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true；
  • EM：資料庫上點右鍵->屬性->選項。 
回到頂部

六:管理觸發器　

1.查看觸發器

(1).查看資料庫中所有的觸發器

--查看資料庫中所有的觸發器
use 資料庫名
go
select * from sysobjects where xtype='TR'

sysobjects 保存著資料庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。在 name 一列，我們可以看到觸發器名稱。

(2).sp_helptext 查看觸發器內容

use 資料庫名
go
exec sp_helptext '觸發器名稱'

 將會以表的樣式顯示觸發器內容。 

 除了觸發器外，sp_helptext 還可以顯示 規則、預設值、未加密的存儲過程、用戶定義函數、視圖的文本。

(3).sp_helptrigger 用於查看觸發器的屬性

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

use 資料庫名
go
exec sp_helptrigger tableName

2.禁用啟用觸發器

　　禁用:alter table 表名 disable trigger 觸發器名稱
　　啟用:alter table 表名 enable trigger 觸發器名稱

　　如果有多個觸發器，則各個觸發器名稱之間用英文逗號隔開。

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

3修改觸發器

複製代碼 
--修改觸發器語法
ALTER TRIGGER  trigger_name 
     ON  table_name 
     [ WITH ENCRYPTION ] 
     FOR {[DELETE][,][INSERT][,][UPDATE]}
     AS
       sql_statement;
複製代碼

4.刪除觸發器

 --語法格式:
      DROP  TRIGGER   { trigger } [ ,...n ]
參數:
 trigger: 要刪除的觸發器名稱
 n:表示可以刪除多個觸發器的占位符
原文轉自：https://www.cnblogs.com/selene/p/4493311.html
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • FreeRTOS--疑難解答
    花了3個晚上，把這個章節看完，受益匪淺。 1. 最有用的應該是與中斷相關的錯誤，優先排查中斷優先順序設置。 2. 堆棧溢出檢查，可能用到，一般先把堆棧設置的足夠大，只要沒有溢出就是好事，溢出了，掌握了棧溢出鉤子函數排錯很方便。 3. 相關的問題應該儘量不會出現，畢竟只要需要列印調試信息的情況下才使用， ...
  • macbookPro 搭建maven環境下載jar包
    今天要用itext的jar包，去官網看發現好像只能用maven下載，而我之前又換了電腦，沒辦法咯，重新搭一次maven環境吧，在此記錄，已便分享或自己將來查找 首選確定自己環境上jdk裝好了，如果沒有，先裝一下吧 去官網下載安裝包，然後解壓放入任意目錄下，最好為純英文目錄 打開命令行 vi .bas ...
  • MySQL建表
    -- 1.創建部門表dept -- 2.創建員工表emp -- 3.創建工資等級表salgrade -- 表建立後，導入數據並查詢名字中有S的員工 -- 查詢結果 ...
  • 泛微e-cology和Oracle無法訪問的解決方案
    最近公司的泛微OA無法訪問，Oracle資料庫也無法正常啟動，嘗試了好多方法，終於解決了，先說說基本情況，希望能給碰到同樣問題的朋友帶來一點幫助。 伺服器操作系統：Window s Server 2016 Datacenter 泛微OA版本： e-cology V8.0，安裝目錄為D:\WEAVER ...
  • WampSever 安裝問題解析
    鑒於有些電腦安裝wampsever出現的幾種問題 [1] 2.2版本問題 這裡埠配置正確，本地伺服器沒有開啟（也就是iis這邊沒有問題） 還是出現這種情況 也就是Apache service 和 PHP service可以使用但是就是差一個服務 那麼就剩下資料庫service了（MySQL） 這裡 ...
  • SQL簡單查詢後續記錄
    --首先創建資料庫TESTCREATE DATABASE TEST--創建表tb_userUSE TESTCREATE TABLE [tb_user]([name] [nvarchar] (50) NOT NULL,[age] [int] NOT NULL,[sex] [nvarchar] (4)) ...
  • 游標遍歷所有資料庫迴圈執行修改資料庫的sql命令
    MSSQL資料庫伺服器上有很多類似的資料庫，需要將這些資料庫統一修改其中的某些表或者某些命令，那麼就會想到用游標來遍歷。 先來說思路： 1，首先需要查詢出所有的資料庫； select [name] from [master].[dbo].[sysdatabases] order by [name]; ...
  • oracle 簡單資料庫同步腳本
    Oracle 資料庫簡單同步 前提： 方式： 步驟： 下載文件：backscript.rar ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類