TRIGGER IN SQL SERVER

TRIGGER

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers

In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.

1. DDL Triggers

In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.

We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.

DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.

2.  DML Triggers

In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types

1.  After Trigger (using FOR/AFTER CLAUSE)

This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.

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.

2.  Instead of Trigger (using INSTEAD OF CLAUSE)

This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ 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.

3. CLR Triggers

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#. I will discuss CLR trigger later.

4. Logon Triggers

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.

Synatx for Logon Trigger

  CREATE TRIGGER trigger_name

  ON ALL SERVER

 [WITH ENCRYPTION]

  {FOR|AFTER} LOGON

  AS

sql_statement [1...n ]

Syntax for Trigger

  CREATE TRIGGER trigger_name

 ON {table|view}

  [WITH ENCRYPTION|EXECUTE AS]

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

 [NOT FOR REPLICATION]

 AS

  sql_statement [1...n ]

1.   trigger_name

This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.

2.      table|view

This is the table/view on which the trigger is to be created.

3.   ENCRYPTION

This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.

4.    EXECUTE AS

This option is optional. This option specifies, the security context under which the trigger is executed.

5.    FOR/AFTER

FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views.

6.   INSTEAD OF

INSTEAD OF specifies that the trigger is Instead Of Trigger.

7.  CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.

8.   NOT FOR REPLICATION

Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

9.    AS

After this we specifies the actions and condition that the trigger perform.

10.   sql_statement

These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements. The trigger actions specified in the T-SQL statements.

----Create Database------

create database dbtrigger

use dbtrigger

----------------Create table----------------

create table emp(id int primary key,Name varchar(50), Salary varchar(50))

------------- Now Insert Records--------

insert into emp values(2,'mohit',200),(3,'naveen',300),(4,'Vinod',500)

select * from emp

select * from emp_Audit

---Now we create table emp_Audit for logging/ backup Purpose of table emp_Audit create table ------

create table emp_Audit(

id int,

name varchar(50),

salary varchar(50),

 Audit_Action decimal(10,2),

 Audit_Time datetime)

 drop table emp_Audit.

-------Create Trigger on Table Emp_Audit for insert statement--------

Create TRIGGER Trigger_After_Insert ON emp

FOR INSERT

AS

      declare @id int;

      declare @name varchar(100);

      declare @salary decimal(10,2);

      declare @audit_action varchar(100);

      select @id=i.ID from inserted i;   

      select @name=i.Name from inserted i;     

      select @salary=i.Salary from inserted i; 

      set @audit_action='Inserted Record -- After Insert Trigger.';

      insert into Audit_action

           (id ,name ,salary,Audit_Action ,Audit_Time)

      values(@id,@name,@salary,@audit_action,getdate());

      PRINT 'AFTER INSERT trigger fired.'

Go

-----------------Insert Record in emp---------------

select * from emp

insert into Emp values(8,'Dya',20);

select * from Emp_action

----------------------Create table----------

CREATE TABLE Employee_Test

(

Emp_ID INT Identity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

------Insert values-----

INSERT INTO Employee_Test VALUES ('Santosh',1100),('naveen',1200),('rini',1500)

,('bharti',1800),('Mukesh',1900)

drop table Employee_Test

-------------Action Table---------------------

CREATE TABLE Employee_Test_Audit

(

Emp_ID int,

Emp_name varchar(100),

Emp_Sal decimal (10,2),

Audit_Action varchar(100),

Audit_Timestamp datetime

)

1-------------Create Insert trigger-----------

CREATE TRIGGER triggetAfterInsertt ON [dbo].[Employee_Test]

FOR INSERT

AS

      declare @empid int;

      declare @empname varchar(100);

      declare @empsal decimal(10,2);

      declare @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_Test_Audit

           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

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

      PRINT 'AFTER INSERT trigger fired.'

----Insert Record Main Table-----------

select * from Employee_Test_Audit

insert into Employee_Test values('Mohit',200)

select * from Employee_Test

------------2.After Update Trigger ---------------

------------2.After Update Trigger ---------------

CREATE TRIGGER TriggerAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

AS

      declare @empid int;

      declare @empname varchar(100);

      declare @empsal decimal(10,2);

      declare @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;

     

      if update(Emp_Name)

            set @audit_action='Updated Record -- After Update Trigger.';

      if update(Emp_Sal)

            set @audit_action='Updated Record -- After Update Trigger.';

      insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

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

      PRINT 'AFTER UPDATE Trigger fired.'

GO

-------update Record in table ------------

update Employee_Test set emp_name='Kavita' where emp_ID=1

update Employee_Test set emp_name='ram' where emp_ID=2

update Employee_Test set emp_name='Rohit' ,emp_sal=500 where emp_ID=10

select * from Employee_Test
-------------------------------------second Example--------

--------------Next practices------After Update Trigger---------------

create Trigger AfterUpdateTr

On emp

For Update

as

Begin

Select * from deleted;

Select * from inserted

End

-----

select * from emp

select * from emp_Audit

Update emp set Name='Kamlesh' where id=3

------------------------------------------------

-------Create Trigger for After Delete------

create Trigger Afterdeletetrigger on  dbo.Employee_Test

 for Delete

 as

 declare @emp_id int

 declare @emp_name varchar(20)

 declare @emp_sal varchar(100)

 declare @audit_action varchar(100);

 select @emp_id = d.emp_id from deleted  d;

 select @emp_name =d.emp_name from deleted d;

 select @emp_sal =d.emp_sal from deleted d;

 set @audit_action ='Deleted ---- After Delete  Trigger '

 insert into Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

 values(@emp_id,@emp_name,@Emp_Sal,@audit_action,GETDATE())

 Print 'After Deleted Trigger Fired'

---------------------------delete record in table----

 select * from Employee_Test_Audit

 select * from Employee_Test

 delete from Employee_Test where emp_id=10

 insert into Employee_Test values('Ram',8000)

1------Instead Of Trigger For Delete-------------

 CREATE TRIGGER Trigger_InsteadOf_Delete ON dbo.Employee_Test

INSTEAD OF DELETE

AS

      declare @emp_id int;

      declare @emp_name varchar(100);

      declare @emp_sal int;

     

      select @emp_id=d.Emp_ID from deleted d;

      select @emp_name=d.Emp_Name from deleted d;

      select @emp_sal=d.Emp_Sal from deleted d;

      BEGIN

            if(@emp_sal>1200)

            begin

                  RAISERROR('Cannot delete where salary > 1200',16,1);

                  ROLLBACK;

            end

            else

            begin

                  delete from Employee_Test where Emp_ID=@emp_id;

                  COMMIT;

                  insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

                  values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

                  PRINT 'Record Deleted -- Instead Of Delete Trigger.'

            end

      END

----insert Record in table using Instead of Trigger----

delete from Employee_Test where Emp_ID=1;

select * from Employee_Test

select * from Employee_Test_Audit