Implementing Triggers in SQL Server 2000

smileyfrnd

3.50/5 (2 votes)

Mar 19, 2009

CPOL

6 min read

34134

Implementing Triggers in SQL Server 2000.

Introduction

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 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 script below, 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 script below, 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

From the output, we can conclude that when the user tries to insert data in the table USER_MASTER, three triggers are executed automatically. That is, you can write several AFTER triggers on one table for each of the three triggering actions.

Similarly, we can write several AFTER triggers on the DELETE and UPDATE triggering actions.

Note: If a table has multiple AFTER triggers, then you can specify which trigger should be executed first and which trigger should be executed last using the Stored Procedure sp_settriggerorder. All the other triggers are in an undefined order which you cannot control.

An AFTER trigger can be created only on tables, not on Views. Using the script below, first, we shall create a simple view [vwUserMaster] which will fetch the Username and Password from the table USER_MASTER.

Create View vwUserMaster
as
SELECT USERNAME, PASSWORD FROM USER_MASTER
GO
 
CREATE TRIGGER trgOnView
ON vwUserMaster
FOR INSERT
AS 
BEGIN
        Print ('AFTER Trigger [trgOnView]  – vwUserMaster !!')
END
GO

Output

Server: Msg 208, Level 16, State 4, Procedure trgOnView, 
Line 2 Invalid object name 'vwUserMaster'.

From the output, we can conclude that we cannot create an AFTER trigger on Views.

Like Stored Procedures and Views, triggers can also be encrypted. The trigger definition is then stored in an unreadable form. Once encrypted, the definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.

CREATE TRIGGER trgEncrypted
ON User_Master WITH ENCRYPTION
FOR INSERT  
AS 
BEGIN
      Print ('AFTER Trigger [trgEncrypted] Encrypted  – Trigger executed !!')
END
GO
 
SELECT     
             sysobjects.name AS [Trigger Name], 
             SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
             OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
             syscomments.encrypted AS [IsEncrpted]
 
FROM 
             sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE    
             (sysobjects.xtype = 'TR')

Output

Trigger Name	Trigger Definition	Table Name	IsEncrpted
trgInsert	CREATE TRIGGER trgInsert	User_Master	0
trgInsert1	CREATE TRIGGER trgInsert1	User_Master	0
trgInsert2	CREATE TRIGGER trgInsert2	User_Master	0
trgEncrypted	???????????????	User_Master	1

Since the trigger trgEncrypted is created with the option WITH ENCRYPTION, the trigger definition is hidden, and there is no way that we can easily decrypt the trigger code.

We all know that the DML statements change or modify data. Sometimes, it becomes necessary for the triggers to have access to the changes being caused by the DML statements. SQL Server 2000 provides four different ways to determine the effects of the DML statements. The INSERTED and DELETED tables, popularly known as MAGIC TABLES, and the update () and columns_updated() functions can be used to determine the changes being caused by the DML statements.

The table below depicts the contents of the INSERTED and DELETED tables for the three different table events:

EVENT	INSERTED	DELETED
`Insert`	Contains the inserted rows	Empty
`Delete`	Empty	Contains the rows to be deleted
`Update`	Contains the rows after update	Contains the rows before update

Note that the Magic Tables do not contain information about the columns of data-types text, ntext, or image. Attempting to access these columns will cause an error.

The update() function is used to find whether a particular column has been updated or not. This function is generally used for data checks.

CREATE TRIGGER trgUddate
ON User_Details
FOR UPDATE 
AS 
If UPDATE(FName)
BEGIN
             PRINT('AFTER Trigger [trgUddate] - Executed - First Name has been updated')
             ROLLBACK TRANSACTION
END
else If UPDATE(LName)
BEGIN
             PRINT('AFTER Trigger [trgUddate] - Executed - Last Name has been updated')
             ROLLBACK TRANSACTION
END
else If UPDATE(MName)
BEGIN
             PRINT('AFTER Trigger [trgUddate] - Executed - MName Name has been updated')
             ROLLBACK TRANSACTION
END
else If UPDATE(Email)
BEGIN
             PRINT('AFTER Trigger [trgUddate] - Executed - Email has been updated')
             ROLLBACK TRANSACTION
END
GO
 
UPDATE User_Details
SET MName = 'Diwaker'
WHERE UserID = 1

Output

AFTER Trigger [trgUddate] - Executed - MName Name has been updated

Depending upon the column updated, a message will be displayed. With this feature, we can determine which column in the table has been updated, and then proceed with the business rules to be implemented further.

The Columns_Update() function returns a varbinary data type representation of the columns updated. This function returns a hexadecimal value from which we can determine which columns in the table have been updated.

INSTEAD OF Triggers

A trigger which gets executed automatically in place of triggering actions, i.e., INSERT, DELETE, and UPDATE, is called an INSTEAD OF trigger.

INSTEAD OF triggers get executed automatically before the Primary Key and the Foreign Key constraints are checked, whereas the traditional AFTER triggers get executed automatically after these constraints are checked.

CREATE TRIGGER trgAfterInsert
On User_Details
FOR INSERT
AS
BEGIN
  Print ('AFTER Trigger [trgAfterInsert] – Trigger executed !!')
END
 
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')

Output

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_User_Details_User_Master'.
The conflict occurred in database 'KDMNN', table 'User_Master', column 'UserID'.
The statement has been terminated.

