3.3. Data Definition Language (DDL) Triggers Data Manipulation Language (DML) triggers are fired on actions that cause a change to the data in a table or a viewINSERT, UPDATE, or DELETE. SQL Server 2005 introduces Data Definition Language (DDL) triggers that fire in response to DDL statements that change the database schema or database server. These statements include CREATE, ALTER, DROP, GRANT, DENY, and REVOKE. DDL triggers are typically used for auditing and logging. The syntax for a DDL trigger is: CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ...,n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > } [ ; ] <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name
where:
trigger_name The name of the trigger.
ON { ALL SERVER | DATABASE } Defines the scope of the DDL trigger. Triggers scoped as ALL SERVER fire whenever event_type or event_group happens anywhere in the current server. You must have at least CONTROL SERVER permission on the server to create a DDL trigger with server scope. Triggers scoped as DATABASE fire whenever event_type or event_group occurs in the current database.
<ddl_trigger_option> The WITH ENCRYPTION clause encrypts the CREATE TRIGGER statement body and prevents it from being accessed through catalog views or from being published as part of SQL Server replication. The EXECUTE AS clause specifies the security context under which the trigger is executed.
{ FOR | AFTER } FOR specifies that the trigger fires for each row affected by the triggering statement. AFTER specifies that the trigger fires only when all operations in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks must also succeed. AFTER is the default.
event_type A T-SQL event that causes the DDL trigger to fire once the event has finished executing. For a complete list of event types that are valid for use in DDL triggers, see Microsoft SQL Server 2005 Books Online.
event_group The name of a predefined grouping of T-SQL events. The DDL trigger fires after any one of the events in event_group finishes executing. For a complete list of event groups that are valid for use in DDL triggers, see Microsoft SQL Server 2005 Books Online.
sql_statement T-SQL that specifies both the conditions under which the DDL trigger is fired and the T-SQL that specifies the action or actions to be taken when the trigger fires.
<method_specifier> Specifies the method of a registered assembly to bind with the trigger for CLR triggers. Creating CLR triggers is discussed in detail in Chapter 5. As an example, look at the process for creating a DDL trigger to log CREATE TABLE and DROP TABLE operations to a log table: Execute the following query to create a table named DdlLog in the ProgrammingSqlServer2005 databasecreate the database if you haven't previously. The DdlLog table will store the CREATE_TABLE and DROP_TABLE event information. USE ProgrammingSqlServer2005 CREATE TABLE DdlLog ( LogID int IDENTITY(1,1) NOT NULL, LogEntry xml NOT NULL, CONSTRAINT PK_Log PRIMARY KEY CLUSTERED ( LogID ASC ) )
Create a DDL trigger that will log created and dropped tables: USE ProgrammingSqlServer2005 GO CREATE TRIGGER LogTableActivity ON DATABASE FOR CREATE_TABLE, DROP_TABLE AS INSERT INTO DdlLog (LogEntry) VALUES ( EVENTDATA( ) )
The EVENTDATA function returns information about database or server events. The function is called when the event notification fires. For events that fire a DDL trigger, the EVENTDATA function returns a value of xml type that contains: The time of the event. The System Process ID (SPID) of the connection during which the trigger executed. The type of event that fired the trigger. Additional information depending on the event type. For details about the schemas for specific event types, see the EVENtdATA (transact-SQL) entry in Microsoft SQL Server 2005 Books Online.
Execute the following T-SQL statement to create and then drop a table named TestTable: USE ProgrammingSqlServer2005 CREATE TABLE TestTable ( TestID int NOT NULL ) DROP TABLE TestTable
Examine the table DdlLog. It contains two rows with details about the DDL CREATE_TABLE and DROP_TABLE events: <EVENT_INSTANCE> <EventType>CREATE_TABLE</EventType> <PostTime>2005-09-15T22:23:06.030</PostTime> <SPID>51</SPID> <ServerName>WHAMILTONXP</ServerName> <LoginName>DANTECONSULTING\WHamilton</LoginName> <UserName>dbo</UserName> <DatabaseName>ProgrammingSqlServer2005</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>TestTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE TestTable ( TestID int NOT NULL ) </CommandText> </TSQLCommand> </EVENT_INSTANCE> <EVENT_INSTANCE> <EventType>DROP_TABLE</EventType> <PostTime>2005-09-15T22:23:06.063</PostTime> <SPID>51</SPID> <ServerName>WHAMILTONXP</ServerName> <LoginName>DANTECONSULTING\WHamilton</LoginName> <UserName>dbo</UserName> <DatabaseName>ProgrammingSqlServer2005</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>TestTable</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>DROP TABLE TestTable </CommandText> </TSQLCommand> </EVENT_INSTANCE>
|
