SQL Trigger

SQL Trigger

Trigger allows us to execute a batch of SQL command (or database object or PL/SQL block) on either insert, update or delete operation on a specified table.

It is a special type of procedure which is executed on insert , update or delete operation.

Types of Trigger

There are four types of trigger

1)      DDL trigger

2)      DML trigger

3)      CLR trigger

4)      Logon trigger

DDL Trigger

DDL trigger is executed on data definition language statements like create, alter and drop statement.

It is also executed on certain system defined stored procedures like create login statement and sp_addlogin stored procedure.

DML Trigger

DML trigger is executed on data manipulation language statement like Insert, Update and Delete command and the stored procedures that define the DML command.

There are two types of DML command.

1)      After trigger (using for/after clause)

2)      Instead of trigger (using instead of clause)

After Trigger

This type of trigger is get executed after the SQL server successfully finishes the execution of PL/SQL block.

Example: If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails,

SQL Server will not fire the After Trigger.

Before Trigger

This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differing from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

Example: If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

CLR Trigger

CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#. We coded the objects (like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#.

Logon Trigger

Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define

In the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

CREATE TRIGGER trigger_name

ON ALL SERVER

[WITH ENCRYPTION]

{FOR|AFTER} LOGON

AS

sql_statement [1...n ]

Syntax

1.  CREATE TRIGGER trigger_name

2.  ON {table|view} 

3.  [WITH ENCRYPTION|EXECUTE AS] 

4.  {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 

5.  [NOT FOR REPLICATION] 

6.  AS 

7.  sql_statement [1...n ] 

AFTER triggers cannot be defined on views.

Trigger

Instead-of-Trigger-Example.html

CREATE TABLE Employee_Demo

(

 Emp_ID int identity,

 Emp_Name varchar(55),

 Emp_Sal decimal (10,2)

)

Insert into Employee_Demo values ('Amit',1000);

Insert into Employee_Demo values ('Mohan',1200);

Insert into Employee_Demo values ('Avin',1100);

Insert into Employee_Demo values ('Manoj',1300);

Insert into Employee_Demo values ('Riyaz',1400);

create table Employee_Demo_Audit

(

 Emp_ID int,

 Emp_Name varchar(55),

 Emp_Sal decimal(10,2),

 Audit_Action varchar(100),

 Audit_Timestamp datetime

)

CREATE TRIGGER trgAfterInsert on Employee_Demo

FOR INSERT

AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values (@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);

select * from Employee_Demo

select * from Employee_Demo_Audit