In this article, I am going to explain you AFTER Trigger in SQL-Server with example. In this demo, we will learn only AFTER trigger (DML trigger) and remaining I will be explaining in later articles.

What is Trigger is SQL-Server

A trigger is a special kind of stored procedure that invokes or gets executed in response to certain action on Table like insert, update or delete. You can not explicitly invoke triggers.

Types of Trigger

Below is types of triggers.1. DML Triggers (Data Manipulation Language Triggers)2. DDL Triggers (Data Definition Language Triggers)3. CLR Triggers4. Logon Triggers

DML Triggers

DML triggers are fired automatically in response to DML events that is INSERT, UPDATE and DELETE.Below is types of DML triggers.1. After Triggers (also called as FOR trigger)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.AFTER Insert (for insert)AFTER Update (for update)AFter Delete (for delete)

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/delete 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. INSTEAD of InsertINSTEAD of UpdateINSTEAD of Delete

AFTER Insert Trigger (for insert) with Example

We will be discussing only after insert trigger in this demo. First of all, create a table Employee with three columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

create table Employee
(
EmployeeId int primary key identity,
Name varchar(20),
Age int
)
go
insert into Employee values('Mr. A',50)
insert into Employee values('Mr. B',55)
insert into Employee values('Mr. C',60)
insert into Employee values('Mr. D',65)
insert into Employee values('Mr. E',70)
go
select * from Employee

Now, we will create a Audit Table for Employee table. When somebody insert record into Employee table, then we will insert same record in audit table by using after trigger. Below is the script to create Employee_Audit table.

create table Employee_Audit
(
Id int identity primary key,
EmployeeId int,
Name varchar(20),
Age int,
InsertedDate datetime
)

Creating Trigger

Now, we will create a trigger. Below is the script to create after insert trigger.

create trigger tr_Employee_AfterInsert
on Employee
after insert   --(you can also use 'for insert')

as
begin
declare @EmployeeId int,
@Name varchar(20),
@Age int

select @EmployeeId=i.EmployeeId,@Name=Name,@Age=Age from inserted as i

insert into Employee_Audit values(@EmployeeId,@Name,@Age,GETDATE())

end

When you insert a record into Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing Trigger

As we can not execute triggers explicitly, so we will insert a new record Employee table. When you insert new record into Employee table, after insert trigger will be fired and a new record will be inserted into Employee_Audit table as shown below. Initially we are having only 5 record in Employee table as shown above.

Now we will insert a new record in Employee table.

insert into Employee values('Mr. G',75)

As you can see below a new record will be inserted into both tables Employee and Employee_Audit.

DISABLE and ENABLE Triggers

We can disable and enable trigger using SQL Server Management Studio or using SQL below query.1. Using Management StudioExpand your database → go to Tables and expand it → Go to your respective table and expand it → go to Triggers → Right click on trigger name and now you can modify, disable and delete ..etc.2. SQL Query

--To disable
DISABLE TRIGGER tr_Employee_AfterInsert on Employee
--To enable
ENABLE TRIGGER tr_Employee_AfterInsert on Employee

--Before enabling/disabling you can check if exists
IF OBJECT_ID ('tr_Employee_AfterInsert', 'TR') IS NOT NULL
begin
   DISABLE TRIGGER tr_Employee_AfterInsert on Employee
   print 'Trigger disabled'
end
else
begin
   print 'No trigger found'
end

DROP Triggers

We can drop or delete triggers using SQL Server Management Studio or using SQL below query.1. Using Management StudioAlready explained. 2. SQL Query

--To delete or drop
DROP TRIGGER tr_Employee_AfterInsert

--Before deleting you can check if exists
IF OBJECT_ID ('tr_Employee_AfterInsert', 'TR') IS NOT NULL
begin
DROP TRIGGER tr_Employee_AfterInsert
   print 'Trigger deleted'
end
else
begin
   print 'No trigger found'
end