\u2013 <\/strong>Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established\n <\/li>\n<\/ul>\nDML Triggers in SQL Server<\/h2>\n \n 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.\n<\/p>\n
Creating triggers for a DML event<\/h3>\n \n Let us create some sample tables and triggers in SQL Server.\n<\/p>\n
CREATE TABLE Locations (LocationID int, LocName varchar(100))\r\n\r\nCREATE TABLE LocationHist (LocationID int, ModifiedDate DATETIME)<\/pre>\n<\/p>\n\n 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.\n<\/p>\n
\n Below is the sample syntax for creating a DML trigger for update event.\n<\/p>\n
CREATE TRIGGER TR_UPD_Locations ON Locations\r\nFOR UPDATE \r\nNOT FOR REPLICATION \r\nAS\r\n\r\nBEGIN\r\n INSERT INTO LocationHist\r\n SELECT LocationID\r\n ,getdate()\r\n FROM inserted\r\nEND<\/pre>\n<\/p>\n\n \n These triggers are created at the table level. Upon successful creation of trigger, we can see the triggers by navigating to Triggers<\/strong> folder at table level. Please refer to the below image.\n<\/p>\n\n Instead of triggers in SQL Server<\/h3>\n \n These triggers are fired before the DML event and the actual data is not modified in the table.\n<\/p>\n
\n 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.\n<\/p>\n
\n T-SQL Syntax for creating an instead of trigger\n<\/p>\n
CREATE TRIGGER TR_DEL_Locations ON Locations\r\nINSTEAD OF DELETE\r\nAS\r\nBEGIN\r\n Select 'Sample Instead of trigger' as [Message]\r\nEND<\/pre>\n<\/p>\n\n \n\n If there are multiple triggers along with instead of trigger on the table, the instead of trigger is fired first in the order\n <\/li>\n \n INSTEAD of triggers can be created on views\n <\/li>\n \n we can define only one instead of trigger per INSERT, UPDATE, or DELETE statement on a table or view\n <\/li>\n<\/ul>\nEnabling and disabling DML triggers on a table<\/h3>\n \n Navigate to triggers folder at the table level, select the trigger, Right click on trigger and Click on Enable<\/strong>\/Disable<\/strong> to Enable or disable the trigger using SSMS<\/strong>.\n<\/p>\n\n Disabling specific SQL Server trigger on a table using T-SQL.\n<\/p>\n
DISABLE TRIGGER TR_UPD_Locations2 on Locations<\/pre>\n<\/p>\n\n \n Enabling specific trigger on the table using T-SQL.\n<\/p>\n
ENABLE TRIGGER TR_UPD_Locations2 on Locations<\/pre>\n<\/p>\n\n To enable all triggers on a table, use below syntax.\n<\/p>\n
ENABLE TRIGGER ALL ON Locations<\/pre>\n<\/p>\n\n To disable all triggers on a table, use below syntax. This statement is not supported if the table is part of merge replication.\n<\/p>\n
DISABLE TRIGGER ALL ON Locations<\/pre>\n<\/p>\nDropping a trigger on a table.<\/h3>\n \n To drop a DML trigger on the table using SQL Server management studio, navigate to the Triggers<\/strong> folder under the table. Select the table you want to drop, Right click on the trigger and click on Delete<\/strong>. Click Ok<\/strong>.\n<\/p>\n\n \n T-SQL to drop a trigger on the table.\n<\/p>\n
DROP TRIGGER TRL_UPD_Locations2<\/pre>\n<\/p>\n\n Dropping a table will drop all the SQL Server triggers on the table along with the table.\n<\/p>\n
DDL Triggers<\/h2>\n \n 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.\n<\/p>\n
\n These triggers are useful in the below cases.\n<\/p>\n
\n\n Prevent changes to the database schema\n <\/li>\n \n Audit database schema changes\n <\/li>\n \n To respond to a change in the database schema\n <\/li>\n<\/ul>\nCreating a DDL trigger<\/h3>\n \n 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().\n<\/p>\n
CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime)\r\n\r\nCREATE TRIGGER TR_ALTERTABLE ON DATABASE\r\nFOR ALTER_TABLE\r\nAS\r\nBEGIN\r\n\r\nINSERT INTO TableSchemaChanges\r\nSELECT EVENTDATA(),GETDATE()\r\n\r\nEND<\/pre>\n<\/p>\n\n \n 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.\n<\/p>\n
\n 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.\n<\/p>\n
\n \n 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<\/strong> -> Database Triggers.<\/strong>\n<\/p>\n\n \n To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects<\/strong> and then Triggers<\/strong> folder.\n<\/p>\n\n Enabling and disabling DDL triggers<\/h3>\n \n Use below T-SQL syntax to disable or enable the DDL trigger at the database level.\n<\/p>\n
ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASE\r\nGO\r\n\r\nDISABLE TRIGGER TR_DATABASEEVENTS ON DATABASE\r\nGO<\/pre>\n<\/p>\n\n Use below T-SQL syntax to drop a DDL trigger which is created at the database level.\n<\/p>\n
DROP TRIGGER TR_DATABASEEVENTS ON DATABASE<\/pre>\n<\/p>\nLOGON Triggers in SQL Server<\/h2>\n \n These triggers in SQL Server fire in response to a LOGON event. LOGON triggers fire after successful authentication and before establishing the user session.\n<\/p>\n
\n LOGON triggers are created at the server level and are useful below cases.\n<\/p>\n
\n\n To audit login activity\n<\/li>\n \n To control the login activity\n<\/li>\n<\/ol>\nCreating LOGON triggers<\/h3>\n \n 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.\n<\/p>\n
CREATE TABLE LoginActivity (LOGONEvent XML ,Logintime datetime)\r\n\r\nCREATE TRIGGER [track_logins] ON ALL SERVER\r\nFOR LOGON AS\r\n\r\nBEGIN\r\n INSERT INTO LoginActivity\r\n SELECT EVENTDATA()\r\n ,GETDATE()\r\nEND<\/pre>\n<\/p>\n\n 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.\n<\/p>\n
Enabling and disabling LOGON triggers<\/h3>\n \n Use below T-SQL syntax to disable or enable the LOGON trigger.\n<\/p>\n
ENABLE TRIGGER track_logins ON ALL SERVER\r\nGO\r\n\r\nDISABLE TRIGGER track_logins ON ALL SERVER\r\nGO<\/pre>\n<\/p>\n\n Use below T-SQL syntax to drop a LOGON trigger.\n<\/p>\n
DROP TRIGGER track_logins ON ALL SERVER<\/pre>\n<\/p>\nDirect recursion<\/h2>\n \n 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.\n<\/p>\n
\n For example, please refer to below sample trigger for an update which is direct recursive.\n<\/p>\n
SET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nCREATE TABLE [dbo].[Locations](\r\n [LocationID] [int] NULL,\r\n [LocName] [varchar](100) NULL,\r\n DateUpdated datetime\r\n) ON [PRIMARY]\r\nGO\r\n\r\nINSERT INTO Locations VALUES(1,'Richmond Road', NULL)\r\n\r\nCREATE TRIGGER TR_UPD_Locations ON Locations\r\nFOR UPDATE \r\nAS\r\n\r\nBEGIN\r\n Update Locations set DateUpdated =GETDATE()\r\nEND<\/pre>\n<\/p>\n\n Direct recursion can be controlled by a database setting RECURSIVE_TRIGGERS<\/strong>. If the setting is on, then the above trigger throws an error.\n<\/p>\n\n \n If the database setting RECURSIVE_TRIGGERS is off, then the trigger is fired only once and does not loop.\n<\/p>\n
\n \n To change RECURSIVE_TRIGGERS setting using SSMS, navigate to the database, right click on the database and select Properties. <\/strong>Click on Options<\/strong> and change the setting to the option you want.\n<\/p>\n\n \n To set the RECURSIVE_TRIGGERS OFF using T-SQL, use below statement and replace the database name with your database name.\n<\/p>\n
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT\r\nGO<\/pre>\n<\/p>\n\n To set the RECURSIVE_TRIGGERS ON using T-SQL, use below statement and replace the database name with your database name.\n<\/p>\n
ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS ON WITH NO_WAIT\r\nGO<\/pre>\n<\/p>\nIndirect Recursion<\/h2>\n \n This is a case where a trigger is fired and invokes another trigger of the same type.\n<\/p>\n
\n Below is the sample trigger for indirect recursion.\n<\/p>\n
CREATE TABLE Temp1 (id int)\r\nGO\r\n\r\nINSERT INTO Temp1 values (1),(2)\r\nGO\r\n\r\nCREATE TABLE Temp2 (id int)\r\nGO\r\n\r\nINSERT INTO Temp2 values (1),(2)\r\nGO\r\n\r\nCREATE TRIGGER TR_Temp1 on Temp1\r\nFOR UPDATE \r\nAS\r\nBEGIN\r\nUPDATE TEMP2 set ID ='5' where id in (select id from inserted)\r\nEND\r\nGO\r\n\r\nCREATE TRIGGER TR_Temp2 on Temp2\r\nFOR UPDATE \r\nAS\r\nBEGIN\r\nUPDATE Temp1 set ID ='5' where id in (select id from inserted)\r\nEND<\/pre>\n<\/p>\n\n 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.\n<\/p>\n
\n \n This behavior can be controlled by setting nested triggers<\/strong> off.\n<\/p>\n
EXEC sp_configure 'nested triggers', 0 ; \r\nGO<\/pre>\n<\/p>\nSQL Server trigger order<\/h2>\n \n SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers.\n<\/p>\n
\n 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.\n<\/p>\n
\n Below is the sample syntax for setting the order of trigger to first for the INSERT statement.\n<\/p>\n
CREATE TABLE TriggerOrderTest (id int)\r\nGO\r\n\r\nCREATE TRIGGER TR_1 ON TriggerOrderTest\r\nFOR INSERT\r\nas\r\nBEGIN\r\nPRINT 'First Trigger'\r\nEND\r\nGO\r\n\r\nCREATE TRIGGER TR_2 ON TriggerOrderTest\r\nFOR INSERT\r\nas\r\nBEGIN\r\nPRINT 'Second Trigger'\r\nEND\r\nGO\r\n\r\nCREATE TRIGGER TR_3 ON TriggerOrderTest\r\nFOR INSERT\r\nas\r\nBEGIN\r\nPRINT 'Third Trigger'\r\nEND\r\nGO\r\n\r\nsp_settriggerorder @triggername ='TR_3'\r\n , @order = 'FIRST' \r\n , @stmttype = 'INSERT'<\/pre>\n<\/p>\n\n Now, when the data is inserted into the table \u201cTriggerOrderTest\u201d INSERT event occurs and the trigger TR_3 fires first.\n<\/p>\n
\n \n 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.\n<\/p>\n
\n Below is the sample syntax for setting the DDL trigger order.\n<\/p>\n
sp_settriggerorder @triggername ='DDL_3'\r\n , @order = 'FIRST' \r\n , @stmttype = 'ALTER_TABLE' \r\n , @namespace = 'DATABASE'<\/pre>\n<\/p>\nNOT FOR REPLICATION<\/h2>\n<\/p>\n \n NOT FOR REPLICATION indicates that the trigger should not fire when the replication agent syncs the data changes to the subscriber.\n<\/p>\n
\n 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 \u201cNOT FOR REPLICATION\u201d the trigger does not fire when replication agent sync\u2019s the changes and fires only for the data changes done by the user.\n<\/p>\n
\n Below is the sample syntax to create a triggers in SQL Server with not for replication.\n<\/p>\n
CREATE TRIGGER TR_UPD_Locations ON Locations\r\nFOR UPDATE \r\nNOT FOR REPLICATION \r\nAS\r\n\r\nBEGIN\r\n INSERT INTO LocationHist\r\n SELECT LocationID\r\n ,getdate()\r\n FROM inserted\r\nEND<\/pre>\n<\/p>\n\n 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 \u201cNOT FOR REPLICATION\u201d.\n<\/p>\n
![\"DML](\"data:image\/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==\")
![\"DML](\"\/wp-content\/uploads\/2019\/05\/dml-trigger-for-update-event.png\")