觸發(fā)器分類

SQL Server提供三類觸發(fā)器：

• DML觸發(fā)器：在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作(Insert、Update 或Delete)事件時(shí)自動(dòng)執(zhí)行骡湖。
• DDL觸發(fā)器：在服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義(如Create佩捞、Alter灶伊、Drop)事件時(shí)自動(dòng)執(zhí)行援制。
• 登錄觸發(fā)器：在與SQL Server實(shí)例建立用戶會(huì)話時(shí)自動(dòng)執(zhí)行赠摇，主要用來(lái)審核和控制服務(wù)器會(huì)話超燃。

與Oracle對(duì)比区拳，SQL Server的觸發(fā)器有以下特點(diǎn)：

• SQL Server不支持Oracle的Before觸發(fā)器功能。

雖然沒(méi)有Before觸發(fā)器功能意乓，但是SQL Server可以利用Instead Of重寫關(guān)鍵字樱调，然后在觸發(fā)器中加入想要在DML操作之前的事務(wù)。

• SQL Server不支持Oracle的row-level觸發(fā)器功能届良，而只支持類似Oracle的statement-level形式的觸發(fā)器笆凌，即DML語(yǔ)句只會(huì)激活一次觸發(fā)器的執(zhí)行，而不管其影響了多少條記錄士葫。

Oracle的row-level觸發(fā)器：創(chuàng)建觸發(fā)器時(shí)附加for each row關(guān)鍵字乞而，對(duì)觸發(fā)事件所影響的每條記錄都執(zhí)行一次觸發(fā)器。如果一個(gè)update語(yǔ)句修改了10條記錄慢显，則row-level觸發(fā)器會(huì)執(zhí)行10次爪模。
??雖然SQL Server不支持row-level，但是我們可以通過(guò)INSERTED表和DELETED表的功能荚藻。比如Oracle觸發(fā)器中的這條語(yǔ)句：

for each row when(abs(new.sal>old.sal)>2000)

在SQL Server中可以寫成這樣：

if(select max(abs(i.sal-d.sal)) from inserted i,deleted d 
  where i.empno=d.empno)>2000 

• SQL Server的DDL觸發(fā)器只支持After形式屋灌。

SQL Server的DDL觸發(fā)器只有在完成相應(yīng)的DDL語(yǔ)句之后才會(huì)被觸發(fā)，因此DDL觸發(fā)器不能使用INSTEAD OF選項(xiàng)应狱。

• SQL Server的DDL觸發(fā)器除與Oracle的DDL觸發(fā)器功能類似外共郭，還包含了Oracle的系統(tǒng)觸發(fā)器的部分功能

DML觸發(fā)器

了解DML觸發(fā)器

當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語(yǔ)句侦香、UPDATE 語(yǔ)句或 DELETE 語(yǔ)句落塑。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語(yǔ)句罐韩。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待憾赁。如果檢測(cè)到錯(cuò)誤（例如，磁盤空間不足）散吵，則整個(gè)事務(wù)即自動(dòng)回滾龙考。
??DML 觸發(fā)器在以下方面非常有用：

• DML 觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改蟆肆。不過(guò)，通過(guò)級(jí)聯(lián)引用完整性約束可以更有效地進(jìn)行這些更改晦款。
• DML 觸發(fā)器可以防止惡意或錯(cuò)誤的 INSERT炎功、UPDATE 以及 DELETE 操作，并強(qiáng)制執(zhí)行比 CHECK 約束定義的限制更為復(fù)雜的其他限制缓溅。
  與 CHECK 約束不同蛇损，DML 觸發(fā)器可以引用其他表中的列。例如坛怪，觸發(fā)器可以使用另一個(gè)表中的 SELECT 比較插入或更新的數(shù)據(jù)淤齐，以及執(zhí)行其他操作，如修改數(shù)據(jù)或顯示用戶定義錯(cuò)誤信息袜匿。
  DML 觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)更啄，并根據(jù)該差異采取措施。
• 一個(gè)表中的多個(gè)同類 DML 觸發(fā)器（INSERT居灯、UPDATE 或 DELETE）允許采取多個(gè)不同的操作來(lái)響應(yīng)同一個(gè)修改語(yǔ)句祭务。

DML 觸發(fā)器的類型

