SQL Triggers - An Introduction

SQL Triggers - An Introduction

Introduction

Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special kind of stored procedures. Before describing the types of triggers, we should first understand the Magic tables which are referenced in triggers and used for reuse.

Magic Tables

    There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server internal tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:

Action	Inserted	Deleted
Insert	Table contains all the inserted rows	Table contains no row
Delete	Table contains no rows	Table contains all the deleted rows
Update	Table contains rows after update	Table contains all the rows before update

DML Triggers
       

Types of trigger
   In SQL Server, there are two types of triggers which are given below:-

1.    After Triggers
2.    Instead of Triggers

After Trigger (using FOR/AFTER CLAUSE)

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

Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different 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/delet to the table.

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

Example

For Example purposes we create two table 1. Employee_Demo 2. Employee_Demo_a

 -- First create table Employee_Demo
CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
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);
--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime
) 

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1. After Insert Trigger

 -- Create trigger on table Employee_Demo for Insert statement
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.'
--Output will be 

 --Now try to insert data in Employee_Demo table
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
--Output will be 

 --now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

2.After Update Trigger

 -- Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
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; if update(Emp_Name)
 set @audit_action='Update Record --- After Update Trigger.';
if update (Emp_Sal)
 set @audit_action='Update Record --- After Update Trigger.';
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE trigger fired.'
--Output will be 

 --Now try to upadte data in Employee_Demo table
update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
--Output will be 

 --now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

3. After Delete Trigger

1. 
    -- Create trigger on table Employee_Demo for Delete statement
   CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
   FOR DELETE
   AS
   declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
   select @empname=d.Emp_Name from deleted d;
   select @empsal=d.Emp_Sal from deleted d;
   select @audit_action='Deleted -- After Delete 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 DELETE TRIGGER fired.'
   --Output will be 
   
   

   

   
    --Now try to delete data in Employee_Demo table
   DELETE FROM Employee_Demo where emp_id = 5
   --Output will be 
   
   

   

   
    --now select data from both the tables to see trigger action
   select * from Employee_Demo
   select * from Employee_Demo_Audit
   --Output will be 
   
   

   
   Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example

1. Instead of Insert Trigger

 -- Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN 
 BEGIN TRAN
 SET NOCOUNT ON
 if(@emp_sal>=1000)
 begin
 RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
 else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
 COMMIT;
 PRINT 'Record Inserted -- Instead Of Insert Trigger.'
END
--Output will be 

 --Now try to insert data in Employee_Demo table
insert into Employee_Demo values ('Shailu',1300)
insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
--Outputs will be 

 

 --now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.

2. Instead of Update Trigger

 -- Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
BEGIN
 BEGIN TRAN
if(@emp_sal>=1000)
 begin
 RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
 else begin 
 insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
 COMMIT;
 PRINT 'Record Updated -- Instead Of Update Trigger.'; END
--Output will be 

 --Now try to upadte data in Employee_Demo table
update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
update Employee_Demo set Emp_Sal = '900' where emp_id = 6
--Output will be 

 

 --now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.

3. Instead of Delete Trigger

 -- Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
INSTEAD OF DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
BEGIN TRAN if(@empsal>1200) begin
 RAISERROR('Cannot delete where salary > 1200',16,1);
 ROLLBACK;
 end
 else begin
 delete from Employee_Demo where Emp_ID=@empid;
 COMMIT;
 insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
 values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
 PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
--Output will be 

 --Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 1
DELETE FROM Employee_Demo where emp_id = 3
--Output will be 

 

 --now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.

Nested Triggers

Nested Trigger: - In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table. 
For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates.
 Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.

We can also stop the execution of nested triggers through the following SQL Command:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Recursive triggers
  In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. In SQL Server, we have two types of recursion.

1.   Direct recursion
2.   Indirect recursion

  In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively.
  In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables.

Please note: Recursive trigger can only be possible when the recursive trigger option is set.

Recursive trigger option can be set using the following SQL Command:

ALTER DATABASE databasename 
SET RECURSIVE_TRIGGERS ON | OFF

How to find the Triggers in a database

1)  Finding all the triggers defined on whole the database
Suppose we want to get the list of all the triggers and their respective tables name then we can use the following SQL Statement.

 select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'

2) Finding all the triggers defined on a particular table

 For example if we want to find out all the triggers created on the table Customer then we can use the following SQL Statement:-

sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'

3)  Finding the definition of a trigger

    Suppose if we want to find out the definition of the trigger, we can use the following SQL Statement:-

   sp_helptext triggername
For example:-
  sp_helptext 'trig_custadd'