Trigger – DDL

When we think about trigger, we always think about DML trigger but DDL triggers are playing a very important role on database impact.

SQL Server 2005 introduced a new trigger called the DDL Trigger. DDL means "Data Definition Language" that is create table, alter table, create proc, etc. What's great about these new triggers is if they are setup on the database/server, you are able to capture the DDL statement and automatically log it to a change log. You have no idea how much easier that is than doing it manually. Also, the laziness in all of us gets a little boost.

The DDL trigger has 2 type of scope

1.    Server-scoped statements

2.    Database-scoped statements

In "server-scoped statement", the triggered worked on entire SQL server instance, no matter how many database exists within this instance.

But in the "database-scoped statements", the triggers work on a particular database.

Server-scoped statements

ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE	ALTER_DATABASE	DROP_DATABASE
CREATE_ENDPOINT	DROP_ENDPOINT
CREATE_LOGIN	ALTER_LOGIN	DROP_LOGIN
GRANT_SERVER	DENY_SERVER	REVOKE_SERVER

Database-scoped statements

CREATE_APPLICATION_ROLE	ALTER_APPLICATION_ROLE	DROP_APPLICATION_ROLE
CREATE_ASSEMBLY	ALTER_ASSEMBLY	DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE
CREATE_CERTIFICATE	ALTER_CERTIFICATE	DROP_CERTIFICATE
CREATE_CONTRACT	DROP_CONTRACT
GRANT_DATABASE	DENY_DATABASE	REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION	DROP_EVENT_NOTIFICATION
CREATE_FUNCTION	ALTER_FUNCTION	DROP_FUNCTION
CREATE_INDEX	ALTER_INDEX	DROP_INDEX
CREATE_MESSAGE_TYPE	ALTER_MESSAGE_TYPE	DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION	ALTER_PARTITION_FUNCTION	DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME	ALTER_PARTITION_SCHEME	DROP_PARTITION_SCHEME
CREATE_PROCEDURE	ALTER_PROCEDURE	DROP_PROCEDURE
CREATE_QUEUE	ALTER_QUEUE	DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING	ALTER_REMOTE_SERVICE_BINDING	DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE	ALTER_ROLE	DROP_ROLE
CREATE_ROUTE	ALTER_ROUTE	DROP_ROUTE
CREATE_SCHEMA	ALTER_SCHEMA	DROP_SCHEMA
CREATE_SERVICE	ALTER_SERVICE	DROP_SERVICE
CREATE_STATISTICS	DROP_STATISTICS	UPDATE_STATISTICS
CREATE_SYNONYM	DROP_SYNONYM
CREATE_TABLE	ALTER_TABLE	DROP_TABLE
CREATE_TRIGGER	ALTER_TRIGGER	DROP_TRIGGER
CREATE_TYPE	DROP_TYPE
CREATE_USER	ALTER_USER	DROP_USER
CREATE_VIEW	ALTER_VIEW	DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION	ALTER_XML_SCHEMA_COLLECTION	DROP_XML_SCHEMA_COLLECTION

Syntax to Create DDL triggers

CREATE TRIGGER [name of trigger]

ON [scope (database|server)]

FOR [event]

    As

    --Trigger definition here…

An example to DLL Triggers

CREATE TRIGGER backup_procs

ON DATABASE

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

AS

SET NOCOUNT ON

DECLARE @data XML

SET @data = EVENTDATA()

INSERT INTO dbo.eventslog

           (eventtype, objectname, objecttype, sqlcommand, username)

VALUES     (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),

            @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),

            @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),

            @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),

            @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

)

GO

The script above will create a DDL trigger called backup_procs. It will insert a row into the events log table when a proc is created, altered or dropped.

For better understanding of DDL trigger please refer to MSDN

http://msdn.microsoft.com/en-us/library/ms186406.aspx

Posted by: MR. JOYDEEP DAS