SQL Server Trigger

"A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs."

 

If you write a trigger for an insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in memory. Then it does the insert operation and then the statements inside the trigger executes. We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger.

 

Similarly, if you write a trigger for a delete operation on a table, it creates a table in memory named “DELETED” and then deletes the row.

 

Why and when to use a trigger

 

We use a trigger when we want some event to happen automatically on certain desirable scenarios.

 

Let's see an example

 

You have a table that changes frequently, now you want to know how many times and when these changes take place.

 

In that case you can create a trigger that will insert the desired data into another table whenever any change in the main table occurs.

 

Types of Triggers

 

In SQL Server we can create the following 3 types of triggers:

• Data Definition Language (DDL) triggers
• Data Manipulation Language (DML) triggers
• Logon triggers

DDL Triggers

 

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

 

DML Triggers

 

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

 

After trigger (using FOR/AFTER CLAUSE)

 

The After trigger (using the FOR/AFTER CLAUSE) fires after SQL Server finishes the execution of the action successfully that fired it.

 

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

 

Instead of Trigger (using INSTEAD OF CLAUSE)

 

The Instead of Trigger (using the INSTEAD OF CLAUSE) fires before SQL Server starts the execution of the action that fired it. This is different from the AFTER trigger that 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 a record/row into 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.

 

Logon Triggers

 

Logon triggers are a special type of triggers that fire when a 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.

 

In short, the following are the various types of triggers.

 

DML

• Instead of Trigger: An Instead of trigger is fired instead of the triggering action such as an insert, update, or delete
• After Trigger: An After trigger executes following the triggering action, such as an insert, update or delete

DDL Trigger

 

This type of trigger is fired against DDL statements like Drop Table, Create Table or Alter Table. DDL Triggers are always After Triggers.

 

Logon trigger

 

This type of trigger is fired against a LOGON event before a user session is established to the SQL Server.

 

Syntax

 

The following is the very easy and useful syntax of triggers:

1. CREATE TRIGGER triggerName ON table   
2. AFTER INSERT |After Delete |After Upadte  
3. AS BEGIN  
4.   
5. INSERT INTO dbo.UserHistory............  
6.   
7. END   

Example

 

1. Create a Table:

1. CREATE TABLE Employee_Test  
2. (  
3. Emp_ID INT Identity,  
4. Emp_name Varchar(100),  
5. Emp_Sal Decimal (10,2)  
6. )  

2. Insert records into it:

1. INSERT INTO Employee_Test VALUES ('Anees',1000);  
2. INSERT INTO Employee_Test VALUES ('Rick',1200);  
3. INSERT INTO Employee_Test VALUES ('John',1100);  
4. INSERT INTO Employee_Test VALUES ('Stephen',1300);  
5. INSERT INTO Employee_Test VALUES ('Maria',1400);  

3. Create another table to store transaction records, like records of insert, delete and update on the Employee_Test Table:

1. CREATE TABLE Employee_Test_Audit  
2. (  
3. Emp_ID int,  
4. Emp_name varchar(100),  
5. Emp_Sal decimal (10,2),  
6. Audit_Action varchar(100),  
7. Audit_Timestamp datetime  
8. )  

4. Now a create trigger:

1. CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]   
2. FOR INSERT  
3. AS  
4. declare @empid int;  
5. declare @empname varchar(100);  
6. declare @empsal decimal(10,2);  
7. declare @audit_action varchar(100);  
8. select @empid=i.Emp_ID from inserted i;   
9. select @empname=i.Emp_Name from inserted i;   
10. select @empsal=i.Emp_Sal from inserted i;   
11. set @audit_action='Inserted Record -- After Insert Trigger.';  
12.   
13. insert into Employee_Test_Audit  
14. (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)   
15. values(@empid,@empname,@empsal,@audit_action,getdate());  
16.   
17. PRINT 'AFTER INSERT trigger fired.'  
18. GO  

5. Now insert a record into Employee_Test:

1. insert into Employee_Test values('Ravi',1500);  

6. Then see the Employee_Test_Audit table:

1. Select * from Employee_Test_Audit