Implementing Triggers in SQL Server 2000

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 the trigger it is? Whether AFTER or INSTEAD OF?

The solution for this question is sp_helptrigger. This stored procedure gives all the information about the triggers such as Event 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 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.

Navneeth Diwaker Naik (navneeth_naik@satyam.com) is an employee of Satyam Computers Services Limited, Hyderabad, Andhra Pradesh. [INDIA].

Published with the explicit written permission of the author. Copyright 2004.

]]>

Leave a comment

Your email address will not be published.