Let us try it out.
ALTER TRIGGER DDLDemo ON DATABASE FOR CREATE_TABLE AS SELECT EVENTDATA() GO
DROP TABLE employee GO
CREATE TABLE employee ( EmployeeID int primary
key, EmployeeName nvarchar(30) )
GO
When you create the table now, the trigger executes the EVENTDATA() function and displays some details:
Click on the XML link in the result. SQL Server Management Studio will open a new window with the XML results.
CREATE_TABLE 2009-05-
14T07:23:08.340 53 AMARESH\SQL2008 AMARESH\Administrator dbo
Name>payroll dbo
Name>employee TABLE CREATE TABLE employee
( EmployeeID int primary key, EmployeeName nvarchar(30) )
You would see this has all the data you would need about the event that fired the trigger.
SQL Server 2005 and 2008 have a set of extensive functions that let you manipulate the results of EVENTDATA() and extract the info you are interested in. Refer to books online for details on the XML data type and the associated methods.
Applications So where do you use DDL triggers? You can use them whenever and wherever you want to take custom actions against execution of DDL events. The following two are classic examples.
1. You want to audit various DDL events and maintain a track of who is creating, altering and dropping various database objects and when. Create a table for maintaining your audit records and insert into this table from within DDL triggers.
2. You want to enforce certain rules on various database objects. Like, you want to enforce thatevery table being created has a primary key. In the CREATE TABLE trigger, you would then check if the table has a primary key. You will rollback the transaction if your rule is violated.
Amresh Patnaik
Get most out of your technology infrastructure investments with Dell
About CIOL | Media Kit | Site Map | Contact Us | Help | Write to us | Jobs@CyberMedia | Privacy Policy
Copyright © CyberMedia India Online Ltd. All rights reserved. Usage of content from web site is subject to Terms and Conditions.