Triggers in SQL Server
SQLShack
SQL Server training Español Triggers in SQL Server
May 15, 2019 by Ranga Babu 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. DML Events DDL Events LOGON Event – Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established 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. Creating triggers for a DML event
Let us create some sample tables and triggers in SQL Server. 123 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. Below is the sample syntax for creating a DML trigger for update event. 1234567891011 CREATE TRIGGER TR_UPD_Locations ON LocationsFOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHist SELECT LocationID ,getdate() FROM insertedEND 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. Instead of triggers in SQL Server
These triggers are fired before the DML event and the actual data is not modified in the table. 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. T-SQL Syntax for creating an instead of trigger 123456 CREATE TRIGGER TR_DEL_Locations ON LocationsINSTEAD OF DELETEASBEGIN 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 we can define only one instead of trigger per INSERT, UPDATE, or DELETE statement on a table or view 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. Disabling specific SQL Server trigger on a table using T-SQL. 1 DISABLE TRIGGER TR_UPD_Locations2 on Locations Enabling specific trigger on the table using T-SQL. 1 ENABLE TRIGGER TR_UPD_Locations2 on Locations To enable all triggers on a table, use below syntax. 1 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. 1 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. T-SQL to drop a trigger on the table. 1 DROP TRIGGER TRL_UPD_Locations2 Dropping a table will drop all the SQL Server triggers on the table along with the table. 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. 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 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(). 1234567891011 CREATE TABLE TableSchemaChanges (ChangeEvent xml, DateModified datetime) CREATE TRIGGER TR_ALTERTABLE ON DATABASEFOR ALTER_TABLEASBEGIN INSERT INTO TableSchemaChangesSELECT 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. 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. 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. To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder. Enabling and disabling DDL triggers
Use below T-SQL syntax to disable or enable the DDL trigger at the database level. 12345 ENABLE TRIGGER TR_DATABASEEVENTS ON DATABASEGO DISABLE TRIGGER TR_DATABASEEVENTS ON DATABASEGO Use below T-SQL syntax to drop a DDL trigger which is created at the database level. 1 DROP TRIGGER TR_DATABASEEVENTS ON DATABASE 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. LOGON triggers are created at the server level and are useful below cases. 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. 12345678910 CREATE TABLE LoginActivity (LOGONEvent XML ,Logintime datetime) CREATE TRIGGER [track_logins] ON ALL SERVERFOR 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. Enabling and disabling LOGON triggers
Use below T-SQL syntax to disable or enable the LOGON trigger. 12345 ENABLE TRIGGER track_logins ON ALL SERVERGO DISABLE TRIGGER track_logins ON ALL SERVERGO Use below T-SQL syntax to drop a LOGON trigger. 1 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. For example, please refer to below sample trigger for an update which is direct recursive. 12345678910111213141516171819202122 SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO 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 LocationsFOR 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. If the database setting RECURSIVE_TRIGGERS is off, then the trigger is fired only once and does not loop. 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. To set the RECURSIVE_TRIGGERS OFF using T-SQL, use below statement and replace the database name with your database name. 12 ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS OFF WITH NO_WAITGO To set the RECURSIVE_TRIGGERS ON using T-SQL, use below statement and replace the database name with your database name. 12 ALTER DATABASE [AdventureWorks] SET RECURSIVE_TRIGGERS ON WITH NO_WAITGO 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. 1234567891011121314151617181920212223242526 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 Temp1FOR UPDATE ASBEGINUPDATE TEMP2 set ID ='5' where id in (select id from inserted)ENDGO CREATE TRIGGER TR_Temp2 on Temp2FOR UPDATE ASBEGINUPDATE 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. This behavior can be controlled by setting nested triggers off. 12 EXEC sp_configure 'nested triggers', 0 ; GO 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. 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. Below is the sample syntax for setting the order of trigger to first for the INSERT statement. 123456789101112131415161718192021222324252627282930 CREATE TABLE TriggerOrderTest (id int)GO CREATE TRIGGER TR_1 ON TriggerOrderTestFOR INSERTasBEGINPRINT 'First Trigger'ENDGO CREATE TRIGGER TR_2 ON TriggerOrderTestFOR INSERTasBEGINPRINT 'Second Trigger'ENDGO CREATE TRIGGER TR_3 ON TriggerOrderTestFOR INSERTasBEGINPRINT 'Third Trigger'ENDGO 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. 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. Below is the sample syntax for setting the DDL trigger order. 1234 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. 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. Below is the sample syntax to create a triggers in SQL Server with not for replication. 1234567891011 CREATE TRIGGER TR_UPD_Locations ON LocationsFOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHist SELECT LocationID ,getdate() FROM insertedEND 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”. Author Recent Posts Ranga BabuSQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies Latest posts by Ranga Babu (see all) Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019 Overview of the Collate SQL command - October 22, 2019 Recover a lost SA password - September 20, 2019 Related posts
SQL Server MERGE Statement overview and examples Limit SQL Server Login Authentication scope using a Logon Trigger Nested Triggers in SQL Server Disabling triggers in SQL Server for a specific session Merge SQL Server replication parameterized row filter issues 262,111 Views Follow us
Popular
SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices Trending
SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples Solutions
Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server Categories and tips
▼Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ►Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy