--- 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: "Sachindra Singh" published: 2011-02-14 updated: 2020-01-19 canonical: https://www.mindstick.com/articles/454/trigger-in-sql-server category: "database" tags: ["database"] reading_time: 5 minutes --- # Trigger in SQL Server 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 Language (DML) statements -INSERT, UPDATE OR DELETE or Data Definition Language (DDL).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 before or after performing data manipulations.\ ##### Kinds of Triggers - Data Modification Language (DML) triggers - Data Definition Language (DDL) triggers ##### Data Modification Language (DML) triggers Data Modification Language triggers is fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT, UPDATE OR DELETE.DML trigger is use to maintaining consistent, reliable and correct data in tables. ##### DML triggers characteristics: - Fired automatically by the SQL Server - Trigger cannot explicitly invoked or executed - It cannot return data to the user. Whenever trigger fired two tables are created: - Inserted - Deleted The Inserted table contains a copy of all records that are inserted in the trigger table. The Deleted table contains all records that have been deleted from the trigger table. This table is used to refer old values.\ Whenever any updating takes place, the trigger uses both the inserted and deleted tables. **Insert trigger**: Is fired whenever an attempt is made to insert a row in the trigger table. **Delete trigger**: Is fired whenever an attempt is made to delete a row in the trigger table. **Update trigger**: Is fired when Update statements executed in the trigger table .It uses two logical tables for its operations, the deleted table that contains the original rows and the inserted table that stores the new rows. Data Definition Language (DDL) triggers A Data Definition Language trigger is fired in response to Data Definition Language, such as create table or alter table .DDL triggers can be used to perform database auditing, administrative tasks. ##### DDL trigger are categorized as: - After Triggers - Instead of Triggers - Nested Triggers - Recursive Triggers ##### After Trigger It is a specialized stored procedure that is executed when data in the table associated with trigger is modified. After trigger executes the code associated with it after the event for which it is made happens. You can have multiple after triggers for any single DML operations. <%-- | | | --- | --%> ##### Instead of Trigger The instead of triggers can be primarily used to perform an action , such as a DML operation on another table or view .This type of trigger can be created on both a table as well as view . ##### Nested Trigger Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the **Nested Triggers** server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting. ##### Recursive Triggers Recursive triggers when a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur. There are two triggers, Direct and Indirect.\ ##### How to Create Trigger ``` Create Trigger trigger_name      On {Object Name}{For | After | Instead Of}{event _type[,..n]| DDl_Database_Level_Events}      {AS            {            SQl_Staement[..n]            } ``` For example I have one table Studentdetail to perform trigger operation ##### StudentDetail ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/c3e9c82e-caac-4e7f-a1a3-8c233c49100b.png) ##### Creating Insert Trigger ##### Query ``` create trigger trg2 on StudentDetailfor insertasbegin      select * from StudentDetailend ``` In StudentDetail inserting one new record after created insert trigger ##### Query Insert StudentDetail values('S011','Imran','25','Varansi','U.P.') ##### Output ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/9cb89b52-1830-4c73-9172-c35f6af6c76d.png) Creating Delete trigger ##### Query ``` create trigger trg4 on StudentDetailfor deleteasbegin      select * from StudentDetailend ``` In Studentdetail table deleting one record after created delete trigger ##### Query delete StudentDetail where Name='Imran' ##### Output ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/9ac12f23-ff8c-4fe9-9c29-81595f66c809.png) Creating Update Trigger ##### Query ``` create trigger trg5 on StudentDetailfor updateasbegin      select * from StudentDetailend ``` In studentdetail table updating one record after created update trigger ##### Query update StudentDetail set City='Gorakhpur',State='U.P.' where Name='Pooja' ##### Output ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/6f4ca0c7-c370-45ee-a7d0-505c1de56006.png) Creating After Trigger ##### Query ``` create trigger trg10on StudentDetailAfterinsertasbeginselect * from StudentDetailend ``` In studentdetail table inserting one record after created after trigger ##### Query Insert StudentDetail values('S012','Vivek','25','Varansi','U.P.') ##### Output ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/e33e1d94-972f-48fe-b0f3-eaa611b3d27b.png) \ Creating Instead of Trigger ##### Query ``` create trigger trg12 on StudentDetail instead of update asbegin    select * from StudentDetailend ``` In studentdetail table updating one record after created instead of trigger but you will see there is no updating performs on table there is cause of instead of trigger. ##### Query update StudentDetail set City='Jaunpur' where Name='Raj' ##### Output ![Trigger in SQL Server](https://www.mindstick.com/mindstickarticle/f7074849-0e9e-463b-a12d-f3d7a35b2785/images/10c4c380-a40f-46a4-a6ac-a772e909e313.png) --- Original Source: https://www.mindstick.com/articles/454/trigger-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.