SQL Server 2005 – Data Definition Language Triggers

One of the many new features that have been added to SQL Server 2005 is DDL triggers. We use DML triggers in SQL Server 7.0 and 2000, which executes a bunch of SQL statements or procedures whenever an INSERT, UPDATE or DELETE statement is executed and limited to a table or view object.


In SQL 2005, a trigger can be created in order to perform any action upon a DDL statement. The scope can be database level or Server level. DDL triggers are used when you want a certain action to be performed when a schema change occurs.


Let us create a trigger that stops any user from executing Create, Alter and Drop table or procedure statement on a database. Let us assume that we have the database DDLTrTest and let us also assume that we have MyTable and Usp_Querymytable proc existing on the database DDLTrTest.


Create the database and tables by using the following SQL statements.

USE [MASTER]
GO
IF EXISTS (SELECT NAME FROM SYS.DATABASES
WHERE NAME = N’DDLTRTEST’)
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

Let us create our DDL Trigger STOP_DDL_on_Table_and_PROC as shown below

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 ‘You are not allowed to CREATE,ALTER and DROP
any Tables and Procedures’
ROLLBACK;

Here we use the new function EVENTDATA() provided in SQL Server 2005 to capture the SQL Statement.


Now let us try to add a column to the table Mytable by executing the following command.

ALTER TABLE MYTABLE ADD X INT

The result would be:

ALTER TABLE MYTABLE ADD X INT
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Let us try to drop the table Mytable.

DROP TABLE MYTABLE

The result would be:

DROP TABLE MYTABLE
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Let us try to create a new procedure.

CREATE PROCEDURE USP_QUERYMYTABLE2 AS
SELECT TOP 100 * FROM MYTABLE

The result would be:

CREATE PROCEDURE USP_QUERYMYTABLE2 AS
SELECT TOP 100 * FROM MYTABLE
(1 row(s) affected)
You are not allowed to CREATE,ALTER and DROP any Tables and Procedures
Msg 3609, Level 16, State 2, Procedure USP_QUERYMYTABLE2, Line 3
The transaction ended in the trigger. The batch has been aborted.

The DDL trigger STOP_DDL_on_Table_and_PROC that we created applies only to the database DDLTrTEST. Create, Alter and Drop statements can be executed on all other databases.


These triggers can be disabled or enabled or dropped by the following commands respectively.

DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC
ON DATABASE
GO
ENABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC
ON DATABASE
GO
DROP TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON DATABASE
GO

If you want to extend the scope of this trigger from database level to server level, then change the ON DATABASE to ON ALL SERVER as shown below. This DDL trigger stops the user from Creating, Altering and dropping any databases.

CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS
PRINT ‘You are not allowed to CREATE,ALTER and DROP any Databases’
ROLLBACK;

Now, let us try to create, drop and alter a database as shown below.

USE MASTER
GO
CREATE DATABASE TEST
GO
ALTER DATABASE DDLTRTEST MODIFY FILE
(
NAME = DDLTRTEST,
FILENAME=’D:\DDLTRTEST.MDF’)
GO
USE MASTER
GO
DROP DATABASE DDLTRTEST
GO

The result would be

You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
The file “DDLTRTEST” has been modified in the system catalog.
The new path will be used the next time the database is started.
You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
You are not allowed to CREATE,ALTER and DROP any Databases
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

This Server level trigger can be disabled or enabled or dropped by executing the following SQL Command.

DISABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC
ON ALL SERVER
GO
ENABLE TRIGGER STOP_DDL_ON_TABLE_AND_PROC
ON ALL SERVER
GO
DROP TRIGGER STOP_DDL_ON_TABLE_AND_PROC ON ALL SERVER
GO

Conclusion


This article has explored the new SQL Server 2005 feature “DDL trigger” and EVENTDATA() function.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles