Saturday, July 27, 2013

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 TriggerTrigger1. If Trigger1 fails with any error in that case Trigger2, Trigger3 and Trigger4 will not 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 * FROMSYS.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

SQL Server: Coding the After Delete Trigger in SQL Server
A trigger is special type of stored procedure that is executed when an INSERT, DELETE or UPDATE statement modifies data in a table.
SQL Server initiates an AFTER DELETE trigger whenever a delete statement event occurs.
You can download the AFTER DELETE Trigger script used here so that you can run this script on your local SQL Server, while following the problem below.
We want to keep track of records deleted from employee table into employee_history table.
If any employee leaves the company, his record will be deleted from employee table. However, it will be inserted into employee_history table using the AFTER DELETE trigger.
Our example will have two tables: employee table and employee_history table.
We will create these tables and populate them with some data.
/* Check whether employee table already exists or not*/

IF OBJECT_ID('EMPLOYEE') IS NOT NULL
DROP TABLE EMPLOYEE
GO

/* Create employee table if it does not exist*/

CREATE TABLE EMPLOYEE
(
 EMPID INT PRIMARY KEY,
 FNAME VARCHAR(25),
 LNAME VARCHAR(25),
)
GO

/*Populate employee table with sample rows*/

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (500, 'John','Smith'),
      (501, 'Alex','Admas'),
      (502, 'Eric','James'),
      (503, 'Shaun','Marsh')
GO

/*Create employee_history table with the same structure as
employee table with no rows*/

IF OBJECT_ID('EMPLOYEE_HISTORY') IS NOT NULL
DROP TABLE EMPLOYEE_HISTORY
GO

SELECT *
INTO  EMPLOYEE_HISTORY
FROM  EMPLOYEE
WHERE 1 = 0 -- This will populate Employee_history table with 0 rows
GO

/*See the table records from both tables. We have four rows in both tables*/

SELECT * from EMPLOYEE e -- 4 rows
SELECT * from EMPLOYEE_HISTORY eh -- 0 rows
GO
sql_server_after_delete_trigger

Now, if any record deleted from employee table is deleted, we need to insert it into employee_history table for future reference. Therefore, in this case, we will create an AFTER DELETE trigger.
/*After Delete trigger on employee table*/

IF OBJECT_ID('TRG_EmployeeHistory') IS NOT NULL
DROP TRIGGER TRG_EmployeeHistory
GO

CREATE TRIGGER TRG_EmployeeHistory
ON dbo.EMPLOYEE
AFTER DELETE AS
BEGIN
INSERT INTO EMPLOYEE_HISTORY
SELECT * FROM DELETED
END
GO

Now that we have an AFTER DELETE trigger on employee table, we will delete a row from employee table to see the trigger at work.
The deleted row will be inserted into employee_history table.
/*Delete a record from employee table*/

DELETE FROM EMPLOYEE
WHERE EMPID = 501
GO

/* Notice both the tables. The deleted record from employee is inserted into 
  EMPLOYEE_HISTORY */

SELECT * from EMPLOYEE e -- 3 rows
SELECT * from EMPLOYEE_HISTORY eh -- 1 row
GO
Coding-after-delete-trigger-2
The DELETED table is a magical table that keeps the rows which are deleted. In the AFTER DELETE trigger, we used the DELETED table to keep track of deleted rows. Those rows are inserted into EMPLOYEE_HISTORY table.
You can also go through SQL Server: Coding the After Insert Trigger.


SQL Server: Coding the After Insert Trigger in SQL Server


A trigger is a special type of stored procedure that is executed when an INSERT, DELETE or UPDATE statement modifies data in a table.
SQL Server initiates an AFTER INSERT trigger whenever an insert statement event occurs.
Download the AFTER INSERT TRIGGER script used here with examples so that you can execute the script on your SQL Server machine while following the problem below.
Our goal is that when a record is inserted in employee table, we also want this record to be inserted in employee_backup table. Our example will have two tables: employee andemployee_backup. We will create these tables and populate them with some data.
/*Check whether or not employee table already exists*/

IF OBJECT_ID('EMPLOYEE') IS NOT NULL
DROP TABLE EMPLOYEE
GO

/* Create employee table if it does not exist */

CREATE TABLE EMPLOYEE
(
  EMPID INT PRIMARY KEY,
  FNAME VARCHAR(25),
  LNAME VARCHAR(25),
)
GO

/* Populate employee table with sample rows */

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (500, 'John','Smith'),
       (501, 'Alex','Admas'),
       (502, 'Eric','James'),
       (503, 'Shaun','Marsh')
GO

/* Create and sync employee_backup table with records from employee table so that both 
  tables will have the same records */

IF OBJECT_ID('EMPLOYEE_BACKUP') IS NOT NULL
DROP TABLE EMPLOYEE_BACKUP
GO

