Search This Blog

Monday, November 1, 2010

DDL EVENTS List - SQL Server 2008/R2

Below are the all events with their hierarchy and scope that you can use to implement DDL triggers at both Server or Database Level.
You can create trigger to fire on all events defined under a group, then you can create trigger for that particular group like "DDL_TABLE_EVENTS", this trigger will fire on all three sub-events defined under this group; these are CREATE_TABLE, ALTER_TABLE and DROP_TABLE. Similarly if you want to have a trigger to fire only for a particular event in an event group, then specify only that particular event like "ALTER_TABLE".
See example below as well:




Example 01: Database Level Trigger for a particular event
-- DDL Trigger to prevent column changes on a Database
CREATE TRIGGER ColumnChanges
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
-- Detect whether a column was created/altered/dropped.
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);
ROLLBACK;
END
GO


Example 02: Server Level Trigger for a particular event
-- DDL Trigger to prevent column changes for all databases on an instance
CREATE TRIGGER ColumnChanges
ON ALL SERVER
FOR ALTER_TABLE
AS
BEGIN
-- Detect whether a column was created/altered/dropped.
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);
ROLLBACK;
END
GO


No comments: