Attach Database Trigger

Question

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';

Answer 1

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