SELECT * 
INTO  EMPLOYEE_BACKUP
FROM  EMPLOYEE
GO

/* See the table records from both tables. We have four rows in both tables */

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP
GO
Now, we need to keep both tables in sync so that when a record is inserted into EMPLOYEE it should automatically be inserted into EMPLOYEE_BACKUP. In such a case, we need an AFTER INSERT trigger.
We will create an AFTER INSERT trigger on employee table.
/* After Insert trigger on employee table */

IF OBJECT_ID('TRG_InsertSyncEmp') IS NOT NULL
DROP TRIGGER TRG_InsertSyncEmp
GO

CREATE TRIGGER TRG_InsertSyncEmp 
ON dbo.EMPLOYEE
AFTER INSERT AS
BEGIN
INSERT INTO EMPLOYEE_BACKUP
SELECT * FROM INSERTED
END
GO

Our trigger has been successfully created. Now, we will insert a record in employee table, and we will have the same record in employee_backup table because we have created an AFTER INSERT trigger on employee table.
The INSERTEDtable is a special table which keeps only the rows which are inserted; therefore, we are using this INSERTED table to keep both tables in sync. We are inserting the rows from INSERTED table which is populated with insert statements made on employee table into employee_backup table.
/* Insert a record in employee table. An Insert trigger will be executed here, 
   and the same record will be inserted into employee_backup table */

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME) VALUES (504, 'Vish', 'Dalvi') 
/* See both tables are in sync with same number of records */

SELECT * from EMPLOYEE
SELECT * from EMPLOYEE_BACKUP 
GO
The AFTER INSERT trigger on EMPLOYEE table, as soon as it encounters an insert statement, immediately invokes another insert statement to insert the same row into EMPLOYEE_HISTORY table.


SQL Server: Enable and Disable Triggers


If we have a trigger and want to prevent it from running, we can either delete the trigger or disable it temporarily. With the trigger disabled, it will not function. We have three ways to enable/disable a trigger in SQL Server by using a graphical option or by using a query editor.
We will see two ways of disabling a trigger using a query editor.
In the example below, we will assume that we have a TRG_EmployeeHistory trigger already created on the Employee table.

Disabling/enabling a trigger with the ALTER TABLE option

Disable a Trigger
ALTER TABLE EMPLOYEE 
DISABLE TRIGGER TRG_EmployeeHistory

Enable a Trigger
ALTER TABLE EMPLOYEE
ENABLE TRIGGER TRG_EmployeeHistory

Specifying the trigger and the table name to enable/disable triggers directly

Disable a Trigger
DISABLE TRIGGER TRG_EmployeeHistory ON EMPLOYEE

Enable a Trigger
ENABLE TRIGGER TRG_EmployeeHistory ON EMPLOYEE

Using the Graphical Option to enable/disable a trigger

Follow the instructions below.
[+] Object Explorer
      [-] Database Name
          [-] Table Name
              [-] expand Triggers
                  [-] Right-click on the trigger name to enable/disable it.




Database Triggers, Types of Triggers, Syntax of Triggers

Triggers are special types of Stored Procedures that are defined to executeautomatically in place of or after data modifications.
They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.

Types of triggers:
1. INSTEAD OF:
A trigger that fires before the INSERT, UPDATE, or DELETE statement is conducted.
CREATE TRIGGER trigger name
ON table name
INSTEAD OF operation AS DML statements

2. AFTER:AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.
CREATE TRIGGER trigger name ON table name AFTER operation AS DML statements

Note:
INSTEAD OF triggers may be defined on views where as AFTER cannot.


Trigger

Definition: A special type of SPs which are defined to execute automatically when UPDATE/INSERT/DELETE statement is issued against a table/view
OR some DDL operation happens in database

 Special tables
