Triggers In SQL Server

There are two primary mechanisms for enforcing business rules and data integrity first is Constraints and second is Triggers.

Triggers are special type of stored procedure that automatically initiates an action when DML or DDL event occurs in database.

Triggers are stored in Database; it is basically used to maintain referential integrity of data. A trigger can’t be called and executed like stored procedure, DBMS automatically fires the trigger when DML or DDL event occurs in the database that is why we call Triggers as event driven stored procedure.

Triggers are attached to a specific table, View or database.

There are two types of Trigger in SQL Server.

1-      DML Trigger

2-      DDL Trigger

DML Triggers

DML Triggers are those triggers which are automatically fired whenever any DML event takes place in the database. A DML event includes INSERT, UPDATE, and DELETE. DML Trigger can be created on Table and Views.

A DML Trigger can query other tables and can include complex TSQL commands. The Trigger and Statement that fires it are treated as a single Transaction which can be rolled back from within the Trigger.

In SQL Server DML Triggers are disabled on Truncate Table and Bulk Insert Operation.

DML Triggers are useful in these ways

·         They can cascade changes through related tables in the database.

·         They can guard against wrong or malicious INSERT, UPDATE and DELETE operations.

·         Unlike CHECK Constraints, DML Triggers can reference columns in other tables.

·         They can evaluate the state of a table before and after a data modification with the help of Inserted and Deleted temporary table and take actions based on that difference.

