DDL triggers are one of the best features of SQL Server 2005. It provides you great power and flexibility in implementing different scenarios which were either impossible or very difficult before. DDL Triggers are of two different types:
- DDL Triggers for Database
- DDL Triggers for Server
For example, you want to make sure that no one can create database on your server except the DBA. You can now easily achieve this by implementing the following trigger:
Use master
Go
Create Trigger trStopDatabaseCreation
ON ALL Server
FOR CREATE_DATABASE
As
Begin
Print ‘Only DBA can create new databases on this server.’
Rollback;
End
Go
Now, lets try the above trigger:
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N‘TestDB’, FILENAME = N‘C:\SQL_Data\TestDB.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N‘TestDB_log’, FILENAME = N‘C:\SQL_Log\TestDB_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO
You will get the following result.
Only DBA can create new databases on this server.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
The DBA can disable this trigger as follows and can create database as usual
DISABLE TRIGGER trStopDatabaseCreation ON ALL SERVER
For further information please refer to SQL Server Books Online
