15. DML, DDL, LOGON 觸發器

来源:https://www.cnblogs.com/seusoftware/archive/2018/06/01/9120632.html
-Advertisement-
Play Games

一. DML觸發器; 二. DDL觸發器; 三. LOGON 觸發器; ...


觸發器可以理解為由特定事件觸發的存儲過程， 和存儲過程、函數一樣，觸發器也支持CLR，目前SQL Server共支持以下幾種觸發器：

1. DML觸發器， 表/視圖級有效，可由DML語句 (INSERT, UPDATE, DELETE) 觸發；

2. DDL 觸發器，資料庫級有效，可由DDL語句 (CREATE, ALTER, DROP 等) 觸發；

3. LOGON 觸發器， 實例級有效，可由用戶賬號登錄(LOGON)資料庫實例時觸發；

 

一. DML觸發器

1. 語句級觸發器/行級觸發器

在SQL Server中，從定義來說只有語句級觸發器，但如果有行級的邏輯要處理，有兩個僅在觸發器內有效的表 (inserted, deleted)， 存放著受影響的行，可以從這兩個表裡取出特定的行並自行定義腳本處理；

在ORACLE中， 對錶做一次DML操作產生一次觸發，叫語句級觸發器，另外還可以通過指定[FOR EACH ROW]子句，對於表中受影響的每行數據均觸發，叫行級觸發器，原有行用:OLD表示，新行用:NEW表示；

 

2. BEFORE/AFTER/INSTEAD OF

在SQL Server中，從定義來說只有AFTER/INSTEAD OF觸發器，在表上支持AFTER觸發器，在表/視圖上支持INSTEAD OF觸發器，對於BEFORE觸發器的需求可以嘗試通過INSEAD OF觸發器來實現；

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

√

√

VIEW

N/A

N/A

√

在ORACLE中，在表上支持BEFORE/AFTER觸發器，在視圖上支持INSTEAD OF觸發器，比如ORACLE中無法直接對視圖做DML操作，可以通過INSTEAD OF觸發器來變樣完成；

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

√

√

N/A

VIEW

N/A

N/A

√

 

3. 觸發條件

(1) 不能觸發的情況

對於UPDATE，DELETE操作而言，均會觸發觸發器；而對於INSERT或者說IMPORT的情況，是可以控制不去觸發的。

  • 大批量導入操作，如：BULK INSERT, bcp/INSERT... SELECT * FROM OPENROWSET，都有FIRE_TRIGGERS/IGNORE_TRIGGERS選項，可以設置是否觸發觸發器；
  • 導入導出嚮導/SSIS，如果目標是表，也有FIRE_TRIGGERS的設置選項；
  • 另外truncate操作也不會觸發；

(2) 嵌套觸發器 (Nested Triggers), 迴圈/遞歸觸發器 (Recursive Triggers)

嵌套觸發器，就是一次操作觸發了一個觸發器，然後觸發器里的語句繼續觸發其他觸發器，如果繼續回頭觸發了自己，那麼就是遞歸觸發器。

對於AFTER觸發器有個兩個開關分別控制嵌套觸發和遞歸觸發：

exec sp_configure 'nested triggers'

這個參數預設值為1， 也就是說允許AFTER觸發器嵌套，最多嵌套32層，設為0就是不允許AFTER觸發器嵌套，如下：

exec sp_configure 'nested triggers',0
RECONFIGURE

但這個參數有兩個另外：

  • INSTEAD OF觸發器，可以嵌套，不受這個參數開關與否影響；
  • AFTER觸發器，即使打開該選項，也不會自己嵌套自己(即遞歸)，除非打開了RECURSIVE_TRIGGERS選項，也就是迴圈/遞歸觸發器；
--create table, sql server 2016 & higher
drop table if exists A
GO
create table A(id int)
GO

--create DML trigger
drop trigger if exists tri_01
GO
create TRIGGER tri_01
ON A
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
end
GO

--check nested triggers server option
exec sp_configure 'nested triggers'
--name    minimum    maximum    config_value    run_value
--nested triggers    0    1    1    1

--test with RECURSIVE_TRIGGERS off
ALTER DATABASE dba set RECURSIVE_TRIGGERS off
select is_recursive_triggers_on, * from sys.databases 
GO
insert A values(1)
select * from A
--id
--1
--0

--test with RECURSIVE_TRIGGERS on
ALTER DATABASE dba set RECURSIVE_TRIGGERS on
select is_recursive_triggers_on, * from sys.databases 
GO

