SQL Server 創建觸發器（trigger）

来源:http://www.cnblogs.com/Brambling/archive/2017/04/21/6741666.html
-Advertisement-
Play Games

觸發器簡介： 觸發器是一種特殊的存儲過程，它的執行不是由程式調用，也不是手動執行，而是由事件來觸發。觸發器是當對某一個表進行操作。例如：update、insert、delete這些操作的時候，系統會自動調用執行該表上對應的觸發器。 觸發器分類： 1、DML（ 數據操縱語言 Data Manipula ...


觸發器簡介：

觸發器是一種特殊的存儲過程，它的執行不是由程式調用，也不是手動執行，而是由事件來觸發。觸發器是當對某一個表進行操作。例如：update、insert、delete這些操作的時候，系統會自動調用執行該表上對應的觸發器。

觸發器分類：

1、DML（ 數據操縱語言 Data Manipulation Language）觸發器：是指觸發器在資料庫中發生 DML 事件時將啟用。DML事件是指在表或視圖中對數據進行的 insert、update、delete 操作的語句。

2、DDL（數據定義語言 Data Definition Language）觸發器：是指當伺服器或資料庫中發生 DDL 事件時將啟用。DDL事件是指在表或索引中的 create、alter、drop 操作語句。

3、登陸觸發器：是指當用戶登錄 SQL SERVER 實例建立會話時觸發。如果身份驗證失敗，登錄觸發器不會觸發。

其中 DML 觸發器比較常用，根據 DML 觸發器觸發的方式不同又分為以下兩種情況：

after 觸發器（之後觸發）：其中 after 觸發器要求只有執行 insert、update、delete 某一操作之後觸發器才會被觸發，且只能定義在表上。

instead of 觸發器 （之前觸發）：instead of 觸發器並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身。可以在表或視圖上定義 instead of 觸發器。

DML 觸發器有兩個特殊的表：插入表（instered）和刪除表（deleted），這兩張表是邏輯表。這兩個表是建立在資料庫伺服器的記憶體中，而且兩張表的都是只讀的。這兩張表的結構和觸發器所在的數據表的結構是一樣的。當觸發器完成工作後，這兩張表就會被刪除。Inserted 表的數據是插入或是修改後的數據，而 deleted 表的數據是更新前的或是已刪除的數據。

AFTER 觸發器語法：

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

INSTEAD OF 觸發器語法：

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name

DDL 觸發器語法：

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]

登陸觸發器語法：

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]

參數：

CREATE OR ALTER：

創建或者有條件的修改觸發器（即要修改的觸發器必須已經存在）。

schema_name：
DML觸發器所屬的模式的名稱（即所有者，例如：dbo）。

trigger_name：
是觸發器的名稱。

table | view：

是執行 DML 觸發器的表或視圖，有時稱為觸發器表或觸發器視圖。指定表格或視圖的完全限定名稱是可選的。視圖只能由 INSTEAD OF 觸發器引用。

DATABASE：
將 DDL 觸發器的範圍應用於當前資料庫。如果指定，觸發器會在當前資料庫中發生 event_type 或 event_group 時觸發。

ALL SERVER：

將 DDL 或登錄觸發器的作用域應用於當前伺服器。如果指定，觸發器會在當前伺服器的任何地方發生 event_type 或 event_group 時觸發。

WITH ENCRYPTION：

加密 CREATE TRIGGER 語句的文本。使用 WITH ENCRYPTION 可以防止觸發器作為 SQL Server 複製的一部分進行發佈。無法為 CLR 觸發器指定 WITH ENCRYPTION。

EXECUTE AS：
指定執行觸發器的安全上下文。以便能夠控制 SQL Server 實例用於驗證觸發器引用的任何資料庫對象的許可權的用戶帳戶。

NATIVE_COMPILATION：
表示觸發器是本地編譯的。

SCHEMABINDING：
指定觸發器引用的表不能被刪除或更改。

