How to create a trigger in sqlserver2005

 Hi Selvi,

A trigger is a pre-programmed notification that performs a set of actions that may be commonly required. Triggers can be programmed to execute certain actions before or after an event occurs. Triggers are very useful as they increase efficiency and accuracy in performing operations on databases and also are increase productivity by reducing the time for application development. Triggers however do carry a price in terms of processing overhead.

A trigger is a special type of stored procedure that automatically takes effect when the data in a specified table is modified.

A trigger is invoked in response to an INSERT, UPDATE or DELETE Statement.

A trigger can query other tables and can include complex SQL statements.

The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If error is detected.

How are triggers useful?

•Triggers can cascade changes through related tables in the database.

•Triggers can enforce restrictions that are more complex than those defined with check constraints.

•Triggers can also evaluate the state of a table before and after a data modification.

•Multiple triggers of the same type (INSERT, UPDATE or DELETE) On a table allow multiple, different actions to take place in response to the same modification statement.

•The primary benefit of triggers is that they can contain complex processing logic that uses SQL code.  Hence triggers can support all of the functionality of constraints. (triggers are not always best method for a given feature)

 

Types of Triggers

In SQL Server 2008 you have two types of triggers

Data Manipulation Language Triggers (DML)

Data Definition Language Triggers  (DDL) or Logon Trigger

DML Triggers existed in previous editions of SQL server, but DDL triggers are on of the key new features that will ease your work 

The first ingredient in properly employing triggers is to understand the differences between AFTER and INSTEAD OF Triggers.

AFTER trigger execute following the triggering action, such as an insert, update or delete. Basically AFTER triggers fire very late in the process.

INSTEAD OF triggers, introduced with SQL server 2000, are intended to be employed in different situations, INSTEAD OF triggers force in place of the triggering action.

Trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

Triggers Dos and Donts

DML trigger cannot be defined on local or global temporary tables

A view can be referenced only by an INSTEAD OF trigger

With Encryption: Encapsulates Create Trigger definition , with encryption cannot be specified for CLR triggers 

After: specifies that DML trigger is fred only when all operations specified in the triggering SQL statement have executed successfully.

After triggers cannot be defined on views

Instead of specifies that the DML trigger is executed 

Instead of cannot be specified for DDL or Logon triggers 

At most one Instead of Trigger per Insert, Update or Delete statement can be defined on a table or view.

However you can define views on views where each view has its own Instead of trigger.

Instead of triggers are not allowed on updatable views that use WITH CHECK option

For Instead if triggers, the Delete option is not allowed on tables that have  a referential relationship specifying a cascade action on DELETE.

WITH APPEND: specifies that an additional trigger of an existing type should be added. With append cannot be used for Instead of Triggers (will be removed in coming versions)

 

Create trigger must be the first statement in the batch and can apply to only one table.

Trigger can be created only in the current database, but it can refere objects outside the current database

Same trigger action can be defined for more than one user action 

Instead of Delete/Update triggers cannot be defined on a table that has a foreign key with a cascade on Delete/Update action defined

Any Set statement can be specified inside a trigger, after executing trigger it will set to former setting

Truncate table statement is in effect a Delete statement, but it does not activate a trigger, because the operation does not log individual rows.

Create Index DBCC DBREINDEX, ALTER Table are not allowed inside triggers

 Triggers can be nested to a maximum of 32 levels to disable nexted triggers set the nested_triggers option to 0 (off) 

Recursive triggers is also off if nested trigger is off

DML Triggers existed in previous editions of SQL server, but DDL triggers are on of the key new features that will ease your work 

The first ingredient in properly employing triggers is to understand the differences between AFTER and INSTEAD OF Triggers.

AFTER trigger execute following the triggering action, such as an insert, update or delete. Basically AFTER triggers fire very late in the process.

INSTEAD OF triggers, introduced with SQL server 2000, are intended to be employed in different situations, INSTEAD OF triggers force in place of the triggering action.