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).
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:
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.
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:
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.
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:
Expand the node of the table with which the trigger is associated.
Expand the trigger’s node.
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.
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.