posted on Tuesday, December 13, 2005 12:08 AM
by
bknight
Preventing Schema Changes with DDL Triggers
DDL triggers are a fantastic way to prvent DDL events in a production database. In the following database triggers, you prevent any type of DDL event, like altering a sproc in production. If a statement is issued, the statement is logged, rolled back and a user receives a message. Databae triggers can be raised at nearly any level. The below example captures any database-level event. There are also server DDL triggers that captures server events like creating logins or changing the configuration of a database.
SET
ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE
TRIGGER PreventDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
PRINT
'DDL events are prohibited, your statement was rolled back.'
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
SET @data = EVENTDATA()
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
INSERT [dbo].[DatabaseLog]
([PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent])
VALUES
(GETDATE(), CONVERT(sysname, CURRENT_USER), @eventType,
CONVERT(sysname, @schema), CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data)
ROLLBACK
-- Brian Knight