In this article, I will give you a good explanation of Triggers and I will also show you how you can use it.

A Trigger is a database object that is fired in result of DML statements. It is fired automatically when an associated DML statement like UPDATE, INSERT and DELETE is executed. Trigger is a named procedural code that is fired in response to a particular event. That particular event can be associated with a table or a view. Statements that fires Trigger and the Trigger itself are treated as single transaction and this transaction can be rolled back within the Trigger. Trigger is often called as special kind of stored procedure and it is something like stored procedure with a basic difference. Trigger fires automatically in response of certain events and we cannot invoke triggers explicitly. The only way to invoke trigger is to perform DML statement that is associated with it. Triggers cannot be called directly from your application like stored procedures. They are invoked only in a result of INSERT, UPDATE and DELETE operation. The definition of Trigger is stored in database management system and it can only be invoked by database management systems. Trigger functionality can be used inside a stored procedure to fire it in response of DML statements.

When a Trigger is fired in response of a DML statement, it can execute another DML statement. It means a Trigger can result of firing another Trigger. This situation is called nested Trigger. In nested Trigger, a Trigger can contain INSERT, UPDATE and DELETE statements inside it. SQL Server 2005 and later also supports Triggers for Data Definition Language which fires in response of CREATE, ALTER and DROP statements.

Uses and Benefits of Triggers

Triggers are used to restrict access to specific data.

They are used to implement data integrity.

Triggers are used when we need to perform certain actions in response of any DML statement.

They are helpful to enforce business rules and they can catch errors in business logic in database.

They can be used to perform cascading update or delete operations.

You can perform logging in database using Triggers.

Triggers can prevent inconsistent, incorrect and unauthorized changes to data.

They are also useful when you want to audit changes of data in table.

You can use Triggers to gather statistics on a table access.

Disadvantages of Trigger

Triggers are stored in complied form like store procedure. Trigger code is complied every time when it is invoked.

They execute invisibly from application which used database so sometimes it can be difficult to understand what happened.

Triggers cannot be used with SELECT statement.

There is no version control system available for multiple versions of Triggers on a Table.

Types of Triggers

There are basically two types of Triggers.

After Trigger

Instead of Trigger

Both these Triggers are used with DML statements and DDL statements

If you know how to create stored procedure, there will no problem for creating Triggers. General syntax for Triggers can be like this:

CREATE TRIGGER trigger_name

ON table_name

FOR {AFTER [OR] INSTEAD OF}

{INSERT [OR] | UPDATE [OR] | DELETE}

WHEN (condition)

BEGIN

--- SQL statements

END

After Trigger

After Trigger executes after an INSERT, UPDATE or DELETE statement on a database table. These triggers cannot be used with Views. After Triggers never fires if a constraint violation occurs. DML After Triggers are divided into further types.

After Insert Trigger

It executes after an INSERT statement execution.

Following Trigger prevent user to insert record into Products table.

CREATE TRIGGER PreventInsertProduct

ON Products

AFTER INSERT

AS

BEGIN

ROLLBACK TRANSACTION

END

After Update Trigger

This Trigger fires after an UPDATE statement on database table.

Following Trigger don’t allow users to update product name in Products table.

CREATE TRIGGER PreventChangeProductName

ON Products

AFTER UPDATE

AS

IF UPDATE(ProductName)

BEGIN

ROLLBACK TRANSACTION

END

After Delete Trigger

It executes after a DELETE statement on a table.

Following Trigger will prevent user to delete product from Products table.