Let's play with Trigger

A trigger is a database object which is attached to table and similar to stored procedure. The main difference between triggers and stored procedures is:-
1- Triggers do not accept parameter whereas procedure can.
2- A trigger is executed implicitly and to execute a procedure, it has to be explicitly called by user.

How to apply database triggers
A trigger has three basic parts:-
1- A triggering statement
2- A trigger restriction
3- A trigger action

Triggering Statement:-
It is a sql statement that causes a trigger to be fired. It can be insert, update or delete statement for a specific table.

Trigger restriction:-
A trigger restriction specifies a logical expression that must be TRUE for the trigger to fire. A trigger restriction specifies using WHEN clause.

Trigger Action:-
A trigger action code is executed when a triggering statement is encountered and any trigger restirction evaluates to true.

Type of triggers
Row Triggers
A row trigger is fired each time a row in the table is affected by the triggering statement. For example, if an update statement updates multiple rows of a table, a row trigger is fired once for each row affected by the update statement.

Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects. Statement triggers should be used when a triggering statement affects rows in a table but the processing required is completely independent of the number of rows affected.

Before V/s After triggers
BEFORE triggers executes the trigger action before the triggering statement. These types of triggers are commonly used in the following situations:-

a) BEFORE triggers are used when the trigger action should determine wheather or not the triggering statement should be allowed to complete.

b) BEFORE triggers are used to derive specifc column values before completing triggering insert or update statement.

AFTER trigger executes the trigger action after the triggering statement is executed. These types of triggers are commonly used in the following situations:-

a) AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.

b) If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

The following example demonstrate how to create trigger that displayes the current system time when a row is inserted into the table.

SET NOCOUNT ON
CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
GO
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
GO
INSERT Source (Sou_Desc) VALUES ('Test 1')
-- Results --
Apr 17 2010 9:56AM

When to use triggers
We should use trigger when we need to perform a certain action as a result of an INSERT, UPDATE or DELETE is used.

CREATE TABLE Orders (Ord_ID int IDENTITY, Ord_Priority varchar(10))
GO
CREATE TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') = 1
BEGIN
PRINT 'Email Code Goes Here'
END
GO
INSERT Orders (Ord_Priority) VALUES ('High')
-- Results --
Email Code Goes Here