5

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
  • 2
    Well, the CreatedBy and CreatedDate variables aren't being assigned, so they are NULL and when you concatenate NULL into a string, you get NULL back out. – Jonathan Kehayias Nov 5 '15 at 20:12
10

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.

|improve this answer|||||
  • Next step, adding lot more actions inside the trigger, maybe new questions will arise – Yaroslav Nov 6 '15 at 14:10
  • 1
    Just make sure you understand the scope and ramifications of a rollback happening due to an error. Might be better to use an Event Notification and Service Broker activation to process the required actions asynchronous so you can ensure the original operation completes even in an error and use error handling/database mail to let you know of post operation changes failing. – Jonathan Kehayias Nov 13 '15 at 4:28
  • I'm looking for a version of this that will send an email to my team when a database gets created or attached. This for accountability and sometimes things slip through the cracks. – Sean Perkins Jan 21 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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