FOR | AFTER：
AFTER 指定僅在觸發 SQL 語句中指定的所有操作成功執行時觸發 DML 觸發器。所有引用級聯操作和約束檢查在此觸發器觸發之前也必須成功。當 FOR 是指定的唯一關鍵字時，AFTER 是預設值。視圖無法定義AFTER觸發器。

INSTEAD OF：
指定執行 DML 觸發器而不是觸發 SQL 語句，因此覆蓋觸發語句的操作。無法為 DDL 或登錄觸發器指定 INSTEAD OF。

對於 INSTEAD OF 觸發器，在具有指定級聯動作 ON DELETE 的引用關係的表上不允許使用 DELETE 選項。類似地，在具有指定級聯動作 ON UPDATE 的引用關係的表上，不允許 UPDATE 選項。

{[DELETE] [，] [INSERT] [，] [UPDATE]} ：
指定在針對此表或視圖進行嘗試時激活 DML 觸發器的數據修改語句。必須至少指定一個選項。在觸發器定義中允許以任何順序對這些選項進行任意組合。

event_type：
是執行後導致 DDL 觸發器觸發的 Transact-SQL 語言事件的名稱。

event_group：
是 Transact-SQL 語言事件的預定義分組的名稱。屬於任何 Transact-SQL 語言事件執行後的 DDL 觸發器觸發 event_group。

sql_statement：
是觸發條件和動作。觸發條件指定附加條件，以確定嘗試的 DML，DDL 或登錄事件是否導致執行觸發器操作。

<method_specifier>：

對於 CLR 觸發器，指定要與觸發器綁定的程式集的方法。該方法不得不引用任何參數並返回 void。class_name 必須是有效的 SQL Server 標識符，並且必須作為具有程式集可見性的程式集中的類存在。

 

先看看示例數據：

 

insert 觸發器：

 1 if(OBJECT_ID('trigger_Stu_Insert') is not null)        -- 判斷名為 trigger_Stu_Insert 的觸發器是否存在
 2 drop trigger trigger_Stu_Insert        -- 刪除觸發器
 3 go
 4 create trigger trigger_Stu_Insert
 5 on Student        -- 指定創建觸發器的表
 6 for insert        -- insert 觸發器，也可以寫為 after insert
 7 as
 8 
 9 declare @C_Id    int
10 declare @S_Id    int
11 
12 select @C_Id=C_Id from Course where C_Name='SQL'    -- 獲取課程為 SQL 的ID
13 select @S_Id=S_Id from inserted        --插入一條學生的數據，那麼這條數據就存在 inserted 這個表中
14 
15 select @C_Id
16 select @S_Id
17 
18 select * from inserted
19 
20 update Student set C_S_Id=@C_Id where S_Id=@S_Id
21 go
22 
23 insert into Student(S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate)
24 values('016','大熊','男','210','2017-01-01')
25 
26 select * from Student
27 select * from Course

這個例子是：當 Student 表新增一條數據時，修改這條數據的課程ID。

delete 觸發器：

 1 if(OBJECT_ID('trigger_Stu_Delete') is not null)        -- 判斷名為 trigger_Stu_Delete 的觸發器是否存在
 2 drop trigger trigger_Stu_Delete        -- 刪除觸發器
 3 go
 4 create trigger trigger_Stu_Delete
 5 on Student        -- 指定創建觸發器的表
 6 for delete        -- delete 觸發器，也可以寫為 after delete
 7 as
 8 
 9 declare @C_S_Id    int
10 
11 select @C_S_Id=C_S_Id from deleted        --刪除的學生的數據就存在 deleted 這個表中
12 
13 select @C_S_Id
14 
15 select * from deleted
16 
17 delete from Course where C_Id=@C_S_Id        -- 刪除具有刪除的學生的課程ID的課程
18 go
19 
20 delete from Student where C_S_Id='1'
21 
22 select * from Student
23 select * from Course

