SQL server 2005: DDL triggers

DDL trigger is a new feature in SQL 2005. It’s fired when variaty of DDL statements, e.g. DROP TABLE, ALTER PROCEDURFE etc, are executed.

DDL trigger can be used mainly for two purpose:
1) Prevent the change to database schema (e.g. tables) or server wide objects (e.g. Logins)
2) To log any changes to the database schema or server wide objects.

E.g. the following trigger prevents user accidentally creat, drop, or change a table in master database.

USE MASTER
go
CREATE TRIGGER DropTable
ON DATABASE
FOR DROP_TABLE, CREATE_TABLE, ALTER_TABLE
AS
DECLARE @Cmd nvarchar(max)
DECLARE @DBName nvarchar(255)
SET @DBName=DB_NAME()
SELECT @Cmd=EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’,’nvarchar(max)’)
RAISERROR (‘The command %s cannot be executed. It”s not allowed in the database %s.’, 16, 1,@Cmd,@DBName)
ROLLBACK
;

Try to create a table in master databse:
CREATE TABLE test (c1 int not null)
GO

We will get an error:
Msg 50000, Level 16, State 1, Procedure DropTable, Line 9
The command CREATE TABLE test (c1 int not null)
cannot be executed. It’s not allowed in the database master.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

The following example prevents user create, change, and delete logins in the current SQL server instance:

ALTER TRIGGER ChangeLogin
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
DECLARE @LoginName nvarchar(255)
SELECT @LoginName=EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(255)’)
RAISERROR (‘The login %s cannot be created, changed, or dropped. Users are not allowed to create, change, and delete logins’, 16, 1,@LoginName)
ROLLBACK
;
GO

Try to create a login:
CREATE LOGIN Test WITH PASSWORD=’12WerLasd’
GO

We will get an error:
Msg 50000, Level 16, State 1, Procedure ChangeLogin, Line 7
The login Test cannot be created, changed, or dropped. Users are not allowed to create, change, and delete logins
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

To get ddl trigger informations:
database scoped triggers:
— get triggers
SELECT * FROM sys.triggers
— get events:
SELECT * FROM sys.trigger_events

Server scoped trighers:
SELECT * FROM sys.server_triggers
SELECT * FROM sys.server_trigger_events

DLL trigger can be enabled or disabled as normal trigger, e.g.:
DISABLE TRIGGER ChangeLogin ON ALL SERVER
GO
DISABLE TRIGGER DropTable ON DATABASE
GO

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s