Wednesday, 28 December 2011

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

Posted by: MR. JOYDEEP DAS

2 comments: