Trigger actions on database creation or attach

Question

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

• Jonathan Kehayias: 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.

Answer 1

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.

• Yaroslav: Next step, adding lot more actions inside the trigger, maybe new questions will arise
• Jonathan Kehayias: 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.
• AKDiscer: 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.