Inside DML trigger, we can use special tables like INSERTED or DELETED.
INSERTED table will contain the new value/record for particular table
DELETED table will contain the old value/record for particular table
Type of Triggers

  • DML Trigger
    • Before Trigger
      • Getting executed before actual operation
      • we can create N number of before triggers per table per action (IUD)
      • Syntax
      • CREATE TRIGGER trg ON TriggerCheck FOR INSERT -- Before
        AS
        BEGIN
              DECLARE @newValue VARCHAR(10)
             
              SELECT @newValue = tname FROM INSERTED
             
              PRINT @newValue
        END
    • After Trigger
      • Getting executed after actual operation
      • we can create N number of before triggers per table per action (IUD)
      • Syntax
      • CREATE TRIGGER trg ON TriggerCheck AFTER INSERT
        AS
        BEGIN
              DECLARE @newValue VARCHAR(10)
             
              SELECT @newValue = tname FROM INSERTED
             
              PRINT @newValue
        END
    • Instead of Trigger
      • Getting executed instead of actual operation
      • We can create only 1 instead of trigger per table per action (IUD)
      • Syntax
      • CREATE TRIGGER trg ON TriggerCheck INSTEAD OF INSERT
        AS
        BEGIN
              PRINT 'Not allowed'
        END
  • DDL Trigger
    • Introduced in 2005
    • we can track DDL activities like CREATE, DROP, ALTER table/view/sp at database level or CREATE, DROP database/login at server level
    • useful in following areas
      • Auditing
      • Maintaining coding standards for table/sp/function name
      • For alert whenever some DDL activities happen at database level
    • EVENTDATA is a special function which can be used inside trigger and returns XML data which can be further used for tracking
    • let's say we want to track DDL activities within database then first we can create one customized table like this
    • CREATE TABLE tblDDLEventLog
      (
          ID int IDENTITY(1,1) NOT NULL,
          EventTime datetime,
          EventType varchar(15),
          ServerName varchar(25),
          DatabaseName varchar(25),
          ObjectType varchar(25),
          ObjectName varchar(25),
          UserName varchar(15),
          CommandText varchar(max)
      )
    • Now, create trigger to capture create table events
    • CREATE TRIGGER ddltrg_CREATE_TABLE_LOG ON DATABASE-- Create Database DDL Trigger
      FOR CREATE_TABLE -- Trigger will raise when creating a Table
      AS
          DECLARE @xmlEventData XML
          -- Capture the event data that is created
          SET @xmlEventData = eventdata()
          -- Insert information to a EventLog table
          INSERT INTO tblDDLEventLog
          (
              EventTime,
              EventType,
              ServerName,
              DatabaseName,
              ObjectType,
              ObjectName,
              UserName,
              CommandText
          )
          SELECT  REPLACE(CONVERT(VARCHAR(50),@xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
                  CONVERT(VARCHAR(15),@xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
                  CONVERT(VARCHAR(25),@xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
                  CONVERT(VARCHAR(25),@xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
                  CONVERT(VARCHAR(25),@xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
                  CONVERT(VARCHAR(25),@xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
                  CONVERT(VARCHAR(15),@xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
                  CONVERT(VARCHAR(MAX),@xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
      GO
    • Now create some table and check the entries in tblDDLEventLog table.


Something Extra
  • Debugging of DML Trigger is difficult
  • Generally DML triggers should be avoided and rather the same logic can be implemented at SP level
  • Auditing of data can be handled by CDC
  • If multiple BEFORE or AFTER triggers are available for single DML operation, then order will be same as it got created. However, sp_settriggerorder can be used to set the first and last order of trigger execution


Trigger Execution Order

Introduction 
If we have four Insert trigger in One Table, after Inserting values on it which one fire first, second and last.

To find this question answer lets taken an example.

Step-1 [ Creating Base Table and Output Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U')ISNOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EMPLOYEE];
   END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEE]
     (EMPID    INT         NOT NULL PRIMARY KEY,
      EMPNAME  VARCHAR(50) NOT NULL);
     
IF OBJECT_ID(N'dbo.tbl_TRGACTION', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_TRGACTION];
   END
GO
CREATE TABLE [dbo].[tbl_TRGACTION]
     (EMPID    INT         NOT NULL,
      EMPNAME  VARCHAR(50) NOT NULL,
      FROMTRG  VARCHAR(50) NOT NULL);
           

Step-2 [ Now we Create 3 Insert Trigger On this Base Table named tbl_EMPLOYEE ]

--Trigger Creation   - 1    
IF OBJECT_ID(N'dbo.trg_INDERT_1', N'TR')IS NOT NULL
   BEGIN
     DROP TRIGGER [dbo].[trg_INDERT_1];
   END
GO
CREATE TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_1' AS FROMTRG
    FROM   INSERTED;   
END        
GO
--Trigger Creation   - 2    
IF OBJECT_ID(N'dbo.trg_INDERT_2', N'TR')IS NOT NULL
   BEGIN
     DROP TRIGGER [dbo].[trg_INDERT_2];
   END
GO
CREATE TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_2' AS FROMTRG
    FROM   INSERTED;   
END 
GO

--Trigger Creation   - 3    
IF OBJECT_ID(N'dbo.trg_INDERT_3', N'TR')IS NOT NULL
   BEGIN
     DROP TRIGGER [dbo].[trg_INDERT_3];
   END
GO
CREATE TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_3' AS FROMTRG
    FROM   INSERTED;   
END
GO

When the each Trigger fires it insert records on table tbl_TRGFIRE. By which we can understand which one fire first and so on.

Step-3 [ Insert a Records on Base Table and Observe the tbl_TRGFIRE ]

INSERT INTO tbl_EMPLOYEE
       (EMPID, EMPNAME)
VALUES (1, 'Joydeep Das'); 

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)


SELECT * FROm [dbo].[tbl_TRGACTION]  
EMPID       EMPNAME                FROMTRG
----------- -------------------------- -------------
1           Joydeep Das            trg_INDERT_1
1           Joydeep Das            trg_INDERT_2
1           Joydeep Das            trg_INDERT_3

(3 row(s) affected)
Here the Trigger fire on FIFO Basis. That means which one executes first is fire first.

Step-4 [ Alter Trigger at Reverse Order and Observe the tbl_TRGFIRE ]

-- NOW WE Altering The Trigger Sequence [ Recerse Order ]

ALTER TRIGGER [dbo].[trg_INDERT_3] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_3_ALTER' AS FROMTRG
    FROM   INSERTED;   
END
GO

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER' AS FROMTRG
    FROM   INSERTED;   
END
GO


ALTER TRIGGER [dbo].[trg_INDERT_1] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_1_ALTER' AS FROMTRG
    FROM   INSERTED;   
END   
GO

Step-5 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]


INSERT INTO tbl_EMPLOYEE
       (EMPID, EMPNAME)
VALUES (2, 'Chandan Bannerjee');
(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=2;
EMPID       EMPNAME                       FROMTRG
----------- ---------------------------------------------------
2           Chandan Bannerjee             trg_INDERT_1_ALTER
2           Chandan Bannerjee             trg_INDERT_2_ALTER
2           Chandan Bannerjee             trg_INDERT_3_ALTER

(3 row(s) affected)

Same Output as FIFO

Step-6 [ Insert a New Records on Base Table and Observe the tbl_TRGFIRE ]

Now we alter only Second Trigger and Execute it Again
-- Now We Alter Only Second Trigger

ALTER TRIGGER [dbo].[trg_INDERT_2] ON [dbo].[tbl_EMPLOYEE]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[tbl_TRGACTION]
        (EMPID, EMPNAME, FROMTRG )
    SELECT EMPID, EMPNAME, 'trg_INDERT_2_ALTER_2nd Time' AS FROMTRG
    FROM   INSERTED;   
END
GO

INSERT INTO tbl_EMPLOYEE
       (EMPID, EMPNAME)
VALUES (4, 'Sangram Jit Bhattacharya');

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)


SELECT * FROm [dbo].[tbl_TRGACTION] WHERE EMPID=4;
EMPID       EMPNAME                             FROMTRG
----------- ----------------------------------------------
4           Sangram Jit Bhattacharya            trg_INDERT_1_ALTER
4           Sangram Jit Bhattacharya            trg_INDERT_2_ALTER_2nd Time
4           Sangram Jit Bhattacharya            trg_INDERT_3_ALTER

(3 row(s) affected)

Step-7 [ Conclusion by Observing Result ]

Trigger
Execution Order
1.    Creating Trigger trg_INDERT_1
2.    Creating Trigger trg_INDERT_2
3.    Creating Trigger trg_INDERT_3
Order of Execution
1.    trg_INDERT_1
2.    trg_INDERT_2
3.    trg_INDERT_2
1.    Alter Trigger trg_INDERT_1
2.    Alter Trigger trg_INDERT_2
3.    Alter Trigger trg_INDERT_3
Order of Execution
1.    trg_INDERT_1
2.    trg_INDERT_2
3.    trg_INDERT_3
1.    Alter Trigger trg_INDERT_2
Order of Execution
1.    trg_INDERT_1
2.    trg_INDERT_2
3.    trg_INDERT_3


What is the Process of Altering the Trigger Execution Order

If Multiple Trigger exists within the same Table Objects we can set which trigger can execute first and which trigger can execute last. In between them we are unable to set the execution order of trigger.
We can do this by using sp_settriggerorder system stored procedure

exec sp_settriggerorder @triggername = ,
          @order = [FIRST|LAST|NONE],
          @stmttype = [INSERT|UPDATE|DELETE|],
          @namespace = [DATABASE|SERVER|NULL]

  •  @triggername  it's the trigger being ordered.
  •  @order indicates whether the trigger should fire FIRST or LAST. If NONE is specified, then no order is enforced.
  • @stmttype indicates the trigger type i.e. whether it's an INSERT trigger, for instance.
  • @namespace  indicates whether a DDL trigger was created on the database or on the server. If set to NULL, it indicates that the trigger is a DML trigger

Example


exec sp_settriggerorder
     @triggername = 'trg_INDERT_3',
     @order = 'first',
     @stmttype = 'insert',
     @namespace = null
    
exec sp_settriggerorder
     @triggername = 'trg_INDERT_1',
     @order = 'last',
     @stmttype = 'insert',
     @namespace = null 


ROLLBACK statement in TRIGGER

Introduction

One of the junior developers asks me a question that there is a table and an associate trigger with this table, If a certain condition fails in the trigger that it rollback the entire transaction.   

To understand it let’s take an example

We have a table called [dbo].[tbl_EMPMSTR]
The table contains a trigger named [dbo].[trg_CHECKSAL]
The trigger works like a CHECK constraint. If the salary is <= 500 then it should ROLLBACK TRANSACTION.

Step-1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPMSTR', N'U') IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EMPMSTR];
   END
GO
CREATE TABLE [dbo].[tbl_EMPMSTR]
 (
   EMPID    INT         NOT NULL IDENTITY PRIMARYKEY,
   EMPNAME  VARCHAR(50) NOT NULL,
   EMPSAL   INT         NOT NULL
 );
GO

Step-2 [ Create Trigger ]

IF OBJECT_ID(N'dbo.trg_CHECKSAL', N'TR')IS NOTNULL
   BEGIN
      DROP TRIGGER [dbo].[trg_CHECKSAL];
   END        
GO

CREATE TRIGGER [dbo].[trg_CHECKSAL]
ON [dbo].[tbl_EMPMSTR]
AFTER INSERT  
AS
BEGIN
   DECLARE @v_SAL INT = 0;
   IF EXISTS(SELECT * FROM inserted)
      BEGIN
       SET @v_SAL = (SELECT EMPSAL FROM inserted);
       IF @v_SAL<= 500
          BEGIN  
             RAISERROR (N'SALARY not Less then the Rs. 500.00', 16, 1);
             ROLLBACK TRANSACTION;
          END
      END
END
GO

Step-3 [ Now Try some Insert Statement ]

INSERT INTO [dbo].[tbl_EMPMSTR]
       (EMPNAME, EMPSAL)
VALUES('Chandan Bannerjee', 700);
GO

(1 row(s) affected)

INSERT INTO [dbo].[tbl_EMPMSTR]
       (EMPNAME, EMPSAL)
VALUES('Joydeep Das', 300);         

Msg 50000, Level 16, State 1, Procedure trg_CHECKSAL, Line 12
SALARY not Less then the Rs. 500.00
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Step-4 [ Observation ]

SELECT * FROM [dbo].[tbl_EMPMSTR];
EMPID       EMPNAME                     EMPSAL
----------- --------------------------- -----------
1           Chandan Bannerjee           700

(1 row(s) affected)

Here the second insert statement which have the employee name JOYDEEP DAS has ROLLBACKED as the salary is less than 500.


Hope you like it.


Logon Trigger

Introduction

The Logon Trigger is used to catch the successful login entry by both Windows Authentication and SQL Server Authentication. By the use of logon triggers we can audit or control server sessions by  tracking the login activity, restricting logins to SQL Server, or limiting or denying the number of login sessions for a specific user.

The logon triggers always run after the authentication phase, but before the user session is actually established. That means that trigger logon will not fire if authentication fails.
This article is to understand the use of Logon Trigger.

To understand it properly let’s take an example.

Example-1

By this example we can actually set a Logging Audit feature.

-- Create The Audit Database --
CREATE DATABASE AUD_Db
GO
USE AUD_Db
GO

-- Create Audit Table --
IF OBJECT_ID(N'dbo.tbl_LOGINAUD', N'U') IS NOT NULL
   BEGIN
      DROP TABLE dbo.tbl_LOGINAUD;
   END
CREATE TABLE dbo.tbl_LOGINAUD
(
   LoginUser VARCHAR(512),
   AppName   VARCHAR(max),
   DBUser    VARCHAR(512),
   SPID      INT,
   LogonTime DATETIME)
GO

-- Create Logon Trigger --
IF OBJECT_ID(N'dbo.trg_SQLLogon', N'TR') IS NOT NULL
   BEGIN
      DROP TRIGGER AUD_Db.dbo.trg_SQLLogon;
   END
GO  
CREATE TRIGGER AUD_Db.dbo.trg_SQLLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AUD_Db.dbo.tbl_LOGINAUD
       (LoginUser, AppName, DBUser, SPID, LogonTime)
SELECT SYSTEM_USER, APP_NAME, USER, @@SPID, GETDATE();
END
GO

Example -2

For this we have to care a User in the name of test1
Now we create the Trigger

IF OBJECT_ID(N'dbo.trg_ConnectRestric', N'TR') IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[trg_ConnectRestric];
   END
GO  
CREATE TRIGGER [dbo].[trg_ConnectRestric]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorTxt VARCHAR(128);

SET @ErrorTxt = 'Cannot allow login to "test1" outside of Business hours. ';
SET @ErrorTxt = @ErrorTxt + 'Please try again between business hours 10:00 and 18:00.';

IF ORIGINAL_LOGIN() = 'test1' AND
    (DATEPART(HOUR, GETDATE()) < 10 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT @ErrorTxt;
        ROLLBACK;
    END
END
GO



Hope you like it.

COMMIT and ROLLBACK TRANSACTION in Triggers


One of my friends asks me to write an article related to COMMIT and ROLLBACK TRANSACTION in Triggers.
After reading one of my previous article
DELETE restriction of VIEWS
http://sqlknowledgebank.blogspot.in/2012/06/delete-restrictions-of-views.html
Where I am using a ROLLBACK TRAN in a trigger (INSTEAD OF DELETE trigger), he had a question on mid that without using the BEGIN TRANSACTION on the Trigger, can we us the ROLLBACK TRANSACTION. It is better, if you read my previous article to understand the complete scenario.

The executions of COMMIT TRANSACTION and ROLLBACK TRANSACTION inside the trigger are possible.
When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated.
If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.
However, in the case of TRIGGER, if BEGIN TRANSACTION is executed before or after the COMMIT TRANSACTION, the error does not occur.
In another scenario of TRIGGER, if BEGIN TRANSACTION is executed after the ROLLBACK TRANSACTION, the error does not occur.
Now I am given an example to illustrate my points. Here I am taking my previous article example to demonstrate it.

IF OBJECT_ID('emp_data') IS NOT NULL
   BEGIN
     DROP TABLE emp_data
   END
GO

CREATE TABLE emp_data
       (
         IDNO     INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,
         EMPNAME  VARCHAR(50) NOT NULL,
         EMPGRADE VARCHAR(1)  NOT NULL,
         CONSTRAINT CHK_emp_data_EMPGRADE
         CHECK (EMPGRADE IN('A','B','C'))
        )
      
GO  
-- Insert records

INSERT INTO emp_data
       (EMPNAME, EMPGRADE)
VALUES ('Sudip Das', 'A'),
       ('Joydeep Das', 'B'),
       ('Sangram Jit', 'B'),
       ('Tuhin Shinah', 'B'),
       ('Bhola', 'C')   
     
-- Display records

SELECT IDNO, EMPNAME, EMPGRADE
FROM   emp_data      
    
-- Output

IDNO  EMPNAME           EMPGRADE
1     Sudip Das         A
2     Joydeep Das       B
3     Sangram Jit       B
4     Tuhin Shinah      B
5     Bhola             C
   
-- Creation of VIEWS

IF OBJECT_ID('view_emp_data') IS NOT NULL
   BEGIN
     DROP VIEW  view_emp_data
   END
GO

CREATE VIEW  view_emp_data
AS
SELECT IDNO, EMPNAME, EMPGRADE
FROM   emp_data

GO

-- Display the Records of Views

SELECT IDNO, EMPNAME, EMPGRADE
FROM   view_emp_data

-- Output from View

IDNO  EMPNAME           EMPGRADE
1     Sudip Das         A
2     Joydeep Das       B
3     Sangram Jit       B
4     Tuhin Shinah      B
5     Bhola             C

Now I am creating the trigger with simple ROLLBACK TRANSACTION without any BEGIN TRANSACTION.        


IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
   BEGIN
     DROP TRIGGER trg_RestrictDEL
   END
GO
      
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
  BEGIN

       IF @@rowcount > 0
       BEGIN
          RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!',16, 2 )
          ROLLBACK TRANSACTION
       END
      
END
GO

Now I am trying to DELETE records
DELETE view_emp_data
WHERE IDNO=3
Please Look at the Error Message. The Error portion is highlighted here.
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Now I am trying to ALTER this Trigger with BIGIN TRANSACTION, immediate after the ROLLBACK TRANSACTION. We find no Error after execution. (Please try this)
IF OBJECT_ID('trg_RestrictDEL') IS NOT NULL
   BEGIN
     DROP TRIGGER trg_RestrictDEL
   END
GO
      
CREATE TRIGGER trg_RestrictDEL ON view_emp_data
INSTEAD OF DELETE
AS
  BEGIN

       IF @@rowcount > 0
       BEGIN
                 BEGIN TRAN
                 RAISERROR('Rows from Views Named: view_emp_data, CANNOT be DELETED!', 16, 2 )
                 ROLLBACK TRANSACTION
                 BEGIN TRANSACTION
       END
      
END
GO
Now execute the DELETE statement again.
DELETE view_emp_data
WHERE IDNO=3
Msg 50000, Level 16, State 2, Procedure trg_RestrictDEL, Line 10
Rows from Views Named: view_emp_data, CANNOT be DELETED!

No error message.


Trigger – DDL


When we think about trigger, we always think about DML trigger but DDL triggers are playing a very important role on database impact.

SQL Server 2005 introduced a new trigger called the DDL Trigger. DDL means "Data Definition Language" that is create table, alter table, create proc, etc. What's great about these new triggers is if they are setup on the database/server, you are able to capture the DDL statement and automatically log it to a change log. You have no idea how much easier that is than doing it manually. Also, the laziness in all of us gets a little boost.

The DDL trigger has 2 type of scope
1.    Server-scoped statements
2.    Database-scoped statements

In "server-scoped statement", the triggered worked on entire SQL server instance, no matter how many database exists within this instance.

But in the "database-scoped statements", the triggers work on a particular database.

Server-scoped statements

ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN
ALTER_LOGIN
DROP_LOGIN
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
Database-scoped statements
CREATE_APPLICATION_ROLE
ALTER_APPLICATION_ROLE
DROP_APPLICATION_ROLE
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
CREATE_CERTIFICATE
ALTER_CERTIFICATE
 DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE
ALTER_ROLE
DROP_ROLE
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA
ALTER_SCHEMA
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE
DROP_TYPE
CREATE_USER
ALTER_USER
DROP_USER
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
 CREATE_XML_SCHEMA_COLLECTION
 ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION

Syntax to Create DDL triggers

CREATE TRIGGER [name of trigger]
ON [scope (database|server)]
FOR [event]
    As
    --Trigger definition here…
An example to DLL Triggers
CREATE TRIGGER backup_procs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS

SET NOCOUNT ON

DECLARE @data XML
SET @data = EVENTDATA()

INSERT INTO dbo.eventslog
           (eventtype, objectname, objecttype, sqlcommand, username)
VALUES     (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO

The script above will create a DDL trigger called backup_procs. It will insert a row into the events log table when a proc is created, altered or dropped.
For better understanding of DDL trigger please refer to MSDN

http://msdn.microsoft.com/en-us/library/ms186406.aspx



ISTEAD OF INSERT trigger



First we create a table described bellow
CREATE TABLE t1
            (ids   varchar(1),
             sname varchar(50))

Now create a trigger on it (Normal Insert Trigger)
 CREATE TRIGGER t1_trig ON t1
 FOR INSERT
 AS
 INSERT INTO t1
 SELECT ids, sname FROM inserted

What happened when we insert a statement on table t1 like this
INSERT INTO t1
VALUES('1', 'ABC')
It affects 2 records,
Ids       sanme
1          ABC
1          ABC

Now replace the trigger with this
DROP TRIGGER t1_trig

CREATE TRIGGER t1_trig  on t1
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO t1
       SELECT ids, sname FROM inserted
END

Now insert the value
INSERT INTO t1
VALUES('1', 'DEF')
It affects 1 records,
Ids       sanme
1          DEF

Now got the differences? It works on SQL 2008 Only.
References : http://msdn.microsoft.com/en-us/library/ms175089.aspx


Is Trigger is a Bad performer.



I am reading some well-known article related to trigger. Someone told that he is not using trigger in his whole life of development. 
Someone told that the trigger is a bad performer etc..etc.

One of my friends also told me, why you use trigger. Use the stored procedure for good performance.

What they are taking about? Is it true?

Here I am just sharing my views with my readers with my limited knowledge in Database performance.

As my thought, nothing can take the place of trigger.

When the complexity arises it helps us to get the desired solutions. So the trigger is good but we should keep something in mind before making logic within triggers.

Triggers are not slow itself. The bad logic that we wrote inside the trigger makes it slow. For example using cursors, multi table travels within the trigger.  Triggers are compiled and don't returns any result set. Try to make the trigger as simple as possible.
The triggers maintain the referential interiority. Stored procedure is used to implement the business logic.

Hope you like it.

FOR | AFTER | INSTEAD OF


The syntax of the trigger is mentioned bellow:
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

In this article we are talking about the parameters
{ FOR | AFTER | INSTEAD OF }

FOR|AFTER
When all operations specified in the triggering SQL statement have executed successfully.
 All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified.
Most of the developer preferred AFTER Instead of FOR.

INSTEAD OF
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, we can define views on views where each view has its own INSTEAD OF trigger.
Suppose VIEW-A Is created from 2 base table named TABLE-1 and TABLE-2.
Now if we want to INSERT records in this VIEW-A, SQL server gives us an error.
Msg 4405, Level 16, State 1, Line 1
View or function 'dbo.View-A' is not updatable because the modification affects multiple base tables.

Example:
CREATE TABLE [dbo].[Table-1]
(      [ID]   [INT] NULL,
       [Name] [VARCHAR](20) NULL
)
GO
CREATE TABLE [dbo].[Table-2]
(      [ID]      [INT] NULL,
       [Address] [VARCHAR](200) NULL
)
GO
CREATE View [dbo].[View-A]
AS
SELECT a.ID, a.Name, b.Address
FROM   Table-1 a
       INNER JOIN Table-2 b ON a.ID1 = b.ID
             
INSERT INTO [dbo].[View-A]
            (ID, Name, Address)
VALUES      (1, 'Raja','Kolkata')   

  
Msg 4405, Level 16, State 1, Line 1
View or function 'dbo.View-A' is not updatable because the modification affects multiple base tables.
    
So what the options available.
To avoid this error and make a view modifiable we need to create Triggers on the view. These triggers will be used to 'pass' the changes to base tables.
For that we need to create a trigger on the view with INSTEAD OF options to save the record in the underlying base table.
Example of Trigger definition:
CREATE TRIGGER [dbo].[Trig_1]
ON [dbo].[View-A]
INSTEAD OF INSERT
AS
BEGIN
      INSERT INTO Table-1(ID, Name)
      SELECT I.ID, I.Name
      FROM INSERTED I
     
      INSERT INTO Table-2(ID, Address)
      SELECT I.ID, I.Address
      FROM INSERTED I
END

Now we can insert the records on the view.
If we also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

Note that:
INSTEAD OF not work with the VIEWS that have WITH CHECK OPTIONoptions. If your view has such problem alters your view first.
Hope this article is quite informative and thanking you to provide your valuable time on it.


Trigger – DDL ?


When we think about trigger, we always think about DML trigger but DDL triggers are playing a very important role on database impact.
SQL Server 2005 introduced a new trigger called the DDL Trigger. DDL means "Data Definition Language" that is create table, alter table, create proc, etc. What's great about these new triggers is if they are setup on the database/server, you are able to capture the DDL statement and automatically log it to a change log. You have no idea how much easier that is than doing it manually. Also, the laziness in all of us gets a little boost.
The DDL trigger has 2 type of scope
1.    Server-scoped statements
2.    Database-scoped statements
In "server-scoped statement", the triggered worked on entire SQL server instance, no matter how many database exists within this instance.
But in the "database-scoped statements", the triggers work on a particular database.
Server-scoped statements
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN
ALTER_LOGIN
DROP_LOGIN
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER
Database-scoped statements
CREATE_APPLICATION_ROLE
ALTER_APPLICATION_ROLE
DROP_APPLICATION_ROLE
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
CREATE_CERTIFICATE
ALTER_CERTIFICATE
 DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE
ALTER_ROLE
DROP_ROLE
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA
ALTER_SCHEMA
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE
DROP_TYPE
CREATE_USER
ALTER_USER
DROP_USER
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
 CREATE_XML_SCHEMA_COLLECTION
 ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION

Syntax to Create DDL triggers
CREATE TRIGGER [name of trigger]
ON [scope (database|server)]
FOR [event]
    As
    --Trigger definition here…
An example to DLL Triggers
CREATE TRIGGER backup_procs
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS

SET NOCOUNT ON

DECLARE @data XML
SET @data = EVENTDATA()

INSERT INTO dbo.eventslog
           (eventtype, objectname, objecttype, sqlcommand, username)
VALUES     (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO
The script above will create a DDL trigger called backup_procs. It will insert a row into the events log table when a proc is created, altered or dropped.
For better understanding of DDL trigger please refer to MSDN

Posted by: MR. JOYDEEP DAS 

Viewing Trigger Order (First and Last)


When I am going to see the trigger execution level, I find several article related to change the SQL server trigger execution lave by sp_settriggerorder
But I have a basic question in my mind, I have a table named Table-test and it contains 4 triggers when I am going to see the sp_helptrigger Table-test it display all the related trigger name. But I want to kanow which one is going to run first and which one is going to Last. I don't want to change any order.

I this this SQL statesment help you to understand which trigger is going to execute first and which one is going to execute last.

SELECT  name TriggerName,
                ROW_NUMBER() OVER (Order by modify_date Desc)ExecutionOrder
FROM     sys.triggers
WHERE  parent_id = object_id('Table-test')
ORDER BY modify_date DESC

Hope it is Informative.


Which Trigger Run First


When multiple trigger is set in a table object, definitely we have a question on mind that which trigger is going to first … which one is going to second and which one is going to last.
Definitely we can set the order of trigger execution, but with some limitations with it. We can set the first preference and last preference of trigger executions of MS SQL, but cannot set the preferences between them.
For an example Table-A Contains 4 triggers, Trigger-1 to Trigger-4. We can set that Trigger-2 is running first and Trigger-1 is running last. But we cannot set the preference of Trigger-3, Trigger-4.  They fire by undefined order.
MS SQL provide us  a system stored procedure to set the executions preferences of trigger.
sp_settriggerorder
       [ @triggername = ] '[ triggerschema. ] triggername'
     , [ @order = ] 'value'
     , [ @stmttype = ] 'statement_type'
       [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

[ @triggername= ] '[ triggerschema.] triggername'
It contains the name of triggers. If the Instead of trigger name specified an error.  triggerschema cannot be specified for DDL or logon triggers.
[ @order= ] 'value'
Setting the Order of Executions

Value
Description
First
Trigger is fired first.
Last
Trigger is fired last.
None
Trigger is fired in undefined order.


[ @stmttype= ] 'statement_type'

It can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement event listed in  DDL events.



@namespace = { 'DATABASE' | 'SERVER' | NULL }

When triggername is a DDL trigger, specifies whether triggername was created with database scope or server scope. If triggername is a logon trigger, SERVER must be specified.
Posted by at
Labels: