Trigger to change Database collation on creation

I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger?

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @databasename varchar(200)
set @databasename =db_name()
    ALTER DATABASE @databasename COLLATE xxxxxxxxxxxxxxxxxxx
GO

Obviously, this is not working.

You would need to use dynamic SQL, and the EVENTDATA() function.

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
SET NOCOUNT ON; 
DECLARE @databasename NVARCHAR(256) = N''
DECLARE @event_data XML; 
DECLARE @sql NVARCHAR(4000) = N''

SET @event_data = EVENTDATA()

SET @databasename = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') 

SET @sql += 'ALTER DATABASE ' + QUOTENAME(@databasename) + ' COLLATE al''z a-b-cee''z'

PRINT @sql

EXEC sys.sp_executesql @sql

GO

Just sub in your collation for my fake one.

Now when I create a database...

CREATE DATABASE DingDong

I get this message (from the print):


ALTER DATABASE [DingDong] COLLATE al'z a-b-cee'z

Just note that if other databases (including tempdb) use different collations, you can run into problems comparing string data. You'd have to add COLLATE clauses to string comparisons where casing or accents matter, and even when they don't you can hit errors. Related question where I ran into a similar code problem here.

StackExchange Database Administrators Q#174219, answer score: 11