Overview of SQL Server Database Trigger

A trigger is special kind of Stored Procedure or stored program that is automatically fired or executed when some event (such as insert, delete or 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 the 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 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 the changes took place. In that case you can create a trigger that will insert the desired data into another table whenever a change in your main table occurs.

Types of triggers

In SQL Server we can create four types of triggers. They are Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and 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.

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

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

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

DML Triggers

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

After Trigger (using FOR/AFTER CLAUSE)

This type of trigger 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)

This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differs 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 is successfully executed but does not include the actual insert/update/delete to the table.

Example: If you insert a 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 constraints and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

CLR Triggers

CLR triggers are special types of triggers that are based on the Common Language Runtime (CLR) in the .Net Framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of the .NET languages, like C#, Visual Basic and F#.

We coded the objects (like triggers) in the CLR that has heavy computations or needs 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 explain CLR triggers later.

Logon Triggers

Logon triggers are special types 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.

Short Description Of Various Type of trigger

There are the following three 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: After trigger is excuted 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

CREATE TRIGGER triggerName ON table
AFTER
INSERT |
After
Delete |
After
Upadte AS BEGIN INSERT INTO dbo.UserHistory............END

Example

CREATE TABLE Employee_Test (
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10, 2)
) INSERT INTO Employee_Test
VALUES
('Anees', 1000);
INSERT INTO Employee_Test
VALUES
('Rick', 1200);
INSERT INTO Employee_Test
VALUES
('John', 1100);
INSERT INTO Employee_Test
VALUES
('Stephen', 1300);
INSERT INTO Employee_Test
VALUES
('Maria', 1400);

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10, 2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

CREATE TRIGGER trgAfterInsert 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.' GO

Check

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