truncate table A
insert A values(1)
select * from A --32 rows

--如果沒有加@@NESTLEVEL判斷並退出，會出現32層限制的報錯，並且表裡不會插入任何數據
/*
Msg 217, Level 16, State 1, Procedure tri_01, Line 10
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

select * from A --0 rows
*/

--刪表會級聯刪除觸發器，就像索引
drop table A

 

迴圈/遞歸觸發器的前提就是嵌套觸發器，只有允許嵌套了才可以遞歸(遞歸也就是嵌套並觸發自己)，遞歸有直接和間接兩種情況：

  • 直接遞歸：就是A表的DML觸發器再回來對A表進行DML操作，如上例；
  • 間接遞歸：就是A表DML觸發器去操作B表，然後B表上觸發器回來操作A表，如下例；
--create table, sql server 2016 & higher
drop table if exists A
drop table if exists B
GO
create table A(id int)
create table B(id int)
GO

--create DML trigger
drop trigger if exists tri_01
drop trigger if exists tri_02
GO
create TRIGGER tri_01
ON A
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert B values(0)
end
GO

create TRIGGER tri_02
ON B
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
end
GO

--test with nested triggers server option ON
exec sp_configure 'nested triggers',1
RECONFIGURE

--test with RECURSIVE_TRIGGERS off
ALTER DATABASE dba set RECURSIVE_TRIGGERS off
select is_recursive_triggers_on, * from sys.databases 
GO

truncate table A
truncate table B
insert A values(1)
select * from A --16 rows
select * from B --16 rows

--test with RECURSIVE_TRIGGERS on
ALTER DATABASE dba set RECURSIVE_TRIGGERS on
select is_recursive_triggers_on, * from sys.databases 
GO

truncate table A
truncate table B
insert A values(1)
select * from A --16 rows
select * from B --16 rows

--test with nested triggers server option OFF
exec sp_configure 'nested triggers',0
RECONFIGURE

--test with RECURSIVE_TRIGGERS off
ALTER DATABASE dba set RECURSIVE_TRIGGERS off
select is_recursive_triggers_on, * from sys.databases 
GO

truncate table A
truncate table B
insert A values(1)
select * from A --1
select * from B --0

--test with RECURSIVE_TRIGGERS on
ALTER DATABASE dba set RECURSIVE_TRIGGERS on
select is_recursive_triggers_on, * from sys.databases 
GO

truncate table A
truncate table B
insert A values(1)
select * from A --1
select * from B --0

--刪表會級聯刪除觸發器，就像索引
drop table A, B
  • 可以看出資料庫選項RECURSIVE_TRIGGERS，僅對直接遞歸有效，對間接遞歸無效；可以通過Nest Triggers的開關來控制是否允許嵌套，從而控制是否允許間接遞歸；
  • 不論直接遞歸，還是間接遞歸，遞歸次數都有32次嵌套的上限；

總結下來：

1. AFTER觸發器，預設Nest Triggers值為1，即允許觸發器嵌套，上限32層，間接遞歸也是可以的，直接遞歸需要開啟資料庫選項RECURSIVE_TRIGGERS；

2. INSTEAD OF觸發器，不受Nest Triggers選項影響，均可以嵌套，上限32層，間接遞歸也是可以的，直接遞歸無論是否開啟資料庫選項RECUSIVE_TRIGGERS，都無效；把上面兩個腳本示例中的AFTER改為INSTEAD OF即可演示。

 

4. 觸發器中無法commit/rollback事務

--create table, sql server 2016 & higher
drop table if exists A
GO
create table A(id int)
GO

--create DML trigger
drop trigger if exists tri_01
GO
create TRIGGER tri_01
ON A
AFTER INSERT, UPDATE, DELETE 
as
begin
    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    commit
end
GO

begin tran
insert A values(1)
/*
Msg 3609, Level 16, State 1, Procedure tri_01, Line 10
The transaction ended in the trigger. The batch has been aborted.
*/

在SQL Server和Oracle中都是這樣，觸發器作為整個事務的一部分存在，但是並不控制整個事務的提交/回滾，為保證數據一致性，事務邏輯由觸發器外層的語句來控制。

 

二. DDL觸發器

SQL Server 2005開始支持DDL觸發器，它不只限於對CREATE/ALTER/DROP操作有效，支持的DDL事件還有比如：許可權的GRANT/DENY/REVOEK, 對象的RENAME, 更新統計信息等等，可通過DMV查看更多支持的事件類型如下：

