Trigger Management

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.

Scope and Events of DDL Triggers

DDL triggers can be set to react on database or server objects. In the following example, the trigger will prevent the creation of new logins (server objects stored in the database):

CREATE TRIGGER trdPreventLoginCreation
ON ALL SERVER FOR CREATE_LOGIN
AS

RAISERROR('It is not allowed to add logins to this server
without permission. Call DBA Security Group.', 16, 1)

INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate],[UserName])
VALUES ('LOGIN CHANGE', GetDate(),SYSTEM_USER)

ROLLBACK
GO

If you want to prevent any change on any login, instead of referencing all login events (CREATE_LOGIN, DROP_LOGIN, ALTER_LOGIN), you can reference the event group—DDL_LOGIN_EVENTS:

CREATE TRIGGER trdPreventLoginChanges
ON SERVER FOR DDL_LOGIN_EVENTS
AS
RAISERROR('It is not allowed to add logins to this server
without permission. Call DBA Security Group.', 16, 1)
ROLLBACK
GO

Similarly, to reference all table modification statements you can use the DDL_TABLE_EVENTS group, and if you decide to fire a trigger after all table, view, index, and statistics modification events, you can use the DDL_TABLE_VIEW_EVENTS group. To see a complete list of events and their groups, search “Event Groups for Use with DDL Triggers” in Books OnLine.

TIP

---

To audit all DDL changes at the database level, use the DDL_DATABASE_LEVEL_EVENTS group. To track all security changes on the server, use the DDL_SERVER_SECURITY_EVENTS group. With these groups, you will find the answers to questions such as, “Who keeps changing a stored procedure after you deployed it?” and “Who is allowing developer access to the production database?”

EventData()

Since DDL triggers are not tied to table modifications, as in the case of DML triggers, they do not have Inserted and Deleted virtual tables. Instead, you can get information about the DDL modification statement that has fired the trigger using a special EventData() function. In the following example, changes to the table are allowed, but they are recorded for audit purposes in the special table:

CREATE TRIGGER trdAuditTableChanges
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
   declare @event varchar(max)
   set @event = Convert(nvarchar(max), EVENTDATA())

   INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate],[UserName],[Note])
   VALUES ('TABLE CHANGE', GetDate(),SYSTEM_USER, @event)

GO

When the statement that modifies a table is issued, EventData() will return data in the form of an XML document:

<EVENT_INSTANCE> 
  <EventType>CREATE_TABLE</EventType> 
  <PostTime>2004-09-08T21:56:37.727</PostTime>
  <SPID>52</SPID>
  <ServerName>V505\C4</ServerName> 
  <LoginName>V505\dsunderic</LoginName>
  <UserName>V505\dsunderic</UserName>
  <DatabaseName>Asset5</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>test</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
      <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
      ANSI_PADDING="ON" QUOTED_IDENTIFIER= "ON" ENCRYPTED="FALSE" /> 
      <CommandText>create table test(id int)</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

You can use XQuery methods to parse the content of an event document. In the following example, the Activity, UserName, and Note columns of the ActivityLog table were extracted from the EventType, LoginName, and TSQLCommand nodes of the event document:

CREATE TRIGGER trdAuditTableChanges
ON DATABASE
AFTER DDL_TABLE_EVENTS
AS

declare @event xml
set @event = EVENTDATA()

INSERT INTO [dbo].[ActivityLog]([Activity],[LogDate],[UserName],[Note])
VALUES
(@event.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
 GETDATE(),
 @event.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
 @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') );
GO

The preceding XQuery value() methods simply access the values of the specified nodes. The value() method is better suited for use in Transact-SQL than the alternative query() method, since it returns data with carriage return and linefeed (CR/LF) characters invisible. The query() method returns CR and LF in an ampersand-escaped sequence of characters. For more information on how to use XQuery, search “XQuery Against the XML Data Type” in Books OnLine.

NOTE

---

EventData() can be used only inside the DDL trigger. It is not guaranteed to return a meaningful result after a change is rolled back or committed.

The function uses different schemas to return data when it is invoked by database and server modification statements. The schema also depends on the actual modification statement, since not all nodes are applicable for all events.

Syntax of DDL Triggers

DDL triggers are designed using the following syntax:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [ ENCRYPTION ] | [ EXECUTE AS CALLER | SELF | 'user_name' ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ]}