·         Multiple DML Triggers of the same type (INSERT, UPDATE, DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

·         They can be used for auditing purpose.

There are basically two types of DML Trigger

1-      AFTER Trigger.

2-      INSTEAD OF Trigger.

AFTER Trigger

AFTER Triggers are also called as FOR Trigger. After Triggers are fired after the DML event (INSERT, UPDATE, and DELETE). We can’t create AFTER Trigger on Views. This Trigger can be created only on Tables.

INSTEAD OF Trigger

As name suggest INSTEAD OF Triggers are fired in place of DML Event (INSERT, UPDATE, and DELETE), therefore overriding the actions of the triggering statements. INSTEAD OF Triggers can be created on both Table and Views. At most one INSTEAD OF Trigger per INSERT ,UPDATE, DELETE statement can be defined on a table or view. INSTEAD OF Triggers are not allowed on updatable views that use WITH CHECK OPTION.

I have already explained how to create a INSTEAD OF Trigger on Views. Click here to know about Views in SQL Server and How INSTEAD OF Trigger works on Views.

Syntax to create DML Trigger

CREATE TRIGGER Trigger_Name

ON TABLE|VIEW  (TABLE OR VIEW on which DML trigger will be created, also referred as Trigger Table and Trigger View )

[WITH ENCRYPTION] (Encrypts the text of the CREATE TRIGGER statement)

AFTER|INSTEAD OF (Decide AFTER or INSTEAD OF as par the requirement)

INSERT | UPDATE | DELETE (Specifies the data modification statement that activate the DML Trigger)

 AS
BEGIN
TSQL Statement (Write your TSQL code that should be execute on DML event)

END

Syntax to Drop DML Trigger

DROP TRIGGER Trigger_Name

Syntax to Modify DML Trigger

ALTER TRIGGER Trigger_Name

ON TABLE|VIEW  (TABLE OR VIEW on which DML trigger will be created, also referred as Trigger Table and Trigger View )

[WITH ENCRYPTION] (Encrypts the text of the CREATE TRIGGER statement)

AFTER|INSTEAD OF (Decide AFTER or INSTEAD OF as par the requirement)

INSERT | UPDATE | DELETE (Specifies the data modification statement that activate the DML Trigger)

 AS
BEGIN
TSQL Statement (Write your new TSQL code that should be execute on DML event)

END

Syntax to Disable DML|DDL Trigger

DISABLE TRIGGER Trigger_Name|ALL| ON TABLE|VIEW|DATABASE|ALL SERVER

(If you will use ALL then it will disable all available triggers in a database.)

Inserted and Deleted Tables in Trigger

Inserted and Deleted plays a very important role in Triggers, it is also known as Magic Table. Both tables will be created in Temp DB by the trigger. These two tables are only accessible in Trigger, in another way we can say scope of these tables are until trigger is active. Once Trigger executes SQL Server automatically drops these two tables. User can’t access these two tables outside Triggers. User can’t perform any DML or DDL operation on these two magic tables. These magic tables are read only tables. When we try to perform any DML operation on table if table contains DML trigger then the values of DML statement will be copied in these two magic tables. Structure of these two tables will be same to our Trigger table. Trigger table means table which has trigger. Inserted and deleted table can contain multiple records, it depends on the transaction.

Let’s understand how these two tables work in DML operation.

DELETE Operation

DELETED table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the Trigger Table and Transferred into DELETED Magic table. Trigger Table and Magic table DELETED have no rows in common.

INSERT Operation

INSERTED table stores copies of the affected rows during INSERT and UPDATE statements. During the execution of an INSERT or UPDATE statement, new rows are added at the same time to Trigger Table and INSERTED Magic table. The rows in INSERTED table are copies of the new rows in the Trigger table.

UPDATE Operation

An UPDATE transaction is similar to DELETE operation followed by an INSERT operation. The old records are copied to the DELETED Magic table first, and then the new rows are copied to the Trigger Table and to INSERTED Magic table.

DML Trigger example

Task - A Trigger that will convert the Dname and Loc into Upper case when the user insert in lower case.

1-      AFTER Trigger

/*CREATING A NEW DATABASE*/

USE MASTER

IF EXISTS(SELECT NAME FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')

DROP DATABASE TEACHMESQLSERVER

GO

CREATE DATABASE TEACHMESQLSERVER

/*CREATING NEW DEP TABLE AND POPLUTAING DATA*/

GO

USE TEACHMESQLSERVER

GO

CREATE TABLE DEP (DEPID INT PRIMARY KEY IDENTITY (1,1), DNAME VARCHAR(MAX), LOC VARCHAR(MAX))

GO

INSERT INTO DEP VALUES ('GHANESH','INDIA'),('KEVIN','USA'),('anvie','uk')

GO

SELECT * FROM DEP /*YOU CAN SEE RESULT SHOWS FIRST TWO ROWS ARE ALL IN UPPER CASE AND THIRD ROW IS IN LOWER CASE*/

GO

/*CREATING AFTER DML TRIGGER ON DEP TABLE TO CONVERT LOWER CASE TO UPPER CASE, THIS TRIGGER WILL WORK ONLY FOR SINGLE RECORD GETTING INSERTED INTO TABLE*/

CREATE TRIGGER DEPTRIGGER

ON DEP

WITH ENCRYPTION

AFTER INSERT

AS

BEGIN

DECLARE @DEPID INT,@DEPNAME VARCHAR(MAX), @LOCATION VARCHAR(MAX)

SELECT @DEPID=DEPID, @DEPNAME=DNAME , @LOCATION=LOC FROM INSERTED

UPDATE  DEP SET DNAME=UPPER(@DEPNAME),LOC=UPPER(@LOCATION) WHERE DEPID=@DEPID

END

GO

/*INSERTING NEW RECORD IN LOWER CASE*/

INSERT INTO DEP VALUES ('gautam','japan')

GO

SELECT * FROM DEP /*YOU CAN SEE FOURTH RECORD WAS ENTERED IN LOWER CASE BUT IT WAS CONVERTED INTO UPPER CASE IN TRIGGER TABLE*/

GO

We can accomplish the same thing using INSTEAD OF Trigger, Below is the example.

2-      INSTEAD OF Trigger

*DROPPING AFTER DML TRIGGER FROM DEP TABLE*/

GO

DROP TRIGGER DEPTRIGGER

GO

/*CREATING INSTEAD OF DML TRIGGER ON DEP TABLE TO CONVERT LOWER CASE TO UPPER CASE, THIS TRIGGER WILL WORK ONLY FOR SINGLE RECORD GETTING INSERTED INTO TABLE*/

CREATE TRIGGER DEPTRIGGER

ON DEP

WITH ENCRYPTION

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO DEP SELECT UPPER(DNAME) , UPPER(LOC) FROM INSERTED

END

GO

/*INSERTING NEW RECORD IN LOWER CASE*/

INSERT INTO DEP VALUES ('Prasad','china')

GO

SELECT * FROM DEP /*YOU CAN SEE FIFTH RECORD WAS ENTERED IN LOWER CASE BUT IT WAS CONVERTED INTO UPPER CASE IN TRIGGER TABLE*/

GO

Once you create a trigger it gets stored in database. You can easily find it. There are many ways to find Trigger in a database.

Method 1-

Go to in Object Explorer ----> Tables ----> Your Table (i.e DEP Table) ---->Triggers

under Trigger you will find Trigger if present, in our case we recently created DEPTRIGGER.

Method 2-

You can find all Triggers present in your database using System Views. Click here to know about System Views.

Run any of the below query to get list of all Triggers in your database.

GO

SELECT * FROM SYS.triggers

GO

SELECT * FROM SYS.objects WHERE TYPE='TR'

In our case we have only one trigger in our database that is why only DEPTRIGGER is returned, If you want to find on which object your trigger was created then use Parent_Id from above queries and pass it into below query.

GO

SELECT * FROM SYS.objects WHERE OBJECT_ID=581577110

You can see query shows we had created DEPTRIGGER on DEP Table.

Disable-Enable Trigger

Suppose you don’t want Trigger to fire on any DML or DDL event, There are two ways to achieve this one is drop the Trigger  and if you don’t want to drop your Trigger then Disable the trigger.

Let’s Disable DEPTRIGGER

GO

DISABLE TRIGGER DEPTRIGGER ON DEP

You have successfully disabled the Trigger , now if you will execute any DML command it will execute on DML Event let’s try.

GO

INSERT INTO DEP VALUES ('kumar','india')

GO

SELECT * FROM DEP

As you can see Dname and Loc was not changed to UPPER CASE.

We can enable Trigger using Enable keywor, Below you can see the code.

GO

ENABLE TRIGGER DEPTRIGGER ON DEP

Change Trigger Execution Sequence

We can have many DML triggers on a single Table or View for the same event.

Generally execution sequence will follow Queue Data structure (first come first serve) concept. I will try to explain this suppose we have four Triggers Trigger1, Trigger2, Trigger3 and Trigger4 on DEP Table. First we created Trigger1 then Trigger2 then Trigger3 and last Trigger4 on DEP Table. When any DML event occurs on DEP Table, Trigger1 will be executed first. Once Trigger1 completes; Trigger2 will get the control and it will get executed then Trigger3 and last Trigger4 will get executed. Trigger2 will get control only after completion of First Trigger Trigger1. If Trigger1 fails with any error in that case also Trigger2, Trigger3 and Trigger4 will  be fired.

Let’s create these four Triggers on DEP Table.

/*CREATING FOUR TRIGGERS ON DEP TABLE ON INSERT DML OPERATION*/

CREATE TRIGGER TRIGGER1 ON DEP

AFTER INSERT

AS

BEGIN

PRINT 'THIS CODE IS PRINTED BY TRIGGER1'

END

GO

CREATE TRIGGER TRIGGER2 ON DEP

AFTER INSERT

AS

BEGIN

PRINT 'THIS CODE IS PRINTED BY TRIGGER2'

END

GO

CREATE TRIGGER TRIGGER3 ON DEP

AFTER INSERT

AS

BEGIN

PRINT 'THIS CODE IS PRINTED BY TRIGGER3'

END

GO

CREATE TRIGGER TRIGGER4 ON DEP

AFTER INSERT

AS

BEGIN

PRINT 'THIS CODE IS PRINTED BY TRIGGER4'

END

GO

Now let’s create an INSERT DML Event and will see the execution sequence of all four Triggers.

GO

SET NOCOUNT ON

INSERT INTO DEP VALUES ('Anshul','India')

Once we execute above query our four triggers gets fired one after another. From the result you can easily find the execution sequence of all four Triggers. Trigger1 was executed first and Trigger4 was executed last. Execution sequence and Triggers creation sequence are same.

If you want to change execution sequence of trigger then you can change Trigger execution sequence by using SP_Settriggerorder system stored procedure. SP_Settriggerorder Specifies which AFTER trigger will be fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

Let’s change the Execution sequence order of Trigger. I want my Trigger3 should be fired first and Trigger1 should be fired last.

Sp_SETTRIGGERORDER Stored procedure has four string parameters.Click here to know more about SP_SETTRIGGERORDER stored procedure.

@triggername= ‘trigger name’

@order= ’value’

@stmttype= ’statement type'

@namespace = 'DATABASE' | 'SERVER' | NULL (Database or Server for DDL Triggers, NULL for DML Triggers)

  

GO

EXEC sp_settriggerorder 'TRIGGER3','FIRST','INSERT' /*ASSISNING ORDERVALUE FOR FIRST*/

GO

EXEC sp_settriggerorder  'TRIGGER1','LAST','INSERT' /*ASSISNING ORDERVALUE FOR LAST*/

GO

INSERT INTO DEP VALUES ('YOGITA','India')

As you can see execution sequence has been changed as per our requirement.

Delete all Triggers

If you want to drop all triggers from your database then you can use below code.

USE TEACHMESQLSERVER

GO

/*LETS DROP ALL TRIGGERS FROM OUR DATABASE */

DECLARE @SQL VARCHAR(MAX),@TRIGGERNAME VARCHAR(MAX)

SET @SQL ='DROP TRIGGER '

SET @TRIGGERNAME=(SELECT TOP 1 NAME FROM SYS.TRIGGERS)

SELECT @TRIGGERNAME= @TRIGGERNAME+','+NAME FROM SYS.triggers WHERE name <>@TRIGGERNAME

SET @SQL= @SQL+@TRIGGERNAME

EXEC(@SQL)

PRINT 'Trriger '+'('+@TRIGGERNAME+')' +' Has been Dropped'

GO

As you can see it has deleted all four Triggers we had created in our previous exercise.

Nested Triggers

If a trigger changes a table on which there is another Trigger, the second Trigger is activated and can then call a third trigger and so on. Triggers can be nested to a maximum of 32 levels. We can disable nested triggers, using system stored procedure sp_configure to 0, by default SQL Server allows for nested triggers.

I will explain how Nested Triggers works with a simple example.

I will use my previous created table DEP, I will also create a new DuplicateDep table. This table will be a duplicate table of DEP table, whenever any new record will be entered into this table, it will be copied to this DuplicateDep table.

This example is demonstrated to understand the concept of nested triggers, it’s a basic code you can make more interactive code.

GO

SELECT * FROM DEP

GO

SELECT * INTO DUPLICATEDEP FROM DEP /*CREATING A NEW TABLE FROM DEP TABLE AND POPULATING DATA INTO IT*/

GO

SELECT * FROM DUPLICATEDEP

GO

CREATE TRIGGER DEPTRIGGER ON DEP /*CREATING DML TRIGGER ON DEP TABLE, IT WILL MAKE CHANGE IN DUPLICATEDEP TABLE*/

AFTER INSERT

AS

INSERT INTO DUPLICATEDEP SELECT DNAME,LOC FROM INSERTED

PRINT 'A NEW RECORD HAS BEEN ADDED INTO DUPLICATEDEP TABLE'

GO

CREATE TRIGGER DUPLICATEDEPTRIGGER ON DUPLICATEDEP /*CREATING DUPLICATEDEPTRIGGER ON DUPLICATEDEP TABLE, THIS TRIIGGER WILL BE FIRED IF INSERT DML EVENT OCCURS*/

AFTER INSERT

AS

PRINT 'New Record was inserted by DEPTRIGGER'+char(10)

GO

SET NOCOUNT ON

INSERT INTO DEP VALUES('SAURABH','USA')

New record was successfully inserted into DUPLICATEDEP Table, This record was inserted by DEPTRIGGER, AS you know we also have more Trigger on DuplicateDep Table which will fire whenever any INSERT command will be executed on DuplicateDep table. In this case INSERT command was executed by DEPTRIGGER. In my definition “If a trigger changes a table on which there is another Trigger, the second Trigger is activated” this is known as Nested Trigger.

You can check that new record is also available in DUPLICATEDEP Table.

GO

SELECT * FROM DUPLICATEDEP

DML Triggers Important points

·         We can execute Stored Procedure in Trigger’s SQL Statement

·         We can use CTE in Trigger’s SQL Statement

·         We can perform DML and DDL operations in Trigger’s

·         We can call functions in Triggers

·         We can use Temporary Tables in Triggers

·         Inserted and delete magic tables follow stack data structure concept for data insertion, deletion. 

·         We can use Cursors in Triggers

·         Trigger will be fired per Transaction.

DDL Triggers

DDL Triggers are those triggers which are automatically fired whenever any DDL event takes place in the Database or Server. There are many DDL events some of them are CREATE, DROP, ALTER. DDL Triggers are only AFTER Triggers which can be created on Database or Servers. We can’t create DDL Trigger on Tables or Views. DDL Triggers can be created either Database or Server. Syntax to create DDL Trigger is similar to DML Trigger. You can use DDL and DML command in DDL Trigger SQL Statement block.

If you have Server Scope Trigger and Database scope Trigger then Server Scope Trigger always Fire First.

Let’s create one DDL Trigger to understand how it works

Suppose you don’t want any user to CREATE, DROP or ALTER Table in your database in that case you can create a DDL Trigger on your database.

USE TEACHMESQLSERVER

GO

CREATE TRIGGER FIRSTDDL ON DATABASE

AFTER CREATE_TABLE,DROP_TABLE, ALTER_TABLE /*THESE ARE DDL EVENTS*/

AS

ROLLBACK

PRINT 'YOU CANT CREATE/DROP/MODIFY TABLE IN THIS DATABASE.'

GO

You have successfully creatd DDL Trigger on TEACHMESQLSERVER Database, similar to this you can Create DDL Trigger on Servers.

Now If you will run CREATE, DROP and ALTER command it will not allow. Let’s try to run CREATE, DROP and ALTER command.

GO

CREATE TABLE EMP1 (EMPID INT ,EMPNAME VARCHAR(MAX))

GO

DROP TABLE DEP

GO

ALTER TABLE DEP ADD FULLNAME VARCHAR(MAX)

We couldn’t perform any of the operation we got an error message. This is the beauty of DDL Trigger

Like DML Trriggers all DDL Database Triggers will be stored in the datbase, beasue these triggers will be created on Database or Server so finding them in database is different from fiding DML Triggers.

You can find DDL Database Triggers under the follwing path.

Or you can query against system table SELECT * FROM SYS.TRIGGERS

DDL Server Scope Trigger can be find under SYS.SERVER_TRIGGERS system table, you can query against system table SELECT * FROM SYS.SERVER_TRIGGERS

There are many DDL Event, All DDL Trigger Events are stored in SQL Server Database.  You can easily find them using System View. The below query will give you list of all available DDL Events.

SELECT * FROM SYS.TRIGGER_EVENT_TYPES

Syntax to Drop DDL Trigger

DROP TRIGGER Trigger_Name on Database| All Server

I hope you enjoyed the article don't forget to share on facebook. Please leave your comments if there are any questions.