Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 1, 2006

SQL Server 2005 - Data Definition Language Triggers

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date