Implementation of DDL triggers in SQL Server 2005

server| triggers SQL SERVER 2005, a new addition to a number of new features, in which DDL triggers are a good choice, based on the information of the preliminary study as follows, now sorted:
In SQL Server 2000, after triggers can only be defined for DML statements (INSERT, UPDATE, and DELETE) that are issued against a table. SQL Server 2005 can define triggers for DDL events for a range of the entire server or database. DDL triggers can be defined for a single DDL statement (for example, create_table) or for a set of statements (for example, ddl_database_level_events). Inside the trigger, you can obtain data about the event that fired the trigger by accessing the EVENTDATA () function. This function returns the XML data about the event. The schema for each event inherits the Server events infrastructure.
For example, in SQL SERVER 2005, create a database called Ddltrtest, and create a table called MyTable
and usp_querymytable stored procedures, as shown below
DROP DATABASE [Ddltrtest]
Go
CREATE DATABASE Ddltrtest
Go
Use [Ddltrtest]
Go
IF EXISTS (SELECT * from SYS. OBJECTS
WHERE object_id = object_id (N ' [dbo].[ MYTABLE] ')
and TYPE in (N ' U '))
DROP TABLE [DBO]. [MYTABLE]
Go
CREATE TABLE MYTABLE (ID INT, NAME VARCHAR (100))
Go
INSERT into MYTABLE SELECT 1, ' A '
INSERT into MYTABLE SELECT 2, ' B '
INSERT into MYTABLE SELECT 3, ' C '
INSERT into MYTABLE SELECT 4, ' D '
INSERT into MYTABLE SELECT 5, ' E '
INSERT into MYTABLE SELECT 6, ' F '
Go
Use [Ddltrtest]
Go
IF EXISTS (SELECT * from sys.objects WHERE object_id =
OBJECT_ID (N ' [dbo].[ Usp_querymytable] ')
and type in (n ' P ', n ' PC '))
DROP PROCEDURE [dbo]. [Usp_querymytable]
Go
CREATE PROC usp_querymytable
As
SELECT * from MYTABLE
Go

Next, define a DDL trigger as follows

CREATE TRIGGER Stop_ddl_on_table_and_proc
On DATABASE
For Create_table,drop_table,
Alter_table,create_procedure,
Alter_procedure,drop_procedure
As
SELECT eventdata (). Value
(' (/event_instance/tsqlcommand/commandtext) [1] ',
' nvarchar (max) ')
PRINT ' are not allowed to Create,alter and DROP
Any Tables and procedures '
ROLLBACK;

Next, we try the following actions:
ALTER TABLE MYTABLE ADD X INT
The results are as follows, error prompts
ALTER TABLE MYTABLE ADD X INT
(1 row (s) affected)
are not allowed to Create,alter and DROP any Tables and procedures
MSG 3609, level, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.

And then the drop operation, which also triggers a warning

DROP TABLE MYTABLE
(1 row (s) affected)
are not allowed to Create,alter and DROP any Tables and procedures
MSG 3609, level, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.
Because our trigger rules out the inability to use create_table,drop_table,
Alter_table,create_procedure,
Alter_procedure,drop_procedure and other operations. If we want to turn off this trigger, we can do this: DISABLE TRIGGER stop_ddl_on_table_and_proc
On DATABASE of course, we have to take a policy on the entire server, it is also very simple, and the method is similar to the above only the parameters on the database to on server, such as the CREATE TRIGGER Stop_ddl_on_table_and _proc
On the All SERVER
For Create_database,alter_database,drop_database
As
PRINT ' are not allowed to Create,alter and DROP any Databases '
ROLLBACK;