Trigger Management

(Page 1 of 4 )

Last week, we covered a number of specific triggers. This week, we will continue our discussion of specifc triggers, and then begin looking at trigger management. The third of several parts, it is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).

DML Trigger Order of Execution

SQL Server 7.0 introduced the idea that more than one trigger could be created per modification statement. However, the execution order of such triggers could not be controlled. In SQL Server 2000 and SQL Server 2005, it is possible to define which DML After trigger to execute first and which to execute last against a table. For example, the following statement will set trInventory_I to be the first trigger to be executed in the case of an Insert modification statement:

Exec sp_settriggerorder @triggername = 'trInventory_I',
                        @order = 'first',
                        @stmttype = 'INSERT'

The @order parameter must have one of these values: ‘first’,  ‘last’, or ‘none’. The value ‘none’ is used to reset the order of the execution of the trigger after it has been specified. The @stmttype parameter must have one of these values: ‘INSERT’, ‘UPDATE’, or ‘DELETE’.

Since only one Instead-of trigger can be associated with a table, and since it is executed before any other trigger (or constraint), it is not possible to set its order.

NOTE

---

Alter trigger statements reset the order of the trigger. After altering the trigger, you must execute the sp_SetTriggerOrder statement to set it again.

DDL Triggers

DDL triggers are new in SQL Server 2005. They can be fired after the engine invokes Data Definition Language (DDL) statements such as:

• Create Table
  
• Drop Table
  
• Alter Procedure
  
• Drop Schema
  
• Create Login

DDL triggers are typically used to audit or control changes in database and server objects. In the following example, the trigger will prevent changes to any table in the current database:

CREATE TRIGGER trdPreventTableChanges
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS

RAISERROR('This database contains sensitive data. Changes to the
tables are typically not allowed and are rolled back by a trigger.
If you do have a permission to change tables,
temporarily disable the trigger by using:

DISABLE TRIGGER trdPreventTableChanges ON DATABASE
<your batch with table changes>
ENABLE TRIGGER trdPreventTableChanges ON DATABASE;

', 16, 1)
ROLLBACK;
GO

DDL triggers are After triggers, which means that:

• You can replace the For keyword with After (as in the third line of the preceding example). 
  
• You have to explicitly invoke Rollback statements to cancel the changes made by DDL statements.

Since it is not an Instead-of trigger, you do not have to actually execute changes in the code yourself. A DDL trigger will not fire if there is another reason to prevent completion of a DDL modification statement. For example, if a table that you are trying to drop does not exist, SQL Server will not invoke a DDL trigger to log the identity of a user who issues a Drop Table statement.

Next: Scope and Events of DDL Triggers >>
 

More MS SQL Server Articles
More By McGraw-Hill/Osborne

Comments On: Trigger Management

· This article is an excerpt from the book "Microsoft SQL Server 2005 Stored Procedure...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at you favorite bookstore. Buy this book now.