這個例子是：刪除指定課程ID的學生時，並刪除指定課程ID的課程。

update 觸發器：

 1 if(OBJECT_ID('trigger_Cou_Update') is not null)        -- 判斷名為 trigger_Cou_Update 的觸發器是否存在
 2 drop trigger trigger_Cou_Update        -- 刪除觸發器
 3 go
 4 create trigger trigger_Cou_Update
 5 on Course        -- 指定創建觸發器的表
 6 for update        -- update 觸發器，也可以寫為 after update
 7 as
 8 
 9 declare @C_Id    int
10 
11 select @C_Id=C_Id from deleted        
12 
13 select * from deleted        -- 修改前的數據就存在 deleted 這個表中
14 
15 select * from inserted        -- 修改後的數據就存在 inserted 這個表中
16 
17 update Student set C_S_Id=@C_Id where C_S_Id is null 
18 go
19 
20 update Course set C_Name='C#' where C_Id='4'
21 
22 select * from Student
23 select * from Course

這個例子是：修改課程名稱時，把課程ID為空（null）的學生的課程ID預設為修改的課程ID。

禁止修改學生學號觸發器，觸發器進行數據回滾：

 1 if(OBJECT_ID('trigger_Stu_Update') is not null)        -- 判斷名為 trigger_Stu_Update 的觸發器是否存在
 2 drop trigger trigger_Stu_Update        -- 刪除觸發器
 3 go
 4 create trigger trigger_Stu_Update
 5 on Student        -- 指定創建觸發器的表
 6 for update        -- update 觸發器，也可以寫為 after update
 7 as
 8 begin try
 9     if(UPDATE(S_StuNo))        -- 列級觸發器：判斷是否更新了學生學號（學號不允許更改）
10     begin
11         raiserror(66666,16,1)
12     end
13 end try
14 begin catch
15     select * from deleted        -- 修改前的數據就存在 deleted 這個表中
16     select * from inserted        -- 修改後的數據就存在 inserted 這個表中
17     rollback tran;
18 end catch
19 go
20 
21 update  Student set S_StuNo='006' where S_Id='20'
22 
23 select * from Student

after 觸發器可以指定多個操作都可以觸發該觸發器。只需要在 for/after 後面添加逗號和觸發器的類型，例如：

1 for update,insert,delete 
2 
3 after update,insert,delete

instead of 觸發器：

這個觸發器就好玩了，下麵先看看數據。

 1 if(OBJECT_ID('trigger_Stu_InsteadOf') is not null)        -- 判斷名為 trigger_Stu_InsteadOf 的觸發器是否存在
 2 drop trigger trigger_Stu_InsteadOf        -- 刪除觸發器
 3 go
 4 create trigger trigger_Stu_InsteadOf
 5 on Student        -- 指定創建觸發器的表
 6 instead of update,insert,delete         -- instead of 觸發器
 7 as
 8     select * from deleted        -- 修改前的數據就存在 deleted 這個表中
 9     select * from inserted        -- 修改後的數據就存在 inserted 這個表中
10 go
11 
12 update Student set S_StuNo='006' where S_Id='20'
13 
14 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
15 values('017','清紅','女','180','2017-01-01')
16 
17 delete from Student where C_S_Id='5'
18 
19 select * from Student

執行上面的語句之後，咦，數據怎麼一點變化都沒有？看看上面的介紹。instead of 觸發器是之前觸發。

instead of 觸發器並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身，並且會覆蓋觸發語句的操作，即 after 觸發器 T-SQL 語句的操作，很明顯我們上面定義的表 Student 的 after 觸發器也沒有效果了，現在理解了這句話了吧。

修改觸發器：

 1 alter trigger trigger_Stu_InsteadOf        -- 修改觸發器
 2 on Student        -- 指定創建觸發器的表
 3 instead of update,insert,delete         -- instead of 觸發器
 4 as
 5     declare @Count1 int
 6     declare @Count2 int
 7 
 8     select @Count1=COUNT(1) from deleted        
 9     select @Count2=COUNT(1) from inserted        
10 
11     if(@Count1>0 and @Count2>0)
12     begin
13         select 'update操作'
14     end
15     else if(@Count1>0)
16     begin
17         select 'delete操作'
18     end
19     else if(@Count2>0)
20     begin
21         select 'insert操作'
22     end
23 go
24 
25 update Student set S_StuNo='006' where S_Id='20'
26 
27 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
28 values('017','清紅','女','180','2017-01-01')
29 
30 delete from Student where C_S_Id='5'
31 
32 select * from Student

啟用/禁用觸發器：

1 --禁用觸發器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 觸發器名稱
3 --啟用觸發器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 觸發器名稱

查詢已存在的觸發器：

1 -- 查詢已存在的觸發器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'
 1 -- sys.trigger_events 觸發器事件對象視圖
 2 select * from sys.trigger_events 
 3 
 4 -- 查看觸發器觸發事件對象    
 5 select a.type_desc,b.* from sys.trigger_events a 
 6 inner join sys.triggers b on a.object_id = b.object_id
 7 where b.name = 'trigger_Stu_Insert';
 8 
 9 -- 查詢創建觸發器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'

 

參考：

http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql#remarks-dml-triggers


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ORACLE聚合函數細節
    概要 計數 原意：查詢有多少記錄，就隨便選了個欄位 本來應該是14條記錄，但是只查到了13條，原因是當選定欄位進行聚合計算的時候該欄位受到一些規則的限制, 具體發生了什麼，下麵具體舉例說明 count函數調用方法是： count([distinct|all] columnnameOrNumber) ...
  • SELECT中的if_case流程函數
    DQL中常用的流程函數if_case 流程函數在一個SQL語句中實現條件選擇 模擬對職員薪水進行分類： 1、IF(expr1,expr2,expr3) 判斷第一個參數expr1為TRUE (expr1 <> 0 and expr1 <> NULL)： 成功，返回第二個參數expr2 失敗，返回第三個 ...
  • oracleLinux7上安裝oracle11g r2（腳本簡單配置環境）
    一 環境腳本簡單配置 #!/bin/bashmv /etc/yum.repos.d/* /tmpmv iso.repo /etc/yum.repos.d/tar zxvf a.tar.gzmv 7Server /root/sed -i '3 s/^#//' /etc/yum.repos.d/iso. ...
  • Oracle內連接、左外連接、右外連接、全外連接以及(+)號用法
    Oracle內連接、左外連接、右外連接、全外連接以及(+)號用法 1、數據準備階段 創建資料庫表,並插入數據,用於學習和測試. 先進行簡單的單表查詢. 2、內連接（join/inner join） 左表和右表都要做限制，僅顯示滿足on後麵條件的數據。 3、左外連接（left outer join/ ...
  • 串連某一欄位的值
    想把數據表某一欄位值，全串連起來，你可以使用一個方法。COALESCE： DECLARE @list NVARCHAR(MAX) SELECT @list = COALESCE(@list + ',','') + [Transportation_CN] FROM [dbo].[Transportat ...
  • RedHat_Hadoop_單機|偽分佈配置
    title: hadoopRedHatLab2 date: 2017 04 20 10:41:02 tags: hadoop, RedHat, wordcount 本文中所有x xxx均為未知 鬚根據你得具體版本號來決定 創建用戶 groupadd hadoop_user useradd g had ...
  • mysql實現rownum
    CREATE TABLE `total_freq_ctrl` ( CREATE TABLE `total_freq_ctrl` ( CREATE TABLE `total_freq_ctrl` ( 原sql SELECT machine, deny_flow, total_flow, time FR ...
  • Oracle表空間
    【聲明】本文旨在記錄自己學習過程，方便日後資料查閱。 本文原地址：http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html 我們知道oarcle資料庫真正存放數據的是數據文件（data files），Oarcle表空間（tablespac ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類