Sql-server – Trigger actions on database creation or attach

ddlsql serversql server 2014trigger

On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to use DDL trigers and actually found here a question similar to mine. Starting from that code I'm trying a slightly different action but can't manage to even get started.

The following code, taken from the question I mentioned, I just added a condition to check db name and if corresponds to the one I need, then do some action. Problem is that is no running the SET statement inside the IF BEGIN..END. The last SELECT using fn_listextendedproperty doesn't return any data.

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

    IF @DatabaseName = N'asd'
    BEGIN
        SET @SQL = '
            USE ' + @DatabaseName + ';
            EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
            EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';
        EXEC (@SQL);
    END;
GO

CREATE DATABASE asd;
GO

SELECT name, value 
FROM asd.sys.fn_listextendedproperty(default, default, default, default, default, default, default)

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE asd;
GO

Best Answer

You didn't copy the full example and are concatenating NULL into the SQL command string which causes it to be NULL. If you do 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)');

    IF @DatabaseName = 'MyTestDatabase'
    BEGIN

    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);
    END

GO

CREATE DATABASE MyTestDatabase;
GO

SELECT name, value 
FROM MyTestDatabase.sys.fn_listextendedproperty(default, default, default, default, default, default, default)


DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE MyTestDatabase;
GO

It works correctly on my system.

Related Question