--- title: "Trigger in SQL Server" description: "A Trigger is a special kind of stored procedure that is invoked whenever data in the underlying table is affected by any of the Data Manipulation Lang" author: "AVADHESH PATEL" published: 2012-08-20 updated: 2017-11-29 canonical: https://www.mindstick.com/articles/982/trigger-in-sql-server category: "database" tags: ["database"] reading_time: 9 minutes --- # Trigger in SQL Server A Trigger is a special kind of [stored procedure](https://www.mindstick.com/articles/36/stored-procedure-in-microsoft-sql-server) that is invoked whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT, UPDATE OR DELETE or Data Definition Language (DDL) statements-CREATE, ALTER, DROP. A Trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete. Triggers are used to ensure [data integrity](https://www.mindstick.com/forum/160197/what-is-the-normalization-in-sql-server-explain-its-impact-on-data-integrity-and-performance) before or after performing data manipulations.\ ##### There are basically three type of SQL Trigger. 1. DML Trigger 2. DDL Trigger 3. Logon Trigger ##### DML Trigger A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. DML triggers are used to enforce business rules when data is modified and to extend the integrity checking logic of Microsoft SQL Server constraints, defaults, and rules. ##### Types of DML Triggers There are mainly two types of triggers associated with each of action performed I. e. INSERT [UPDATE and DELETE](https://www.mindstick.com/forum/353/insert-update-and-delete-records-in-php). They are: 1. AFTER/FOR Triggers 2. INSTEAD OF Triggers ##### AFTER/FOR Trigger These triggers fire After/For an INSERT, UPDATE or DELETE on table associated. They are not supported for views. \ \ AFTER Triggers can be classified further into three types: i. AFTER INSERT Trigger. ii. AFTER UPDATE Trigger. iii. AFTER DELETE Trigger. ##### AFTER Trigger for INSERT event: When trigger fired on INSERT event on any table then it is called Trigger for ‘INSERT. Syntax: ``` ----- SYNTAX DEMONSTRATION OF TRIGGER ON INSERT EVENT ON ANY TABLE----CREATE TRIGGER ON INSERTAS---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----GO ``` ##### Example: Creating Trigger on AFTER INSERT Event ``` ------DEMONSTRATION OF CREATING TRIGGER ON INSERT EVENTCREATE TRIGGER INSERT_TRIGGERON USERLOGININFOAFTER INSERTASPRINT 'INSERT TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'GO ``` ##### AFTER Trigger for UPDATE Event: When trigger fired on UPDATE event on any table then it is called Trigger for ‘UPDATE. ##### Syntax: Creating Trigger for AFTER UPDATE event ``` ----- SYNTAX DEMONSTRATION OF TRIGGER ON UPDATE EVENT ON ANY TABLE----CREATE TRIGGER ON  UPDATEAS---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----GO ``` ##### Example: Creating Trigger on AFTER UPDATE Event ``` ---- DEMONSTRATION FOR CREATING TRIGGER FOR UPDATE EVENT ON TABLECREATE TRIGGER UPDATE_TRIGGERON USERLOGININFOAFTER UPDATEASPRINT ‘UPDATE TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'GO ``` ##### AFTER Trigger for DELETE Event: When trigger fired on DELETE event on any table then it is called Trigger for ‘DELETE. ##### Syntax: Creating Trigger for AFTER DELETE event ``` ----- SYNTAX DEMONSTRATION OF TRIGGER ON DELETE EVENT ON ANY TABLE----CREATE TRIGGER ON DELETEAS---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----GO ``` ##### Example: Creating Trigger on AFTER DELETE Event ``` ---- DEMONSTRATION FOR CREATING TRIGGER FOR DELETE EVENT ON TABLECREATE TRIGGER DELETE_TRIGGERON USERLOGININFOAFTER DELETEASPRINT DELETE TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'GO ``` ##### INSTEAD OF Triggers AFTER triggers do not work for views, so we will discuss the properties and functionality of INSTEAD OF triggers. INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. Inserted and deleted tables are widely used in operations inside triggers. INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two [virtual tables](https://www.mindstick.com/forum/158401/what-are-some-advanced-features-of-sqlite-such-as-virtual-tables-or-user-defined-functions) called Inserted and Deleted. For a DELETE trigger, the [virtual table](https://www.mindstick.com/interview/876/which-virtual-table-does-a-trigger-use) Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version. Basically there are three types of INSTEAD Of [Trigger in SQL](https://www.mindstick.com/articles/1440/trigger-in-sql) server which are namely define as follows: 1. INSTEAD OF INSERT Trigger 2. INSTEAD OF UPDATE Trigger 3. INSTEAD OF DELETE Trigger ##### INSTEAD OF INSERT Trigger: INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the [INSERT statement](https://www.mindstick.com/forum/157532/why-is-use-the-identity-column-in-the-insert-statement-in-sql). Usually, the INSTEAD OF INSERT trigger is defined on a view to insert data into one or more base tables. ##### Syntax: Creating Instead Of Insert Trigger ``` -----SYNTAX DEMONSTRATION OF INSTEAD OF INSERT TRIGGER--CREATE TRIGGER ON OR INSTEAD OF INSERT ASBEGIN----INSERT SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead Of Insert Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF INSERT TRIGGER CREATE VIEW TEST_INSERT_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF INSERT TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_INSERT_INSTEADOF  INSTEAD OF INSERT  AS  INSERT INTO Userlogin  SELECT * FROM INSERTED  GO ``` ##### INSTEAD OF DELETE Trigger: INSTEAD OF DELETE triggers can be defined on a view or table to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables. DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted. ##### Syntax: Creating Instead of Delete Trigger ```   -----SYNTAX DEMONSTRATION OF INSTEAD OF DELETE TRIGGER--CREATE TRIGGER ON OR INSTEAD OF DELETE ASBEGIN----DELETE SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead of Delete Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF DELETE TRIGGER CREATE VIEW TEST_DELETE_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF DELETE TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_DELETE_INSTEADOF  INSTEAD OF DELETE  AS  INSERT INTO Userlogin  SELECT * FROM DELETEED  GO ``` ##### INSTEAD OF UPDATE Trigger: INSTEAD OF UPDATE triggers can be defined on a view or table to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables. UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all non null view columns referenced in the SET clause. ##### Syntax: Creating Instead of Update Trigger ```   -----SYNTAX DEMONSTRATION OF INSTEAD OF UPDATE TRIGGER--CREATE TRIGGER ON OR INSTEAD OF UPDATE ASBEGIN----UPDATE SQL QUERY TO INSERT DATA INTO TABLE----TRIGGER BODY SQL CODE---ENDGO ``` ##### Example: Creating Instead of Update Trigger ``` ----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF UPDATE TRIGGER CREATE VIEW TEST_UPDATE_INSTEADOF AS SELECT * FROM Userlogin ------DEMONSRATION OF CREATING INSTEAD OF UPDATE TRIGGER--  CREATE TRIGGER TEST_INSTEADOF_TRIGGER  ON TEST_UPDATE_INSTEADOF  INSTEAD OF DELETE  AS  UPDATE Userlogin SET Id = deleted.id, Name = deleted.name    GO ======================================================================= ``` ##### DDL Trigger: DDL triggers execute in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system [stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application) that perform DDL-like operations. ##### Trigger for Create Table: ##### Syntax: Creating DDL Trigger for CREATE table into database ``` ---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR CREATING TABLE INTO DATABASECREATE TRIGGER ON DATABASEFOR CREATE_TABLEAS---SQL EXECUTABLE CODE FOR CREAT TABLE EVENT---GO ``` ##### Example: Creating DDL Trigger for CREATE table into database ``` ----DEMONSTRATION TO CREATING DDL TRIGGER FOR CREATING TABLE INTO DATABASECREATE TRIGGER CREATE_TABLEEON DATABASEFOR CREATE_TABLEASPRINT 'TRIGGER FOR CREATE TABLE IN DATABASE'GO ``` ##### Trigger for Drop Table: ##### Syntax: Creating DDL Trigger for DROP table into database ``` ---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR DROPPING TABLE INTO DATABASECREATE TRIGGER ON DATABASEFOR DROP_TABLEAS---SQL EXECUTABLE CODE FOR DROP TABLE EVENT---GO ``` ##### Example: Creating DDL Trigger for DROP table into database ``` ----DEMONSTRATION TO CREATING DDL TRIGGER FOR DROP TABLE INTO DATABASECREATE TRIGGER DROP_TABLEEON DATABASEFOR DROP_TABLEASPRINT 'TRIGGER FOR DROP TABLE IN DATABASE'GO ``` ##### Trigger for Alter Table: ##### Syntax: Creating DDL Trigger for ALTER table into database ``` ---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR ALTERING TABLE INTO DATABASECREATE TRIGGER ON DATABASEFOR ALTER_TABLEAS---SQL EXECUTABLE CODE FOR ALTER TABLE EVENT---GO ``` ##### Example: Creating DDL Trigger for ALTER table into database ``` ----DEMONSTRATION TO CREATING DDL TRIGGER FOR ALTER TABLE INTO DATABASECREATE TRIGGER ALTER_TABLEEON DATABASEFOR ALTER_TABLEASPRINT 'TRIGGER FOR ALTER TABLE IN DATABASE'GO ``` ##### Logon Trigger: Logon [Trigger event](https://www.mindstick.com/forum/34079/trigger-event-on-tab-menu-click-in-jquery) is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as [error messages](https://www.mindstick.com/forum/160245/how-to-customize-error-messages-with-a-data-annotation-in-asp-dot-net-core) and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails. ##### Syntax: Creating LOGON Trigger into database ``` -----SYNTAX DEMONSTRATION OF CREATING LOGON TRIGGERCREATE TRIGGER ON ALL SERVER FOR LOGONAS--- SQL QUERY TO RETRIEVE INFORMATION OR STORED INFORMATIONGO ``` ##### Example: Creating LOGON Trigger into database ``` ---- CREATE NEW DATABASE TO STORE TRIGGER CREATE DATABASE LOGON_TRIGGER---- CREATE TABLE USING ABOVE DATABASE USE LOGON_TRIGGER----TABLE WHICH STORED LOGIN INFORMATION OF LOGIN SQL SERVER USERCREATE TABLE LOGIN_USER(  SYSTEMUSER VARCHAR(50),  DATABASEUSER VARCHAR(50),  LOGTIME DATETIME)-----DEMONSTRATION OF LOGON TRIGGERCREATE TRIGGER LOGON_TRIGGERON ALL SERVER FOR LOGONASINSERT INTO LOGON_TRIGGER.dbo.LOGIN_USER VALUES (SYSTEM_USER,USER,GETDATE())GO ======================================================================== ``` NOTE: - DROP Trigger in SQL SERVER ##### Trigger on Table DROP TRIGGER ##### Trigger on Database DROP TRIGGER ON DATABASE ##### Trigger on Logon DROP TRIGGER ON ALL SERVER For details on Trigger see below link http://www.mindstick.com/Articles/8a50890e-16b5-4fc6-8970-54ae61a898f9/?Triggers%20in%20SQL%20Server http://www.mindstick.com/Articles/d9d0f625-6146-415b-b286-3319e3ec336f/?Trigger%20in%20SQL%20server http://www.mindstick.com/Articles/f7074849-0e9e-463b-a12d-f3d7a35b2785/?Trigger%20in%20SQL%20Server http://www.mindstick.com/Articles/72769122-ae03-49c6-9017-7aa239e7b49b/?Implementing%20Triggers%20in%20Sql%20Server \ --- Original Source: https://www.mindstick.com/articles/982/trigger-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.