Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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';
share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
Awesome! This DDL trigger works like a charm. However, when I added the sys.sp_addextendedproperty, it ran in the context of the master database rather than the database that was added. Do you know if it's possible to run the code inside the trigger, but in the context of the new database? Thanks again! –  Delux Sep 16 '11 at 17:46
1  
I've added an example that does the trick. Ugly but it works. –  Mark Storey-Smith Sep 16 '11 at 18:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.