Is it possible to write a trigger when someone makes a change at the server level such as attaching or creating a database? I have a dev server where anyone can add a copy of the product database for testing, but there is no cleanup procedure. I want to add the following extended properties when a database is added.
EXEC sys.sp_addextendedproperty
@name = N'Owner',
@value = N'Username';
EXEC sys.sp_addextendedproperty
@name = N'StartDate',
@value = N'9/1/2011';
You can use DDL triggers for this. There doesn't appear to be a DDL event raised for attach but there definitely is for CREATE DATABASE. You could work around this by denying rights to sp_attachdb and force everyone to use CREATE DATABASE FOR ATTACH instead.
The following example feels like a bit of a hack, so hopefully someone with more DDL trigger experience will propose a neater way of doing this.
IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
END
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE
@DatabaseName NVARCHAR(128)
, @CreatedBy NVARCHAR(128)
, @CreatedDate NVARCHAR(23)
, @SQL NVARCHAR(4000);
SELECT
@DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
, @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
, @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');
SET @SQL = '
USE ' + @DatabaseName + ';
EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';
-- PRINT @SQL;
EXEC (@SQL);
GO
CREATE DATABASE MyTestDatabase;
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
DROP DATABASE MyTestDatabase;
GO
