触发器可以理解为由特定事件触发的存储过程， 和存储过程、函数一样，触发器也支持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',0RECONFIGURE
但这个参数有两个另外：
INSTEAD OF触发器，可以嵌套，不受这个参数开关与否影响；
AFTER触发器，即使打开该选项，也不会自己嵌套自己(即递归)，除非打开了RECURSIVE_TRIGGERS选项，也就是循环/递归触发器；
--create table, sql server 2016 & higherdrop table if exists AGOcreate table A(id int)GO--create DML triggerdrop trigger if exists tri_01GOcreate TRIGGER tri_01ON A AFTER INSERT, UPDATE, DELETE asbegin if @@NESTLEVEL = 32 begin return end insert A values(0)endGO--check nested triggers server optionexec sp_configure 'nested triggers'--name minimum maximum config_value run_value--nested triggers 0 1 1 1--test with RECURSIVE_TRIGGERS offALTER DATABASE dba set RECURSIVE_TRIGGERS offselect is_recursive_triggers_on, * from sys.databases GOinsert A values(1)select * from A--id--1--0--test with RECURSIVE_TRIGGERS onALTER DATABASE dba set RECURSIVE_TRIGGERS onselect is_recursive_triggers_on, * from sys.databases GOtruncate table Ainsert 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 & higherdrop table if exists Adrop table if exists BGOcreate table A(id int)create table B(id int)GO--create DML triggerdrop trigger if exists tri_01drop trigger if exists tri_02GOcreate TRIGGER tri_01ON A AFTER INSERT, UPDATE, DELETE asbegin if @@NESTLEVEL = 32 begin return end insert B values(0)endGOcreate TRIGGER tri_02ON B AFTER INSERT, UPDATE, DELETE asbegin if @@NESTLEVEL = 32 begin return end insert A values(0)endGO--test with nested triggers server option ONexec sp_configure 'nested triggers',1RECONFIGURE--test with RECURSIVE_TRIGGERS offALTER DATABASE dba set RECURSIVE_TRIGGERS offselect is_recursive_triggers_on, * from sys.databases GOtruncate table Atruncate table Binsert A values(1)select * from A --16 rowsselect * from B --16 rows--test with RECURSIVE_TRIGGERS onALTER DATABASE dba set RECURSIVE_TRIGGERS onselect is_recursive_triggers_on, * from sys.databases GOtruncate table Atruncate table Binsert A values(1)select * from A --16 rowsselect * from B --16 rows--test with nested triggers server option OFFexec sp_configure 'nested triggers',0RECONFIGURE--test with RECURSIVE_TRIGGERS offALTER DATABASE dba set RECURSIVE_TRIGGERS offselect is_recursive_triggers_on, * from sys.databases GOtruncate table Atruncate table Binsert A values(1)select * from A --1select * from B --0--test with RECURSIVE_TRIGGERS onALTER DATABASE dba set RECURSIVE_TRIGGERS onselect is_recursive_triggers_on, * from sys.databases GOtruncate table Atruncate table Binsert A values(1)select * from A --1select * 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 & higherdrop table if exists AGOcreate table A(id int)GO--create DML triggerdrop trigger if exists tri_01GOcreate TRIGGER tri_01ON A AFTER INSERT, UPDATE, DELETE asbegin if @@NESTLEVEL = 32 begin return end insert A values(0) commitendGObegin traninsert 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_typeswhere 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_logGOcreate table ddl_log
(
LogID int identity(1,1),
EventType varchar(50),
ObjectName varchar(256),
ObjectType varchar(25),
TSQLCommand varchar(max),
LoginName varchar(256)
)GOif exists(select * from sys.triggers where name = 'TABLE_DDL_LOG' and parent_class_desc = 'DATABASE') drop trigger TABLE_DDL_LOG on database;GOcreate trigger TABLE_DDL_LOGon databasefor create_tableasbegin
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)')
)endGOdrop 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;GOCREATE TRIGGER NO_DROP_TABLEON DATABASEFOR DROP_TABLEASBEGIN
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
ENDENDGO
三. LOGON 触发器
SQL Server 2005在SP2中悄悄引入了LOGON触发器，作为一个实例级的对象，它的系统视图，定义语句和DDL/DML触发器都是分开的。
select * from sys.server_triggers where name = 'login_history_trigger'select * from sys.server_trigger_eventsselect 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_historyGOCREATE TABLE login_history
(
FACT_ID bigint IDENTITY(1,1) primary key,
LOGIN_NAME nvarchar(1024),
LOGIN_TIME datetime)GOIF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger') DROP TRIGGER login_history_trigger ON ALL SERVERGOCREATE TRIGGER login_history_triggerON ALL SERVERFOR LOGONASBEGIN
--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 logonSELECT * FROM login_history
代码示例2： 限制特定用户在特定时间范围登录、限制连接数
--限制下班时间不能登录DROP TRIGGER IF EXISTS limit_user_login_time ON ALL SERVERGOCREATE TRIGGER limit_user_login_timeON ALL SERVER FOR LOGON ASBEGIN IF ORIGINAL_LOGIN() = 'TestUser' AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART (HOUR, GETDATE()) > 18) BEGIN PRINT 'TestUser can only login during working hours!' ROLLBACK ENDENDGO--限制连接数DROP TRIGGER IF EXISTS limit_user_connections ON ALL SERVERGOCREATE TRIGGER limit_user_connectionsON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN 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 ENDEND
注意：如果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)
Create Nested Triggers
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
随时随地看视频
