Trigger Management - Scope and Events of DDL Triggers
(Page 2 of 4 )
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.
Next: Managing Triggers >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 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.
|
|
