In this article, we will review triggers in SQL Server, different types of trigger events, trigger order and NOT FOR REPLICATION in triggers. A trigger is a database object that runs automatically when an event occurs. There are three different types of events.
在本文中,我们将回顾SQL Server中的触发器,各种类型的触发器事件,触发器顺序以及触发器中的NOT FOR REPLICATION。 触发器是发生事件时自动运行的数据库对象。 有三种不同类型的事件。
- DML Events DML活动
- DDL Events DDL活动
- – Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established –发生LOGON事件时即建立用户会话时,将触发登录触发器
SQL Server中的DML触发器 (DML Triggers in SQL Server)
DML triggers in SQL Server are fired when a DML event occurs. i.e. when data is inserted/ updated/deleted in the table by a user.
当发生DML事件时,将触发SQL Server中的DML触发器。 即,当用户在表中插入/更新/删除数据时。
为DML事件创建触发器 (Creating triggers for a DML event)
Let us create some sample tables and triggers in SQL Server.
让我们在SQL Server中创建一些示例表和触发器。
CREATE TABLE Locations (LocationID int, LocName varchar(100))
CREATE TABLE LocationHist (LocationID int, ModifiedDate DATETIME)
We can create a DML trigger for a specific event or multiple events. The triggers in SQL Server(DML) fire on events irrespective to the number of rows affected.
我们可以为一个或多个特定事件创建DML触发器。 不管受影响的行数如何,SQL Server(DML)中的触发器都会触发事件。
Below is the sample syntax for creating a DML trigger for update event.
以下是为更新事件创建DML触发器的示例语法。
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
These triggers are created at the table level. Upon successful creation of trigger, we can see the triggers by navigating to Triggers folder at table level. Please refer to the below image.
这些触发器在表级别创建。 成功创建触发器后,我们可以通过导航到表级别的Triggers文件夹来查看触发器。 请参考下图。
而不是SQL Server中的触发器 (Instead of triggers in SQL Server)
These triggers are fired before the DML event and the actual data is not modified in the table.
这些触发器在DML事件之前触发,并且表中未修改实际数据。
For example, if we specify an instead of trigger for delete on a table, when delete statement is issued against the table, the instead of trigger is fired and the T-SQL block inside the triggers in SQL Server is executed but the actual delete does not happen.
例如,如果我们为表上的删除指定了一个代替触发器,则对表发出delete语句时,将触发代替触发器,并执行SQL Server中触发器内的T-SQL块,但实际删除确实不会发生。
T-SQL Syntax for creating an instead of trigger
用于创建触发器而不是触发器的T-SQL语法
CREATE TRIGGER TR_DEL_Locations ON Locations
INSTEAD OF DELETE
AS
BEGIN
Select 'Sample Instead of trigger' as [Message]
END
- If there are multiple triggers along with instead of trigger on the table, the instead of trigger is fired first in the order 如果表上有多个触发器,而不是触发器,那么将先按顺序触发代替触发器
- INSTEAD of triggers can be created on views 可以在视图上创建INSTEAD触发器
- we can define only one instead of trigger per INSERT, UPDATE, or DELETE statement on a table or view 我们只能在一个表或视图上为每个INSERT,UPDATE或DELETE语句定义一个触发器,而不是触发器
在表上启用和禁用DML触发器 (Enabling and disabling DML triggers on a table)
Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable/Disable to Enable or disable the trigger using SSMS.
导航到表级别的触发器文件夹,选择触发器,右键单击触发器,然后单击启用 / 禁用以使用SSMS启用或禁用触发器。
Disabling specific SQL Server trigger on a table using T-SQL.
使用T-SQL在表上禁用特定SQL Server触发器。
DISABLE TRIGGER TR_UPD_Locations2 on Locations
Enabling specific trigger on the table using T-SQL.
使用T-SQL在表上启用特定触发器。
ENABLE TRIGGER TR_UPD_Locations2 on Locations
To enable all triggers on a table, use below syntax.
要启用表上的所有触发器,请使用以下语法。
ENABLE TRIGGER ALL ON Locations
To disable all triggers on a table, use below syntax. This statement is not supported if the table is part of merge replication.
要禁用表上的所有触发器,请使用以下语法。 如果表是合并复制的一部分,则不支持此语句。
DISABLE TRIGGER ALL ON Locations
将触发器放在桌子上。 (Dropping a trigger on a table.)
To drop a DML trigger on the table using SQL Server management studio, navigate to the Triggers folder under the table. Select the table you want to drop, Right click on the trigger and click on Delete. Click Ok.
要使用SQL Server Management Studio在表上放置DML触发器,请导航到表下的Triggers文件夹。 选择要删除的表,右键单击触发器,然后单击“ 删除” 。 单击确定 。
T-SQL to drop a trigger on the table.
T-SQL在表上放置触发器。
DROP TRIGGER TRL_UPD_Locations2
Dropping a table will drop all the SQL Server triggers on the table along with the table.
删除表将同时删除表上的所有SQL Server触发器和表。
DDL触发器 (DDL Triggers)
DDL triggers in SQL Server are fired on DDL events. i.e. against create, alter and drop statements, etc. These triggers are created at the database level or server level based on the type of DDL event.
SQL Server中的DDL触发器在DDL事件上触发。 例如,针对create,alter和drop语句等。这些触发器是根据DDL事件的类型在数据库级别或服务器级别创建的。
These triggers are useful in the below cases.
这些触发器在以下情况下很有用。
- Prevent changes to the database schema 防止更改数据库架构
- Audit database schema changes 审核数据库架构更改
- To respond to a change in the database schema 响应数据库架构中的更改
创建一个DDL触发器 (Creating a DDL trigger)
Below is the sample syntax for creating a DDL trigger for ALTER TABLE event on a database which records all the alter statements against the table. You can write your custom code to track or audit the schema changes using EVENTDATA().
下面是在数据库上为ALTER TABLE事件创建DDL触发器的示例语法,该数据库记录了对该表的所有alter语句。 您可以使用EVENTDATA()编写自定义代码以跟踪或审核模式更改。
CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)
CREATE TRIGGER TR_ALTERTABLE ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
INSERT INTO TableSchemaChanges
SELECT EVENTDATA(),GETDATE()
END
You can specify an event group which consists of different DDL events. If we specify an event group while creating a DDL trigger, the trigger is fired when a DDL event in the group occurs.
您可以指定一个由不同的DDL事件组成的事件组。 如果在创建DDL触发器时指定事件组,则在组中发生DDL事件时将触发该触发器。
For example, if we want to create a trigger for all DDL events at the database level, we can just specify the DDL_DATABASE_LEVEL_EVENTS event group as shown in the below image.
例如,如果我们要为数据库级别的所有DDL事件创建触发器,则只需指定DDL_DATABASE_LEVEL_EVENTS事件组,如下图所示。
To view database level triggers, Login to the server using SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers.
若要查看数据库级别的触发器,请使用SQL Server Management Studio登录到服务器并导航到数据库。 展开数据库,然后导航到Programmability- > Database Triggers。
To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder.
要在服务器级别查看触发器,请使用SSMS登录到服务器,然后导航到“ 服务器对象” ,然后导航到“ 触发器”文件夹。
启用和禁用DDL触发器 (Enabling and disabling DDL triggers)
Use below T-SQL syntax to disable or enable the DDL trigger at the database level.
使用下面的T-SQL语法在数据库级别禁用或启用DDL触发器。
ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE
GO
Use below T-SQL syntax to drop a DDL trigger which is created at the database level.
使用下面的T-SQL语法删除在数据库级别创建的DDL触发器。
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE
SQL Server中的LOGON触发器 (LOGON Triggers in SQL Server)
These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.
SQL Server中的这些触发器将响应LOGON事件而触发。 成功认证之后且建立用户会话之前,LOGON会触发触发。
LOGON triggers are created at the server level and are useful below cases.
LOGON触发器在服务器级别创建,在以下情况下很有用。
- To audit login activity 审核登录活动
- To control the login activity 控制登录活动
创建登录触发器 (Creating LOGON triggers)
You can use EVENTDATA() and write your custom code to track or control the connections. Here I am creating simple triggers in SQL Server for LOGON event. Below is the sample syntax for creating a LOGON trigger.
您可以使用EVENTDATA()并编写自定义代码来跟踪或控制连接。 在这里,我正在SQL Server中为LOGON事件创建简单的触发器。 以下是用于创建LOGON触发器的示例语法。
CREATE TABLE LoginActivity (LOGONEvent XML ,Logintime datetime)
CREATE TRIGGER [track_logins] ON ALL SERVER
FOR LOGON AS
BEGIN
INSERT INTO LoginActivity
SELECT EVENTDATA()
,GETDATE()
END
We must be cautious while creating these triggers as login may fail if the trigger execution fails or if you do not have access to objects referenced in the LOGON trigger. In such cases, the only member of the sysadmin role can connect to the server using a dedicated administrator connection. So, it is always better to enable dedicated administrator connection when using these triggers.
创建这些触发器时,我们必须谨慎,因为如果触发器执行失败或您无法访问LOGON触发器中引用的对象,登录可能会失败。 在这种情况下,sysadmin角色的唯一成员可以使用专用管理员连接连接到服务器。 因此,使用这些触发器时最好启用专用的管理员连接。
启用和禁用LOGON触发器 (Enabling and disabling LOGON triggers)
Use below T-SQL syntax to disable or enable the LOGON trigger.
使用下面的T-SQL语法来禁用或启用LOGON触发器。
ENABLE TRIGGER track_logins ON ALL SERVER
GO
DISABLE TRIGGER track_logins ON ALL SERVER
GO
Use below T-SQL syntax to drop a LOGON trigger.
使用下面的T-SQL语法删除LOGON触发器。
DROP TRIGGER track_logins ON ALL SERVER
直接递归 (Direct recursion)
Direct recursion is a case where the SQL Server trigger on the table is fired and performs an action which again triggers the same trigger.
直接递归是在表上触发SQL Server触发器并执行再次触发同一触发器的操作的情况。
For example, please refer to below sample trigger for an update which is direct recursive.
例如,请参考下面的示例触发器以获取直接递归的更新。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Locations](
[LocationID] [int] NULL,
[LocName] [varchar](100) NULL,
DateUpdated datetime
) ON [PRIMARY]
GO
INSERT INTO Locations VALUES(1,'Richmond Road', NULL)
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
AS
BEGIN
Update Locations set DateUpdated =GETDATE()
END
Direct recursion can be controlled by a database setting RECURSIVE_TRIGGERS. If the setting is on, then the above trigger throws an error.
可以通过数据库设置RECURSIVE_TRIGGERS来控制直接递归。 如果设置为开,则上述触发器将引发错误。
If the database setting RECURSIVE_TRIGGERS is off, then the trigger is fired only once and does not loop.
如果数据库设置RECURSIVE_TRIGGERS关闭,则触发器仅触发一次,并且不会循环。
To change RECURSIVE_TRIGGERS setting using SSMS, navigate to the database, right click on the database and select Properties. Click on Options and change the setting to the option you want.
要使用SSMS更改RECURSIVE_TRIGGERS设置,请导航到数据库,右键单击数据库并选择“ 属性”。 单击选项,然后将设置更改为所需的选项。
To set the RECURSIVE_TRIGGERS OFF using T-SQL, use below statement and replace the database name with your database name.
要使用T-SQL将RECURSIVE_TRIGGERS设置为OFF,请使用以下语句,并将数据库名称替换为您的数据库名称。
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
To set the RECURSIVE_TRIGGERS ON using T-SQL, use below statement and replace the database name with your database name.
要使用T-SQL将RECURSIVE_TRIGGERS设置为ON,请使用以下语句,并将数据库名称替换为您的数据库名称。
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT
GO
间接递归 (Indirect Recursion)
This is a case where a trigger is fired and invokes another trigger of the same type.
这是触发一个触发器并调用另一个相同类型的触发器的情况。
Below is the sample trigger for indirect recursion.
以下是间接递归的示例触发器。
CREATE TABLE Temp1 (id int)
GO
INSERT INTO Temp1 values (1),(2)
GO
CREATE TABLE Temp2 (id int)
GO
INSERT INTO Temp2 values (1),(2)
GO
CREATE TRIGGER TR_Temp1 on Temp1
FOR UPDATE
AS
BEGIN
UPDATE TEMP2 set ID ='5' where id in (select id from inserted)
END
GO
CREATE TRIGGER TR_Temp2 on Temp2
FOR UPDATE
AS
BEGIN
UPDATE Temp1 set ID ='5' where id in (select id from inserted)
END
Now when we update a value in the table Temp1, the trigger TR_Temp1 is fired which updates Temp2 table. TR_Temp2 is fired and updates Temp1 table which causes TR_Temp1 to fire again.
现在,当我们更新表Temp1中的值时,将触发触发器TR_Temp1来更新Temp2表。 将触发TR_Temp2并更新Temp1表,这将导致TR_Temp1再次触发。
This behavior can be controlled by setting nested triggers off.
可以通过将嵌套触发器设置为关闭来控制此行为。
EXEC sp_configure 'nested triggers', 0 ;
GO
SQL Server触发顺序 (SQL Server trigger order)
SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers.
SQL Server允许在同一事件的表上使用多个触发器,并且这些触发器没有定义的执行顺序。
We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.
我们可以使用过程sp_settriggerorder将触发器的顺序设置为第一个或最后一个。 一个表上的每个语句只能有一个第一个或最后一个触发器。
Below is the sample syntax for setting the order of trigger to first for the INSERT statement.
下面是将INSERT语句的触发顺序设置为first的示例语法。
CREATE TABLE TriggerOrderTest (id int)
GO
CREATE TRIGGER TR_1 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT 'First Trigger'
END
GO
CREATE TRIGGER TR_2 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT 'Second Trigger'
END
GO
CREATE TRIGGER TR_3 ON TriggerOrderTest
FOR INSERT
as
BEGIN
PRINT 'Third Trigger'
END
GO
sp_settriggerorder @triggername ='TR_3'
, @order = 'FIRST'
, @stmttype = 'INSERT'
Now, when the data is inserted into the table “TriggerOrderTest” INSERT event occurs and the trigger TR_3 fires first.
现在,将数据插入表“ TriggerOrderTest”时,将发生INSERT事件,并且首先触发触发器TR_3。
In case DDL triggers we must specify the namespace parameter which is the scope of the SQL Server trigger in the stored procedure sp_settriggerorder.
如果是DDL触发器,则必须在存储过程sp_settriggerorder中指定namespace参数,该参数是SQL Server触发器的范围。
Below is the sample syntax for setting the DDL trigger order.
以下是用于设置DDL触发顺序的示例语法。
sp_settriggerorder @triggername ='DDL_3'
, @order = 'FIRST'
, @stmttype = 'ALTER_TABLE'
, @namespace = 'DATABASE'
不用于复制 (NOT FOR REPLICATION)
NOT FOR REPLICATION indicates that the trigger should not fire when the replication agent syncs the data changes to the subscriber.
NOT FOR REPLICATION表示复制代理将数据更改同步到订阅服务器时,不应触发该触发器。
For example, if you are replicating both Locations and LocationHist. Now when you update a record on Location the trigger is fired, inserts record in the history table. When these changes sync to another end (subscribers) there is no need of trigger to be fired again. So, if we mark the trigger for “NOT FOR REPLICATION” the trigger does not fire when replication agent sync’s the changes and fires only for the data changes done by the user.
例如,如果您要同时复制Locations和LocationHist。 现在,当您更新Location上的记录时,将触发触发器,将记录插入历史记录表中。 当这些更改同步到另一端(订户)时,无需再次触发触发器。 因此,如果我们将触发器标记为“ NOT FOR REPLICATION”,则当复制代理同步更改时,触发器不会触发,并且仅针对用户所做的数据更改而触发。
Below is the sample syntax to create a triggers in SQL Server with not for replication.
下面是在SQL Server中创建不用于复制的触发器的示例语法。
CREATE TRIGGER TR_UPD_Locations ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationHist
SELECT LocationID
,getdate()
FROM inserted
END
If you want the triggers in SQL Server to be fired when the replication agent sync data changes to another end, just create the trigger without specifying “NOT FOR REPLICATION”.
如果要在复制代理同步数据更改为另一端时触发SQL Server中的触发器,只需创建触发器而不指定“ NOT FOR REPLICATION”。