|
SQL Server 2000 has Triggers. Triggers are a "special kind" of Stored Procedure which are automatically invoked when actions like INSERT, UPDATE or DELETE are performed on a database table. Therefore, triggers in SQL 2000 respond to DML (Data Manipulation Language) statements. SQL Server 2005 introduced the concept of DDL (Data Definition Language) Triggers.
DDL operations are basiclly all CREATE, ALTER and DROP statements. This means that a DDL trigger is invoked when, as an example, a user creates or deletes a database object, or modifies it. DDL triggers expose all the informations about he event that fired them by means of a function, which returns an XML fragment. This function is called eventdata(). The eventdata() function outputs something like this: <EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2007-07-25T21:44:27.267</PostTime>
<SPID>52</SPID>
<ServerName>MyServer</ServerName>
<LoginName>MyServer\User01</LoginName>
<UserName>dbo</UserName>
<DatabaseName>HotelList</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Hotels</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE"/>
<CommandText>create table Hotels(name varchar(50),
address varchar(30), stars int)</CommandText>
</TSQLCommad>
</EVENT_INSTANCE> |
...which basically contains all the info about the event that occurred. A DDL trigger could then parse this XML data to extract info as needed, using the value method defined in SQL 2005 for XML variables. This is an example of a DDL trigger (click to enlarge): 
After creating a trigger similar to this one, just create a simple table and observe the effects.
|
