This chapter is from the book
This chapter is from the book
This chapter is from the book
DDL Triggers
A trigger is a block of SQL statements that are executed based on the fact that there has been an alteration (INSERT, UPDATE, or DELETE) to a table or on a view. In previous versions of SQL Server, the statements had to be written in T-SQL, but in version 2005, as we saw in Chapter 3, they can also be written using .NET languages. As we mentioned, the triggers are fired based on action statements (DML) in the database.
What about changes based on Data Definition Language statements, changes to the schema of a database or database server? It has not been possible to use triggers for that purpose—that is, until SQL Server 2005. In SQL Server 2005 you can create triggers for DDL statements as well as DML.
The syntax for creating a trigger for a DDL statement is shown in Listing 7-2, and as with a DML trigger, DDL triggers can be written using .NET languages as well.
Listing 7-2: Syntax for a DDL Trigger
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
{ AS
{ sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }
}
< method_specifier > ::=
assembly_name:class_name[::method_name]
The syntax for a DML trigger is almost identical to that for a DDL trigger. There are, however, some differences.
The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE).
A DDL trigger cannot be an INSTEAD OF trigger.
The event for which the trigger fires is defined in the event_type argument, which for several events is a comma-delimited list. Alternatively, you can use the blanket argument DDL_DATABASE_LEVEL_EVENTS.
The SQL Server Books Online has the full list of DDL statements, which can be used in the event_type argument and also by default are included in the DDL_DATABASE_LEVEL_EVENTS. A typical use of DDL triggers is for auditing and logging. The following code shows a simple example where we create a trigger that writes to a log table.
—first create a table to log to
CREATE TABLE ddlLog (id INT PRIMARY KEY IDENTITY,
logTxt VARCHAR(MAX))
GO
—create our test table
CREATE TABLE triTest (id INT PRIMARY KEY)
GO
— create the trigger
CREATE TRIGGER ddlTri
ON DATABASE
AFTER DROP_TABLE
AS
INSERT INTO ddlLog VALUES('table dropped')
You may wonder what the VARCHAR(MAX) is all about in creating the first table—we'll cover that later in this chapter. The trigger is created with a scope of the local database (ON DATABASE), and it fires as soon as a table is dropped in that database (ON DROP_TABLE). Run following code to see the trigger in action.
DROP TABLE triTest SELECT * FROM ddlLog
The DROP TABLE command fires the trigger and inserts one record in the ddlLog table, which is retrieved by the SELECT command.
As mentioned previously, DDL triggers can be very useful for logging and auditing. However, we do not get very much information from the trigger we just created. In DML triggers, we have the inserted and deleted tables, which allow us to get information about the data affected by the trigger. So, clearly, we need a way to get more information about events when a DDL trigger fires. The way to do that is through the eventdata function.
Eventdata
The eventdata() function returns information about what event fired a specific DDL trigger. The return value of the function is XML, and the XML is typed to a particular schema (XSD). Depending on the event type, the XSD includes different information. The following four items, however, are included for any event type:
The time of the event
The SPID of the connection that caused the trigger to fire
The login name and user name of the user who executed the statement
The type of the event
The additional information included in the result from eventdata is covered in SQL Server Books Online, so we will not go through each item here. However, for our trigger, which fires on the DROP TABLE command, the additional information items are as follows:
Database
Schema
Object
ObjectType
TSQLCommand
In Listing 7-3 we change the trigger to insert the information from the eventdata function into the ddlLog table. Additionally, we change the trigger to fire on all DDL events.
Listing 7-3: Alter Trigger to Use eventdata
— alter the trigger ALTER TRIGGER ddlTri ON DATABASE AFTER DDL_DATABASE_LEVEL_EVENTS AS INSERT INTO ddlLog VALUES CONVERT(VARCHAR(max)eventdata()
From the following code, we get the output in Listing 7-4.
—delete all entries in ddlLog DELETE ddlLog —create a new table CREATE TABLE evtTest (id INT PRIMARY KEY) —select the logTxt column with the XML SELECT logTxt FROM ddlLog
Listing 7-4: Output from eventdata
<EVENT_INSTANCE> <PostTime>2004-01-30T11:58:47.217</PostTime> <SPID>57</SPID> <EventType>CREATE_TABLE</EventType> <ServerName>ZMV44</ServerName> <LoginName>ZMV44\Administrator</LoginName> <UserName>ZMV44\Administrator</UserName> <DatabaseName>pubs</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>foo</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText> CREATE TABLE evtTest (id int primary key) </CommandText> </TSQLCommand> </EVENT_INSTANCE>
Because the data returned from the function is XML, we can use XQuery queries to retrieve specific item information. This can be done both in the trigger and from the table where we store the data. The following code illustrates how to retrieve information about the EventType, Object, and CommandText items in the eventdata information stored in the table ddlLog. Notice that we first store it into an XML data type variable, before we execute the XQuery statement against it.
DECLARE @data XML
SELECT @data = logTxt FROM ddlLog
WHERE id = 11
SELECT
CONVERT(NVARCHAR(100),
@data.query('data(//EventType)')) EventType,
CONVERT(NVARCHAR(100),
@data.query('data(//Object)')) Object,
CONVERT(NVARCHAR(100),
@data.query('data(//TSQLCommand/CommandText)')) Command
If the syntax in the previous code snippet seems strange, that's because it is XML and XQuery; read Chapters 8 and 9, where the XML data type and XQuery are covered in detail.
The programming model for both DML and DDL triggers is a synchronous model, which serves well when the processing that the trigger does is relatively short-running. This is necessary because DDL and DML triggers can be used to enforce rules and can roll back transactions if these rules are violated. If the trigger needs to do longer-running processing tasks, the scalability inevitably suffers. Bearing this in mind, we can see that for certain tasks, it would be beneficial to have an asynchronous event model. Therefore, in SQL Server 2005 Microsoft has included a new event notification model that works asynchronously: event notifications.