select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'

註意：

1. TRUNCATE不在DDL觸發器的事件類型中，SQL Server中將Truncate 歸為DML操作語句，雖然它也並不觸發DML觸發器，就像開啟開關的大批量導入操作 (Bulk Import Operations) 一樣；

2. DDL觸發器中捕獲的信息都由EVENTDATA()函數返回，返回類型為XML格式，需要用XQuery來讀取；

 

代碼示例1：記錄所有table上的某些DDL操作

--記錄所有create table操作
if OBJECT_ID('ddl_log','U') is not null
    drop table ddl_log
GO

create table ddl_log
(
LogID        int identity(1,1),
EventType    varchar(50), 
ObjectName   varchar(256),
ObjectType   varchar(25),
TSQLCommand  varchar(max),
LoginName    varchar(256)
)
GO

if exists(select * from sys.triggers where name = 'TABLE_DDL_LOG' and parent_class_desc = 'DATABASE')
    drop trigger TABLE_DDL_LOG on database;
GO

create trigger TABLE_DDL_LOG
on database
for create_table
as
begin
    set nocount on 

    declare @data xml
    set @data = EVENTDATA()

    insert into ddl_log
    values
    (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
    )
end
GO

drop table if exists test_dll_trigger;
create table test_dll_trigger (id int)
select * from ddl_log

 

代碼示例2：禁止特定角色的用戶對特定的表做DROP操作

IF exists(select * from sys.triggers where name = 'NO_DROP_TABLE' and parent_class_desc = 'DATABASE')
    DROP TRIGGER [NO_DROP_TABLE] ON DATABASE;
GO

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    DECLARE @x                XML,
            @user_name        varchar(100),
            @db_name          varchar(100),  
            @schema_name      varchar(100),
            @object_name      varchar(200)

    --select eventdata()
    SET @x = EVENTDATA();
    SET @user_name = @x.value('(/EVENT_INSTANCE/UserName)[1]','varchar(100)');
    SET @db_name = @x.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)');
    SET @schema_name = @x.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)');
    SET @object_name = @x.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)');

    --PRINT 'Current User: '     + @user_name
    --PRINT 'Current Database: ' + @db_name
    --PRINT 'Schema Name: '      + @schema_name
    --PRINT 'Table Name: '       + @object_name

    IF is_rolemember('disallow_modify_tables',@user_name) = 1
       AND @db_name = 'YOUR_DB_NAME'
       AND @schema_name = 'YOUR_SCHEMA_NAME'
       AND @object_name like 'YOUR_TABLE_NAME%'
    BEGIN 
        PRINT 'Dropping tables is not allowed'
        ROLLBACK
    END
END
GO

 

三. LOGON 觸發器

SQL Server 2005在SP2中悄悄引入了LOGON觸發器，作為一個實例級的對象，它的系統視圖，定義語句和DDL/DML觸發器都是分開的。

select * from sys.server_triggers where name = 'login_history_trigger'
select * from sys.server_trigger_events
select OBJECT_ID('login_history_trigger') --無法獲取

在SQL Server中，顧名思義，LOGON觸發器，只支持LOGON事件；

在ORACLE中，實例級觸發器可支持更多事件 (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)。

 

代碼示例1： 記錄所有login登錄歷史 (其實也可以通過修改login auditing選項，來記錄成功和失敗的登錄在errorlog里)

IF OBJECT_ID('login_history','U') is not null
    DROP TABLE login_history
GO

CREATE TABLE login_history
(
FACT_ID         bigint IDENTITY(1,1) primary key,
LOGIN_NAME      nvarchar(1024),
LOGIN_TIME      datetime
)
GO

IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
    DROP TRIGGER login_history_trigger ON ALL SERVER
GO

CREATE TRIGGER login_history_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY\%' AND 
    --   SUSER_NAME() NOT LIKE 'NT SERVICE\%'
    IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY\%' AND
       ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE\%'
    BEGIN
        INSERT INTO DBA..login_history
        VALUES(ORIGINAL_LOGIN(),GETDATE());
    END;
END;
GO

--view login history after logon
SELECT * FROM login_history

 

代碼示例2： 限制特定用戶在特定時間範圍登錄、限制連接數

--限制下班時間不能登錄
DROP TRIGGER IF EXISTS limit_user_login_time ON ALL SERVER
GO
CREATE TRIGGER limit_user_login_time
ON ALL SERVER FOR LOGON 
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT 'TestUser can only login during working hours!'
        ROLLBACK
    END