(1)AFTER 觸發(fā)器
??在執(zhí)行了 INSERT、UPDATE 或 DELETE 語(yǔ)句操作之后執(zhí)行 AFTER 觸發(fā)器怪嫌。指定 AFTER 與指定 FOR 相同义锥，它是 Microsoft SQL Server 早期版本中唯一可用的選項(xiàng)。AFTER 觸發(fā)器只能在表上指定喇勋。
??AFTER觸發(fā)器僅適用于表缨该，每個(gè)觸發(fā)操作（UPDATE、DELETE 和 INSERT）可以包含多個(gè)觸發(fā)器川背。

(2)INSTEAD OF 觸發(fā)器
??執(zhí)行 INSTEAD OF 觸發(fā)器代替通常的觸發(fā)動(dòng)作膨更。還可為帶有一個(gè)或多個(gè)基表的視圖定義 INSTEAD OF 觸發(fā)器，而這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型缴允。
??INSTEAD OF觸發(fā)器既可以用于表也可以用于視圖荚守，但是每個(gè)觸發(fā)操作（UPDATE、DELETE 和 INSERT）只能包含一個(gè)觸發(fā)器练般。

(3)CLR 觸發(fā)器
??CLR 觸發(fā)器可以是 AFTER 觸發(fā)器或 INSTEAD OF 觸發(fā)器敞贡。CLR 觸發(fā)器還可以是 DDL 觸發(fā)器。CLR 觸發(fā)器將執(zhí)行在托管代碼（在 .NET Framework 中創(chuàng)建并在 SQL Server 中上載的程序集的成員）中編寫的方法摄职，而不用執(zhí)行 Transact-SQL 存儲(chǔ)過(guò)程誊役。

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

當(dāng)DML觸發(fā)器執(zhí)行的時(shí)候获列，會(huì)產(chǎn)生兩個(gè)只讀的臨時(shí)表：inserted表和deleted表。這兩個(gè)表的結(jié)構(gòu)和觸發(fā)器所在的表的結(jié)構(gòu)相同蛔垢。當(dāng)觸發(fā)器執(zhí)行完成之后击孩，這兩個(gè)表就會(huì)被自動(dòng)刪除。
??當(dāng)對(duì)觸發(fā)器表執(zhí)行INSERT操作時(shí)鹏漆，新插入的行將同時(shí)被添加到inserted表中巩梢，inserted表中的行是觸發(fā)器表中新添加行的副本。
??當(dāng)對(duì)觸發(fā)器表執(zhí)行DELETE操作時(shí)艺玲，行將從觸發(fā)器表中刪除且改，同時(shí)被存入deleted表中。deleted表和觸發(fā)器表沒(méi)有相同的行板驳。
??當(dāng)對(duì)觸發(fā)器表執(zhí)行UPDATE操作時(shí)，先從觸發(fā)器表中刪除舊行碍拆，然后再插入新行若治。其中被刪除的舊行被插入到deleted表中，插入的新行的副本被插入到inserted表中感混。
??創(chuàng)建DML觸發(fā)器語(yǔ)法如下：

IF EXISTS(select * from sys.sysobjects where name='<觸發(fā)器名>')
    DROP TIGGER <觸發(fā)器名> 
GO
CREATE TIGGER <觸發(fā)器名> 
ON <表名|視圖名>
  [WITH ENCRYPTION]
  FOR|AFTER|INSTEAD OF
  [INSERT][,][UPDATE][,][DELETE]
AS
    <T-SQL語(yǔ)句或語(yǔ)句塊>

• WITH ENCRYPTION：對(duì)觸發(fā)器進(jìn)行加密處理
• FOR|AFTER：FOR和AFTER意義相同端幼，指定觸發(fā)器中在相應(yīng)的DML操作成功執(zhí)行后才觸發(fā)。
• INSTEAD OF：指定執(zhí)行DML觸發(fā)器用于“代替”引發(fā)觸發(fā)器執(zhí)行的DML語(yǔ)句弧满。
• [INSERT][,][UPDATE][,][DELETE]：指定能夠激活觸發(fā)器的操作婆跑，必須至少指定一個(gè)操作

使用觸發(fā)器小Tips
①觸發(fā)器本身就是一個(gè)事務(wù)，所以在觸發(fā)器里面可以對(duì)修改數(shù)據(jù)進(jìn)行一些特殊的檢查庭呜。如果不滿足可以利用事務(wù)回滾滑进，撤銷操作(區(qū)別于過(guò)程和函數(shù)，過(guò)程和函數(shù)不能在語(yǔ)句塊里面進(jìn)行事務(wù)的commit募谎、rollback等操作)扶关。
②SQL Server不支持Oracle創(chuàng)建觸發(fā)器時(shí)的update of column的語(yǔ)法形式，也就是不能判斷指定哪一列被Update操作才觸發(fā)觸發(fā)器数冬。但是在SQL Server中我們可以通過(guò)update(column_name)的形式來(lái)判斷列是否被修改過(guò)节槐，詳見(jiàn)示例(2)。

【示例】
(1)級(jí)聯(lián)修改數(shù)據(jù)

--update更新類型觸發(fā)器
if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的數(shù)據(jù)
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的數(shù)據(jù)
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) 
             where name like '%'+ @oldName + '%';
            print '級(jí)聯(lián)修改數(shù)據(jù)成功拐纱！';
        end
    else
        print '無(wú)需修改student表铜异！';
go

(2)審計(jì)對(duì)emp表的sal列的update操作

create tigger tri_emp_sal_audit
on emp
after update
as
if update(sal)
  insert into emp_sal_audit
  select i.empno,d.sal,i.sal,user,getdate()
  from inserted i,deleted d
  where i.empno=d.empno
go

設(shè)計(jì)INSTEAD OF觸發(fā)器

INSTEAD OF 觸發(fā)器的主要優(yōu)點(diǎn)是可以使不能更新的視圖支持更新〗占埽基于多個(gè)基表的視圖必須使用 INSTEAD OF 觸發(fā)器來(lái)支持引用多個(gè)表中數(shù)據(jù)的插入揍庄、更新和刪除操作。INSTEAD OF 觸發(fā)器的另一個(gè)優(yōu)點(diǎn)是使您得以編寫這樣的邏輯代碼：在允許批處理的其他部分成功的同時(shí)拒絕批處理中的某些部分咕宿。
??INSTEAD OF 觸發(fā)器可以進(jìn)行以下操作：

• 忽略批處理中的某些部分币绩。
• 不處理批處理中的某些部分并記錄有問(wèn)題的行酝枢。
• 如果遇到錯(cuò)誤情況則采取備用操作。

適用INSTEAD OF觸發(fā)器注意事項(xiàng)：

• 對(duì)于含有使用 DELETE 或 UPDATE 級(jí)聯(lián)操作定義的外鍵的表扰她，不能定義 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 觸發(fā)器犹赖。
• 因?yàn)镮NSTEAD OF觸發(fā)器的內(nèi)容會(huì)代替DELETE、UPDATE董瞻、INSERT觸發(fā)操作寞蚌，所以在INSTEAD OF觸發(fā)器內(nèi)要通過(guò)inserted表和deleted表重寫DML操作。

【示例】
(1)創(chuàng)建觸發(fā)器禁止用戶在每天晚上18:00:00到23:59:59之間以及凌晨00:00:00到06:59:59之間對(duì)emp表執(zhí)行update操作钠糊。

create tigger tri_deny_emp_update
on emp
instead of update
as
if(datepart(hour,getdate())>=18 and datepart(hour,getdate())<=23)
or(datepart(hour,getdate())>=0 and datepart(hour,getdate())<=6)
  print 'Update not allowed now.'
else
begin
   delete from emp where empno in (select empno from deleted)
   insert into emp select * from inserted
end
go

(2)在下列 Transact-SQL 語(yǔ)句序列中挟秤，INSTEAD OF 觸發(fā)器更新視圖中的兩個(gè)基表。另外抄伍，顯示以下處理錯(cuò)誤的方法：

• 忽略對(duì) Person 表的重復(fù)插入艘刚，并且插入的信息將記錄在 PersonDuplicates 表中。
• 將對(duì) EmployeeTable 的重復(fù)插入轉(zhuǎn)變?yōu)?UPDATE 語(yǔ)句截珍，該語(yǔ)句將當(dāng)前信息檢索至 EmployeeTable攀甚，而不會(huì)產(chǎn)生重復(fù)鍵沖突。

T-SQL 語(yǔ)句創(chuàng)建兩個(gè)基表岗喉、一個(gè)視圖秋度、一個(gè)記錄錯(cuò)誤表和視圖上的 INSTEAD OF 觸發(fā)器。以下表將個(gè)人數(shù)據(jù)和業(yè)務(wù)數(shù)據(jù)分開(kāi)并且是視圖的基表钱床。

-- 兩個(gè)基表
CREATE TABLE Person
   (
    SSN         char(11) PRIMARY KEY,
    Name        nvarchar(100),
    Address     nvarchar(100),
    Birthdate   datetime
   )

CREATE TABLE EmployeeTable
   (
    EmployeeID       int PRIMARY KEY,
    SSN              char(11) UNIQUE,
    Department       nvarchar(10),
    Salary           money,
    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
    REFERENCES Person (SSN)
   )
-- 可記錄對(duì)插入具有重復(fù)的社會(huì)保障號(hào)的行的嘗試荚斯。
-- PersonDuplicates 表記錄插入的值、嘗試插入的用戶的姓名和插入的時(shí)間查牌。
CREATE TABLE PersonDuplicates
   (
    SSN           char(11),
    Name          nvarchar(100),
    Address       nvarchar(100),
    Birthdate     datetime,
    InsertSNAME   nchar(100),
    WhenInserted  datetime
   )
-- 一個(gè)視圖
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
       Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN

INSTEAD OF觸發(fā)器：

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
      FROM Person P, inserted I
      WHERE P.SSN = I.SSN))
   INSERT INTO Person
      SELECT SSN,Name,Address,Birthdate FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
   INSERT INTO PersonDuplicates
      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE() FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
      FROM EmployeeTable E, inserted
      WHERE E.SSN = inserted.SSN))
   INSERT INTO EmployeeTable
      SELECT EmployeeID,SSN, Department, Salary FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
   UPDATE EmployeeTable
      SET EmployeeID = I.EmployeeID,
          Department = I.Department,
          Salary = I.Salary
   FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSN
END

DDL觸發(fā)器

了解DDL觸發(fā)器

像常規(guī)觸發(fā)器一樣事期，DDL 觸發(fā)器將激發(fā)存儲(chǔ)過(guò)程以響應(yīng)事件。但與 DML 觸發(fā)器不同的是僧免，它們不會(huì)為響應(yīng)針對(duì)表或視圖的 UPDATE刑赶、INSERT 或 DELETE 語(yǔ)句而激發(fā)。相反懂衩，它們將為了響應(yīng)各種數(shù)據(jù)定義語(yǔ)言 (DDL) 事件而激發(fā)撞叨。這些事件主要與以關(guān)鍵字 CREATE、ALTER 和 DROP 開(kāi)頭的 Transact-SQL 語(yǔ)句對(duì)應(yīng)浊洞。執(zhí)行 DDL 式操作的系統(tǒng)存儲(chǔ)過(guò)程也可以激發(fā) DDL 觸發(fā)器牵敷。

測(cè)試您的 DDL 觸發(fā)器以確定它們是否響應(yīng)運(yùn)行的系統(tǒng)存儲(chǔ)過(guò)程。例如法希，CREATE TYPE 語(yǔ)句和 sp_addtype 存儲(chǔ)過(guò)程都將激發(fā)針對(duì) CREATE_TYPE 事件創(chuàng)建的 DDL 觸發(fā)器枷餐。

DDL 觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作苫亦。如果要執(zhí)行以下操作毛肋，請(qǐng)使用 DDL 觸發(fā)器：

• 要防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改怨咪。
• 希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改。
• 要記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件润匙。

DDL觸發(fā)器的作用域

在響應(yīng)當(dāng)前數(shù)據(jù)庫(kù)或服務(wù)器上處理的 Transact-SQL 事件時(shí)诗眨，可以觸發(fā) DDL 觸發(fā)器。觸發(fā)器的作用域取決于事件孕讳。例如匠楚，每當(dāng)數(shù)據(jù)庫(kù)中或服務(wù)器實(shí)例上發(fā)生 CREATE_TABLE 事件時(shí)，都會(huì)激發(fā)為響應(yīng) CREATE_TABLE 事件創(chuàng)建的 DDL 觸發(fā)器厂财。僅當(dāng)服務(wù)器上發(fā)生 CREATE_LOGIN 事件時(shí)芋簿，才能激發(fā)為響應(yīng)CREATE_LOGIN 事件創(chuàng)建的 DDL 觸發(fā)器。

DDL事件以及DDL事件組

請(qǐng)參閱：https://msdn.microsoft.com/zh-cn/library/bb522542(v=sql.105).aspx

使用 EVENTDATA 函數(shù)