The On clause controls the scope of the trigger—it is tied to database or server events. Like stored procedure triggers, DDL triggers can be encrypted and executed alternatively by caller, schema owner, or specific database user.

Managing Triggers

You can manage triggers using GUI tools such as SQL Server Management Studio or Visual Studio, or by using Transact-SQL statements.

Managing DML Triggers in Management Studio

You can access triggers from Management Studio:

1. Expand the node of the table with which the trigger is associated. 
   
2. Expand the trigger’s node.
   
3. Right-click the trigger and choose Modify from the pop-up menu.

SQL Server displays a form for editing trigger properties (see Figure 9-1). This editor is very similar to the editor you use to edit stored procedures.

To create a new trigger on the table, right-click the trigger’s subnode of the table and choose New Trigger from the pop-up menu. SQL Server initially fills the form with a template for creating a new trigger.

Once you have created or modified the trigger, you can choose Assisted Editor | Check Syntax to verify it, and then File | Save to attach it to the table.

Figure 9-1.  Managing triggers in Management Studio

You can delete a trigger by right-clicking the trigger and choosing Delete from the pop-up menu.

Managing DDL Triggers from Management Studio

Database-level DDL triggers are managed from server | Databases | database | Programmability | Database Triggers (see Figure 9-2). Server-level DDL triggers are managed from server | Programmability | Server Triggers.

Managing Triggers Using Transact-SQL Statements

SQL Server has a rich palette of system stored procedures, functions, and views for managing triggers from Transact-SQL.

Figure 9-2.  Managing database-level DDL triggers

Listing Triggers

To list triggers associated with a table, use the system stored procedure sp_helptrigger:

Exec sp_helptrigger 'dbo.OrderHeader'

The server returns the list of triggers associated with the specified table and displays the type of trigger found in the isupdate, isdelete, isinsert, isafter, and isinteadof columns (see Figure 9-3).

Viewing Triggers

You can obtain the code for a trigger using the system stored procedure sp_helptext:

Exec sp_helptext 'dbo.trOrderStatus_U'

Figure 9-3.  Trigger information from sp_helptrigger

The server returns the code for the specified trigger:

Text
------------------------------------------- CREATE Trigger trOrderStatus_U
On dbo.[OrderHeader]
After Update    -- For Update
As
     If Update (OrderStatusId)
     Begin

          Insert into ActivityLog
                    ( Activity,
                    LogDate,
                    UserName,
                    Note)

          Select  
'OrderHeader.OrderStatusId',
      GetDate(),
      USER_NAME(),
      'Value changed from '
      + Cast( d.OrderStatusId as varchar)
      + ' to '
      + Cast( i.OrderStatusId as varchar)
    From deleted d inner join inserted i
    On d.OrderId = i.OrderId
End

Deleting DML Triggers

A trigger can be deleted, as can all other database objects, using the appropriate Drop statement:

if exists (select * from sys.objects
           where object_id = object_id(N'[dbo].[trEquipment_IU]')
           and type in (N'TA', N’TR'))
DROP TRIGGER [dbo].[trEquipment_IU]

Deleting DDL Triggers

The statement for dropping DDL triggers contains a reference to the scope (level) of the trigger:

if  exists (select * from master.sys.server_triggers
            where name = N'trdTestCreateLogin2' and parent_class=100)
DROP TRIGGER [trdTestCreateLogin2] ON ALL SERVER
GO

if  exists (select * from sys.triggers
           where name = N'trdAuditTableChanges' and parent_class=0) DROP TRIGGER [trdAuditTableChanges] ON DATABASE 

NOTE

---

You should check the presence of server-level triggers in the master.sys.server_triggers view and the presence of database-level triggers in the sys.triggers view.

Please check back next week for the continuation of this article.