Triggers in SQL Server 2005

Triggers are the database objects which fire on some event on table or view to perform some specific task.

Triggers are used of three types in terms of language statements and these are DDL, DML and LOGON triggers.

DDL – Data definition languages such as Alter table, Create table etc.

DML – DML triggers are fire on insert, update or delete operation on table.

LOGON – LOGON Triggers fires on the login n the application i.e. fires on the basis of session id.

Generally we care about DML triggers i.e. triggers fires after the insert update or delete operations on a table.

Further triggers can be classified in two types.

1.      Instead of Triggers.

 As per my understanding from different eBooks online Instead of trigger fires instead of the sql statement which is firing the trigger. I.e. the sql statement which is performing some event on table will be skipped and instead of trigger defined on table will do the operation and the sql statement will not be able to do the change. Instead of trigger can be used for views.

2.      After Trigger.

After trigger performs the operation on the table after the execution of sql statement which is performing some even. I.e. is a sql statement is doing update then after the updating the table the after trigger will fire. After trigger cannot be used for views.

There are two magic tables in sql server which are used in triggers to get the update data and earlier data.

Deleted: contains the columns which were deleted or modified in the table in last action. Using this data in triggers logic the task can be performed.

Inserted: It contains the latest data which is inserted or updated in the table.

General Syntax for creating the trigger.

--CREATE A SAMPLE TABLE PERSION

GO

/****** OBJECT:  TRIGGER [DBO].[T1]    SCRIPT DATE: 01/23/2011 12:06:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- AUTHOR:        <AUTHOR,,NAME>

-- CREATE DATE: <CREATE DATE,,>

-- DESCRIPTION:   <DESCRIPTION,,>

-- =============================================

ALTER TRIGGER [DBO].[T1]

   ON [DBO].[PERSON]

   AFTER UPDATE

AS

BEGIN

      -- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM

      -- INTERFERING WITH SELECT STATEMENTS.

      SET NOCOUNT ON;

      DECLARE @VAR VARCHAR(100)

SELECT @VAR= NAME FROM DELETED

UPDATE PERSON

SET ADDRESS=@VAR

    -- INSERT STATEMENTS FOR TRIGGER HERE

END