--- title: "Implementing Triggers in Sql Server" description: "When we want to resolve concurrency issue in database we can use concept of triggers. In Sql server various kinds of triggers can be used for differen" author: "Anonymous User" published: 2011-01-27 updated: 2019-10-11 canonical: https://www.mindstick.com/articles/430/implementing-triggers-in-sql-server category: "database" tags: ["database"] reading_time: 5 minutes --- # Implementing Triggers in Sql Server When we want to resolve concurrency issue in database we can use concept of triggers. In Sql server various kinds of triggers can be used for different types of data manipulation operations. The Sql server supports the following types of triggers.\ - Data Modification Language (DML) triggers. - Data Definition Language (DDL) triggers ##### DML triggers A DML triggers is fired when data in the underlying table if affected by DML statements such as Insert, Update, or Delete. Whenever a trigger is fired in response to the insert, delete or update statement the Sql server creates two temporary tables, called magic tables. The magic table which is created is called Inserted and Deleted. These are virtual table and are similar in structure to the table on which the trigger is defined. Depending on the operation that is performed by user the trigger is further categorized in two parts as: - **Insert trigger**: Is fired whenever an attempt is made to insert a new row in the table. When an insert statement is executed, a new row is added to both the trigger and the inserted tables. - **Delete trigger**: Is fired automatically whenever an attempt is made to delete a row from the trigger table. When a delete statement is executed, the specified rows from the trigger table are deleted and are added to the deleted table. - **Update trigger**: Is fired when an update statement is executed in the trigger table. It uses two logical tables for its operation, the deleted table that contains the original rows and the inserted table that stores the new row. ##### DDL triggers A DDL trigger is fired whenever DDL statements such as create table or alter table are executed. DDL triggers can be used to perform administrative task such as database auditing. ##### Depending on the way in which triggers are fired, they are categorized as follows: - **After trigger**: The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The after trigger is fired after the execution of DML operations for which it has been defined. - **Instead of triggers**: Whenever you want to perform an action on different table or views then we can use instead of triggers. It can be created both table as well as view. - **Nested trigger**: Nested triggers are fired because of action of other triggers. For example, you update a row from a table Student. A trigger on Student table update rows from Marks table. Because we are updating a row from table B, a trigger is executed on table B to record the deleted rows. - **Recursive Triggers**: Recursive triggers are similar of nested triggers. The difference between Recursive trigger and nested trigger is that recursive triggers provide support at database label while nested trigger is not. As name implies recursive triggers are called by itself. ##### Syntax for creating triggers ``` create trigger trigger_name on objectname{for/after/instead of} {DDL eventtype}asbegin     sqlstatement1end  where     create is a keyword used to create trigger     trigger is a keyword which means a trigger is created     {for/after/instead of}represent types of trigger that should be created     DDL eventtype represents trigger condition and actions. ``` ##### Example demonstrating the creation of trigger ##### Example 1: Creating an Insert Trigger ``` create trigger trgInsertStudent on Studentfor insertas     declare @sage int     select @sage=sage from inserted     if(@sage < 18)     begin           print 'The age should be greater than or equal to 18. Hence can not be inserted'           rollback transaction     endreturn  select * from Student  insert Student values('S0005','Alok',16,'Lucknow') ``` ##### output: The age should be greater than or equal to 18. Hence can not be inserted \ Msg 3609, Level 16, State 1, Line 3 \ The transaction ended in the trigger. The batch has been aborted. ##### Example 2: Creating a Delete Trigger ``` create trigger trgDelteStudent on Studentfor deleteas     print 'Deletion of student record is not allowed'     rollback transactionreturn  delete from Student where sid='S0005' ``` ##### Output: ##### Deletion of student record is not allowed \ Msg 3609, Level 16, State 1, Line 2 \ The transaction ended in the trigger. The batch has been aborted. ##### Example 3: Creating an Update Trigger ``` create trigger trgUpdateStudent on Studentfor updateas     if UPDATE (sage)     begin           declare @sage int           select @sage=sage from Student           if(@sage < 18)           begin                print 'The age of student could not be less than 18'                rollback transaction           end     endreturn  update Student set sage=17 where sid='S0003' ``` \ ##### output: (1 row(s) affected) ##### Example 5: Creating an After trigger ``` create trigger trgInsertShift on Studentafter insert asprint 'Record inserted successful' ``` ##### output Record inserted successful (1 row(s) affected) ##### Altering trigger As a database developer you might need to modify the logic of code behind the triggers. For implementing such type of task we need alter command. ##### Syntax for altering trigger ``` alter trigger triffername{for\after}{evemttype}asSqlstatement ``` ##### Example for altering trigger ``` alter trigger trgInsertShift on Studentafter insert asprint 'Thnks for inserting record' ``` ##### Deleting a Trigger Use drop keyword for deleting any trigger ##### Example for deleting Trigger drop trigger **trgInsertShift** ## \ --- Original Source: https://www.mindstick.com/articles/430/implementing-triggers-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.