SQL SERVER TRIGGER 觸發器_ZenDei技術網路在線

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

1.觸發器簡介

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

2.觸發器類型

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 觸發器。

3.觸發器語法結構

AFTER 觸發器語法：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

INSTEAD OF 觸發器語法：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

DDL 觸發器語法：

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

登陸觸發器語法：

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

參數介紹：

CREATE OR ALTER：創建或者有條件的修改觸發器（即要修改的觸發器必須已經存在）
schema_name：ML觸發器所屬的模式的名稱（即所有者，例如：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 標識符，並且必須作為具有程式集可見性的程式集中的類存在。

DML觸發器例子

準備一張訂單表（tbOrder），包含訂單ID,訂單交易金額，訂單創建時間，準備訂單交易分段時間總額（tbOrderTotalPrice ），用來統計每個月的交易合計金額

創建INSERT 觸發器：當下單後更新tbOrderTotalPrice 的統計信息

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tSumTotalOrderPrice]    Script Date: 2018/5/15 11:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tSumTotalOrderPrice] on [dbo].[tbOrder]
AFTER INSERT
AS
BEGIN
	DECLARE @DT INT
	SET @DT= CONVERT(varchar(6),GETDATE(),112)

	DECLARE @SUM DECIMAL(18, 2)
	SET @SUM=(SELECT SUM(OPrice) FROM tbOrder WHERE @DT = CONVERT(varchar(6),CreateDT,112))

	IF(EXISTS(SELECT * FROM tbOrderTotalPrice WHERE YearMoth=@DT))
	BEGIN
		UPDATE tbOrderTotalPrice SET TotalPrice=@SUM WHERE YearMoth=@DT
	END
	ELSE
	BEGIN
		INSERT INTO tbOrderTotalPrice VALUES (@DT,@SUM)
	END
END

GO

執行INSERT 行為：INSERT INTO [dbo].[tbOrder]([OPrice],[CreateDT])VALUES(9,GETDATE())