The UserID 100 does not exist in the User_Master table, so the Foreign Key constraint has been checked and an error message is displayed. What we can conclude is: AFTER triggers get executed automatically after the PK and FK constraints.

Create Trigger trgInsteadInsert
On User_Details
INSTEAD OF INSERT
AS
BEGIN
Print ('INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!')
END
 
INSERT INTO USER_DETAILS(USERID, FNAME,LNAME, MNAME, EMAIL)
VALUES(100, 'FName','LName','MName','test@test.com')

Output

INSTEAD OF Trigger [trgInsteadInsert] – Trigger executed !!
(1 row(s) affected)

Even if the UserID 100 does not exist in the User_Master table, the trigger gets executed automatically.

Unlike AFTER triggers, INSTEAD OF triggers can be created on Views.

Create trigger trgOnView
on vwUserMaster
INSTEAD OF INSERT
AS
begin
             Print ('INSTEAD OF Trigger [trgOnView]  – vwUserMaster !!!')
End
 
INSERT INTO VWUSERMASTER(USERNAME, PASSWORD)
VALUES('Damerla','Venkat')

Output

INSTEAD OF Trigger [trgOnView]  – vwUserMaster !!
(1 row(s) affected)

So, whenever a user tries to insert data into the View vwUserMaster, the INSTEAD OF trigger trgOnView will automatically be executed. In SQL Server 2000, Views can be used to INSERT/DELETE and UPDATE data in multiple tables, and this can be achieved using INSTEAD OF triggers.

CREATE VIEW vwUser
AS 
SELECT
             [User_Master].[Username],
             [User_Master].[Password],
             [User_Details].[FName],
             [User_Details].[MName],
             [User_Details].[LName],
             [User_Details].[Email]
FROM
             [User_Master], [User_Details]
WHERE 
             [User_Master].[UserID]=[User_Details].[UserID]
 
CREATE TRIGGER tgrInsertData
ON vwUser
INSTEAD OF INSERT
AS
BEGIN
             Declare @UserName varchar(50) 
             Declare @Password varchar(50)
             Declare @FName varchar(50)
             Declare @MName varchar(50)
             Declare @LName varchar(50)
             Declare @Email varchar(50)
 
             SELECT 
                           @UserName = UserName,
                           @Password = Password,
                           @FName = FName,
                           @MName = MName,
                           @LName = LName,
                           @Email = Email
             FROM INSERTED
             
             INSERT INTO User_Master(UserName, Password)
             VALUES(@UserName, @Password)
             
             INSERT INTO User_Details(UserID,FName,LName,MName,Email) 
               VALUES(@@Identity, @FName, @LName, @MName, @Email)
 
END
 
INSERT INTO vwUser(UserName,Password,FName,LName,MName,Email)
VALUES ('Dhananjay','Dhananjay','Dhananjay','Nagesh',NULL,
'Dhananjay@kdmnn.com'

Output

AFTER Trigger [trgInsert]  – Trigger executed !!
AFTER Trigger [trgInsert2]  – Trigger executed !!
AFTER Trigger [trgInsert3]  – Trigger executed !!
AFTER Trigger [trgEncrypted] Encrypted  – Trigger executed !!
(1 row(s) affected)
AFTER Trigger [trgAfterInsert] – Trigger executed !!
(1 row(s) affected)

Check the data in the following tables: User_Master and User_Details. The new row gets inserted in both the tables. A View or table can have only one INSTEAD OF trigger for each INSERT, UPDATE, and DELETE event. We have seen that you can create any number of AFTER triggers on the table for the same event, whereas you cannot do that with INSTEAD OF triggers.

CREATE TRIGGER trgInsteadOfTrigger1
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
      Print ('INSTEAD OF Trigger [trgInsteadOfTrigger1] – Trigger executed !!')           
END
 
CREATE TRIGGER trgInsteadOfTrigger2
ON vwUserMaster
INSTEAD OF UPDATE
AS
BEGIN
   Print ('INSTEAD OF Trigger [trgInsteadOfTrigger2] – Trigger executed !!')           
END

Output

Server: Msg 2111, Level 16, State 1, Procedure trgInsteadOfTrigger2, Line 6
Cannot CREATE trigger 'trgInsteadOfTrigger2' for view 'vwUserMaster' 
  because an INSTEAD OF UPDATE trigger already exists.

From the output, it is clear that you cannot create two INSTEAD OF triggers on the View/ table for the same event. Note: An important point to be noted is that INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.

At last, how would you know what are the triggers associated with the table and what type of triggers they are? Whether AFTER or INSTEAD OF? The solution for this question is sp_helptrigger. This Stored Procedure gives all the information about triggers such as Events on which the trigger gets executed, the type of the trigger, etc.

Sp_helptrigger User_Master

Output

trigger_name	trigger_owner	isupdate	isdelete	isinsert	isafter	isinsteadof
trgInsert	dbo	0	0	1	1	0
trgInsert2	dbo	0	0	1	1	0
trgInsert3	dbo	0	0	1	1	0
trgEncrypted	dbo	0	0	1	1	0

Triggers can be used in 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 business rules automatically when data is modified. Triggers can also be used to maintain data integrity. Triggers should be used to maintain data integrity only if you are unable to enforce data integrity using Constraints, Rules, and Defaults. Triggers cannot be created on temporary tables.