TRIGGERS

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.
Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.

Implementing Triggers in SQL Server 2000

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.
There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers. These triggers differ from each other in terms of their purpose and when they are fired. In this article we shall discuss each type of trigger.
First of all, let's create a sample database with some tables and insert some sample data in those tables using the script below:

Create Database KDMNN
GO

USE KDMNN
GO

CREATE TABLE [dbo].[User_Details] (
[UserID] [int] NULL ,
[FName] [varchar] (50) NOT NULL ,
[MName] [varchar] (50) NULL ,
[LName] [varchar] (50) NOT NULL ,
[Email] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Master] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) NULL ,
[Password] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User_Master] WITH NOCHECK ADD
CONSTRAINT [PK_User_Master] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User_Details] ADD
CONSTRAINT [FK_User_Details_User_Master] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[User_Master] (
[UserID]
)
GO

INSERT INTO USER_MASTER(USERNAME, PASSWORD)
SELECT 'Navneeth','Navneeth' UNION
SELECT 'Amol','Amol' UNION
SELECT 'Anil','Anil' UNION
SELECT 'Murthy','Murthy'

INSERT INTO USER_DETAILS(USERID, FNAME, LNAME, EMAIL)
SELECT 1,'Navneeth','Naik','navneeth@kdmnn.com' UNION
SELECT 2,'Amol','Kulkarni','amol@kdmnn.com' UNION
SELECT 3,'Anil','Bahirat','anil@kdmnn.com' UNION
SELECT 4,'Murthy','Belluri','murthy@kdmnn.com'

AFTER Triggers
The type of trigger that gets executed automatically after the statement that triggered it completes is called an AFTER trigger. An AFTER trigger is a trigger that gets executed automatically before the transaction is committed or rolled back.
Using the below script, first we shall create a trigger on the table USER_MASTER for the INSERT event of the table.

USE KDMNN
Go

CREATE TRIGGER trgInsert
ON User_Master
FOR INSERT
AS
Print ('AFTER Trigger [trgInsert] – Trigger executed !!')
GO

BEGIN TRANSACTION
DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)
VALUES('Damerla','Damerla')

SET @ERR = @@Error
IF @ERR = 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END

Output
AFTER Trigger [trgInsert] – Trigger executed !!
(1 row(s) affected)
ROLLBACK TRANSACTION

By looking at the output, we can conclude that before the transaction is rolled back or committed, the AFTER trigger gets executed automatically. A table can have several AFTER triggers for each of the three triggering actions i.e., INSERT, DELETE and UPDATE. Using the below script, we shall create two triggers on the table User_Master for the INSERT triggering action.

CREATE TRIGGER trgInsert2
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert2] – Trigger executed !!')
END
GO

CREATE TRIGGER trgInsert3
ON User_Master
FOR INSERT
AS
BEGIN
Print ('AFTER Trigger [trgInsert3] – Trigger executed !!')
END
GO

BEGIN TRANSACTION
DECLARE @ERR INT

INSERT INTO USER_MASTER(USERNAME, PASSWORD)
VALUES('Damerla','Damerla')

SET @ERR = @@Error
IF @ERR = 0
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK TRANSACTION'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMIT TRANSACTION'
END

Output
AFTER Trigger [trgInsert] – Trigger executed !!
AFTER Trigger [trgInsert2] – Trigger executed !!
AFTER Trigger [trgInsert3] – Trigger executed !!
(1 row(s) affected)
ROLLBACK TRANSACTION

Triggers can be used in the following scenarios, such as if the database is de-normalized and requires an automated way to update redundant data contained in multiple tables, or if customized messages and complex error handling are required, or if a value in one table must be validated against a non-identical value in another table.
Triggers are a powerful tool that can be used to enforce the business rules automatically when the data is modified. Triggers can also be used to maintain the data integrity. But they are not to maintain data integrity. Triggers should be used to maintain the data integrity only if you are unable to enforce the data integrity using CONSTRAINTS, RULES and DEFAULTS. Triggers cannot be created on the temporary tables.