END
GO

--限制連接數
DROP TRIGGER IF EXISTS limit_user_connections ON ALL SERVER
GO
CREATE TRIGGER limit_user_connections
ON ALL SERVER 
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (SELECT COUNT(*) FROM   sys.dm_exec_sessions
            WHERE  Is_User_Process = 1 
            AND Original_Login_Name = 'TestUser') > 2
    BEGIN
        PRINT 'TestUser can only have 1 active session!'
        ROLLBACK
    END
END

 

註意：如果LOGON觸發器把所有人都鎖在外面了怎麼辦？

Logon failed for login 'TestUser' due to trigger execution.

這時，只能通過DAC登錄SQL Server去禁用LOGON觸發器/修改邏輯以允許登錄，DAC登錄方式有遠程和本地兩種，遠程登錄需要通過sp_configure 開啟remote admin connections ，如果沒有事先開啟，那就只能選擇本地登錄方式：

伺服器本地，在SSMS中通過DAC登錄

 

伺服器本地，在cmd中通過DAC登錄

--禁用/啟用LOGON觸發器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL SERVER

 

參考：

CREATE TRIGGER (Transact-SQL)

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

Create Nested Triggers

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-nested-triggers?view=sql-server-2017

Transact-SQL statements

https://docs.microsoft.com/en-us/sql/t-sql/statements/statements?view=sql-server-2017

Why we can‘t use commit in trigger, can anyone give proper explanation

https://community.oracle.com/thread/1082134

Database PL/SQL Language Reference, Using Triggers

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020


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

-Advertisement-
Play Games
更多相關文章
  • 資料庫編程Case when
    資料庫編程題 1、 姓名 日期 是否上班 張三 星期二 是 張三 星期三 是 李四 星期一 是 王五 星期二 是 張三 星期二 是 寫出一條SQL語句輸出下列結果 姓名 星期一 星期二 星期三 張三 2 1 李四 1 王五 1 答案： select t.name,SUM(Case when t.da ...
  • SQL中# 與$ 的區別
    區別： （1）#將傳入的數據都當成一個字元串，會對自動傳入的數據加一個雙引號。如：order by #user_id#，如果傳入的值是id，則解析成的sql為order by "id"。 （2）$將傳入的數據直接顯示生成在sql中。如：order by $user_id$，如果傳入的值是id，則解析 ...
  • [轉]sp_OACreate WriteLine Writing nvarchar 中文漢字 非亂碼to a text file
    本文轉自：https://stackoverflow.com/questions/48135889/writing-nvarchar-to-a-text-file According to the Scripting.FileSystemObject documentation, the Creat ...
  • 資料庫 Like 用法
    查找列名等於某一字元串： select * from table where column like '%string%' 查找列名不等於某一字元串 select * from table where column not like '%string%' ...
  • 查詢每個部門的工資最高的同事的姓名，及工資是多少
    1 select t1.empno, t1.ename, t1.deptno, t1.sal 2 from emp t1 3 inner join ( 4 select t2.deptno, max(sal) max_sal 5 from emp t2 6 group by t2.deptno ) ... ...
  • SparkSQL大數據實戰：揭開Join的神秘面紗
    本文來自 網易雲社區 。 Join操作是資料庫和大數據計算中的高級特性，大多數場景都需要進行複雜的Join操作，本文從原理層面介紹了SparkSQL支持的常見Join演算法及其適用場景。 Join背景介紹 Join是資料庫查詢永遠繞不開的話題，傳統查詢SQL技術總體可以分為簡單操作（過濾操作-wher ...
  • 猛獁機器學習開發實踐
    本文來自 網易雲社區 。 Dolphin 是猛獁平臺里的一個機器學習功能模塊，提供給數據科學家進行機器學習的演算法開發、模型訓練和服務發佈，提供分散式全功能深度學習框架，易學易用，高效靈活，支持 Tensorflow、MXNet、Caffe、Spark 等多種機器或深度學習框架，最大可能的挖掘出數據的 ...
  • ORA-01722:invalid number
    今天查詢表的時候報無效的數字。查看表，都是varchar。 最後發現在進行關聯表操作的時候，其中一個id的類型是number。正常情況下，字元串和數字的比較是可以的。這裡只能給數字加上to_char解決問題。 出現這個問題，一定是存在number欄位，仔細查看 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類