使用 EVENTDATA 函數(shù)璃饱，可以捕獲有關(guān)激發(fā) DDL 觸發(fā)器的事件的信息与斤。此函數(shù)返回 xml 值。XML 架構(gòu)包括下列信息：

• 事件時(shí)間荚恶。
• 在執(zhí)行觸發(fā)器時(shí)幽告，連接的系統(tǒng)進(jìn)程 ID (SPID)。
• 激發(fā)觸發(fā)器的事件類型裆甩。

根據(jù)事件類型，該架構(gòu)還包括其他信息齐唆，例如事件在其中發(fā)生的數(shù)據(jù)庫(kù)嗤栓、發(fā)生事件的相關(guān)對(duì)象以及事件的 Transact-SQL 語(yǔ)句。
??只有直接在 DDL 或登錄觸發(fā)器內(nèi)部引用 EVENTDATA 時(shí)箍邮，EVENTDATA 才會(huì)返回?cái)?shù)據(jù)茉帅。如果 EVENTDATA 由其他例程調(diào)用（即使這些例程由 DDL 或登錄觸發(fā)器進(jìn)行調(diào)用），將返回 NULL锭弊。在隱式或顯式調(diào)用 EVENTDATA 的事務(wù)提交或回滾之后堪澎，EVENTDATA 所返回的數(shù)據(jù)將無(wú)效。
??由于EVENTDATE返回的是xml味滞，所以要對(duì)xml進(jìn)行解析樱蛤。所幸，SQL Server提供了XQuery剑鞍。XQuery 是一種可以查詢結(jié)構(gòu)化或半結(jié)構(gòu)化 XML 數(shù)據(jù)的語(yǔ)言昨凡，基于XPath，并支持更好的迭代蚁署、更好的排序結(jié)果以及構(gòu)造必需的 XML 的功能便脊。詳情請(qǐng)參閱：https://msdn.microsoft.com/zh-cn/library/ms190798(v=sql.105).aspx

如果需要返回事件數(shù)據(jù)，我們建議使用 XQuery value() 方法而不是 query() 方法光戈。query() 方法可在輸出中返回 XML 和以“and”符轉(zhuǎn)義的回車符和換行符 (CR/LF) 實(shí)例哪痰，而 value() 方法無(wú)法在輸出中呈現(xiàn) CR/LF 實(shí)例遂赠。

下面提供一些常用的解析：

-- 受影響的時(shí)間
@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'sysname')
-- 受影響的服務(wù)器名
@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname')
-- 受影響的登陸名
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
-- 受影響的用戶名
@data.value('(/EVENT_INSTANCE/UserName)[1]', 'sysname')
-- 受影響的數(shù)據(jù)庫(kù)名
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
-- 受影響的架構(gòu)名
@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
-- 受影響的表名
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
-- 受影響的sql語(yǔ)句
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
-- 事件類型
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname')

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

CREATE TIGGER <觸發(fā)器名> 
ON ALL SERVER|DATABASE
    [WITH ENCRYPTION]
FOR|AFTER <事件類型或事件組>[,...,n]
[INSERT][,][UPDATE][,][DELETE]
AS
    <T-SQL語(yǔ)句或語(yǔ)句塊>

• ALL SERVER：指定DDL觸發(fā)器的作用域?yàn)楫?dāng)前服務(wù)器。如果指定了此參數(shù)晌杰，則只要當(dāng)前服務(wù)器中的任何位置上出現(xiàn)命令中指定的事件類型或事件組跷睦，就會(huì)激發(fā)該觸發(fā)器。創(chuàng)建的觸發(fā)器可在“SQL Server實(shí)例→服務(wù)器對(duì)象→觸發(fā)器”窗格中顯示乎莉。
• DATABASE：指定DDL觸發(fā)器的作用域?yàn)楫?dāng)前數(shù)據(jù)庫(kù)送讲。如果指定了此參數(shù)，則只要當(dāng)前數(shù)據(jù)庫(kù)中出現(xiàn)命令中指定的事件類型或事件組惋啃，就會(huì)激發(fā)該觸發(fā)器哼鬓。創(chuàng)建的觸發(fā)器可在“SQL Server實(shí)例→數(shù)據(jù)庫(kù)→(數(shù)據(jù)庫(kù)名稱)→可編程性→數(shù)據(jù)庫(kù)觸發(fā)器”窗格中顯示。
• 事件類型：將激活DDL觸發(fā)器的T-SQL事件的名稱边灭。
• 事件組：預(yù)定義的T-SQL語(yǔ)句事件分組名稱异希。執(zhí)行任何屬于事件組的T-SQL語(yǔ)句事件之后，都將激發(fā)DDL觸發(fā)器绒瘦。

【示例】
A称簿、運(yùn)用具有數(shù)據(jù)庫(kù)范圍的 DDL 觸發(fā)器
下面的示例使用 DDL 觸發(fā)器來(lái)防止從數(shù)據(jù)庫(kù)中刪除任何同義詞。

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO

B惰帽、運(yùn)用具有服務(wù)器范圍的 DDL 觸發(fā)器
在以下示例中憨降，如果當(dāng)前服務(wù)器實(shí)例上出現(xiàn)任何 CREATE DATABASE 事件，則使用 DDL 觸發(fā)器輸出一條消息该酗，并使用 EVENTDATA 函數(shù)檢索對(duì)應(yīng) Transact-SQL 語(yǔ)句的文本授药。

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
 PRINT 'Database Created.'
 SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO

C、創(chuàng)建事件數(shù)據(jù)在 DDL 觸發(fā)器中的日志表

CREATE TABLE ddl_log (
    PostTime datetime, 
    DB_User nvarchar(100), 
    Event nvarchar(100), 
    TSQL nvarchar(2000))
GO
CREATE TRIGGER log 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
DECLARE @data XML;
SET @data = EVENTDATA();
INSERT ddl_log (PostTime, DB_User, Event, TSQL) 
   VALUES 
   (GETDATE(), 
   CONVERT(nvarchar(100), CURRENT_USER), 
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO

登錄觸發(fā)器

登錄觸發(fā)器將為響應(yīng) LOGON 事件而執(zhí)行存儲(chǔ)過(guò)程呜魄。與 SQL Server 實(shí)例建立用戶會(huì)話時(shí)將引發(fā)此事件悔叽。登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。如果身份驗(yàn)證失敗爵嗅，將不激發(fā)登錄觸發(fā)器娇澎。
??在登錄觸發(fā)器中不支持分布式事務(wù)。在激發(fā)包含分布式事務(wù)的登錄觸發(fā)器時(shí)睹晒，將返回錯(cuò)誤 3969趟庄。

【示例】
下面的登錄觸發(fā)器示例拒絕了作為 login_test 登錄名的成員登錄 SQL Server 的嘗試（如果在此登錄名下已運(yùn)行三個(gè)用戶會(huì)話）。

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

管理觸發(fā)器

①查看觸發(fā)器信息
因?yàn)橛|發(fā)器是特殊的存儲(chǔ)過(guò)程伪很，所以查看觸發(fā)器也是使用系統(tǒng)存儲(chǔ)過(guò)程實(shí)現(xiàn)岔激，如：
sp_help '觸發(fā)器名稱'
sp_helptext '觸發(fā)器名稱'
sp_depends '觸發(fā)器名稱'|'表名'

②修改觸發(fā)器
1、修改DML觸發(fā)器

ALTER TIGGER <觸發(fā)器名>
ON <表名|視圖名>
  [WITH ENCRYPTION]
  FOR|AFTER|INSTEAD OF
  [INSERT][,][UPDATE][,][DELETE]
AS
    <T-SQL語(yǔ)句或語(yǔ)句塊>

2是掰、修改DDL觸發(fā)器

ALTER TIGGER <觸發(fā)器名> 
ON ALL SERVER|DATABASE
    [WITH ENCRYPTION]
FOR|AFTER <事件類型或事件組>[,...,n]
[INSERT][,][UPDATE][,][DELETE]
AS
    <T-SQL語(yǔ)句或語(yǔ)句塊>
;

③刪除觸發(fā)器

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)    
DROP TRIGGER [ IF EXISTS ] [schema_name.]trigger_name [ ,...n ] 
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)    
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ] ON { DATABASE | ALL SERVER }   
  
-- Trigger on a LOGON event (Logon Trigger)  
DROP TRIGGER [ IF EXISTS ] trigger_name [ ,...n ] ON ALL SERVER   

④禁用與啟用觸發(fā)器

#禁用觸發(fā)器
DISABLE TIGGER <觸發(fā)器名> ON 對(duì)象名|DATABASE|ALL SERVER
#啟用觸發(fā)器
ENABLE TIGGER <觸發(fā)器名> ON 對(duì)象名|DATABASE|ALL SERVER