Trigger to change Database collation on creation

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.

You cannot, generally speaking, issue ALTER DATABASE within a Trigger (or any Transaction that has other statements in it). If you attempt to, you will get the following error:

Msg 226, Level 16, State 6, Line xxxx
ALTER DATABASE statement not allowed within multi-statement transaction.

The reason that this error was not encountered in @sp_BlitzErik's answer is a result of the specific test case provided: the error shown above is a run-time error, while the error encountered in his answer is a compile-time error. That compile-time error prevents execution of the command and hence there is no "run-time". We can see the difference by running the following:

SET NOEXEC ON;

SELECT N'g' COLLATE Latin1;

SET NOEXEC OFF;

The above batch will error, while the following will not:

SET NOEXEC ON;

BEGIN TRAN
CREATE TABLE #t (Col1 INT);
ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2;
ROLLBACK TRAN;

SET NOEXEC OFF;

This leaves you with two options:

1. Commit the Transaction within the DDL Trigger such that there are no other statements in the Transaction. This is not a good idea if there are multiple DDL Triggers that can be fired by a CREATE DATABASE statement, and is possibly a bad idea in general, but it does work ;-). The trick is that you also need to begin a new Transaction in the Trigger else SQL Server will notice that the beginning and ending values for @@TRANCOUNT don't match and will throw an error related to that. The code below does just this, and also only issues the ALTER if the Collation is not the desired one, else it skips the ALTER command.

   USE [master];
   GO
   CREATE TRIGGER trg_DDL_ChangeDatabaseCollation
   ON ALL SERVER
   FOR CREATE_DATABASE
   AS
   SET NOCOUNT ON;
   
   DECLARE @CollationName [sysname] = N'Latin1_General_100_BIN2',
           @SQL NVARCHAR(4000);
   
   SELECT @SQL = N'ALTER DATABASE ' + QUOTENAME(sd.[name]) + N' COLLATE ' + @CollationName
   FROM   sys.databases sd
   WHERE  sd.[name] = EVENTDATA().value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname')
   AND    sd.[collation_name] <> @CollationName;
   
   IF (@SQL IS NOT NULL)
   BEGIN
     PRINT @SQL; -- DEBUG
     COMMIT TRAN; -- close existing Transaction, else will get error
     EXEC sys.sp_executesql @SQL;
     BEGIN TRAN; -- begin new Transaction, else will get different error
   END;
   ELSE
   BEGIN
     PRINT 'Collation already correct.';
   END;
   
   GO
   

   Test with:

   -- skip ALTER:
   CREATE DATABASE [tttt] COLLATE Latin1_General_100_BIN2;
   DROP DATABASE [tttt];
   
   -- perform ALTER:
   CREATE DATABASE [tttt] COLLATE SQL_Latin1_General_CP1_CI_AI;
   DROP DATABASE [tttt];
   

2. Use SQLCLR to establish a regular / external SqlConnection, with Enlist = false; in the Connection String, to issue the ALTER command as that will not be part of the Transaction.

   It appears that SQLCLR is not truly an option, though not due to any specific limitation of SQLCLR. Somehow, typing "as that will not be part of the Transaction" directly above did not sufficiently highlight the fact that there is, in fact, an active Transaction around the CREATE DATABASE operation. The problem here is that while SQLCLR can be used to step outside of the current Transaction, there is still no way for another Session to modify the Database currently being created until that initial Transaction commits.

   Meaning, Session A creates the Transaction for the creation of the Database and the firing of the Trigger. The Trigger, using SQLCLR, will create Session B to modify the Database that has been created, but the Transaction has not yet committed since it is on hold until Session B completes, which it can't because it is waiting for that initial Transaction to complete. This is a deadlock, but it can't be detected as such by SQL Server since it doesn't know that Session B was created by something within Session A. This behavior can be seen by replacing the first part of the IF statement in the example above in # 1 with the following:

   IF (@SQL IS NOT NULL)
   BEGIN
     /*
     PRINT @SQL; -- DEBUG
     COMMIT TRAN; -- close existing Transaction, else will get error
     EXEC sys.sp_executesql @sql;
     BEGIN TRAN; -- begin new Transaction, else will get different error
     */
     DECLARE @CMD NVARCHAR(MAX) = N'EXEC xp_cmdshell N''sqlcmd -S . -d master -E -Q "'
                                + @SQL + N';" -t 15''';
     PRINT @CMD;
     EXEC (@CMD);
   END;
   ELSE
   ...
   

   The -t 15 switch for SQLCMD sets the command/query timeout so that the test doesn't wait around forever with the default timeout. But, you can set it to be longer than 15 seconds and in another session check sys.dm_exec_requests to see all of the lovely blocking going on ;-).

3. Queue the event somewhere that will then read from that queue and execute the appropriate ALTER DATABASE statement. This will allow for the CREATE DATABASE statement to complete and its transaction to commit, after which an ALTER DATABASE statement can be executed. Service Broker could be used here. OR, create a table, have the Trigger insert into that table, then have a SQL Server Agent job call a Stored Procedure that reads from that table and executes the ALTER DATABASE statement and then removes the record from the queue Table.

HOWEVER, the above options are mainly provided to assist in scenarios where someone really does need to do some type of ALTER DATABASE within a DDL Trigger. In this particular scenario, if you really don't want any databases to be using the system / Instance-level default Collation, then you will probably be best served by:

1. Creating a new instance with the desired Collation and moving all of your user Databases over to it.
2. Or, if it is just the system Databases that are of the non-ideal Collation, it is probably safe to change the system Collation from the command-line via setup.exe (e.g. Setup.exe /Q /ACTION=Rebuilddatabase /INSTANCENAME=<instancename> /SQLCOLLATION=... ; this option recreates the system DBs, so you will need to script out server-level objects, etc to recreate later, plus re-apply patches, etc, FUN, FUN, FUN).

3. Or, for the adventurous-at-heart, there is the undocumented (i.e. unsupported, use-at-your-own-risk-but-might-very-well-work) sqlservr.exe -q option that updates ALL DBs and ALL columns (please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of the behavior of this option, as well as the potential scope of impact).

   Regardless of option chosen: always make sure to have backups of master and msdb before attempting such things.

The reason that it would be worth the effort to change the Server-level default Collation is that the Instance's (i.e. Server-level) default Collation controls a few functional areas that could lead to unexpected / inconsistent behavior is everyone is expecting string operations to function along the lines of the default Collation for all of your User Databases:

1. Default Collation for string columns in temporary tables. This is an issue only when being comparing to / Unioning with other string columns IF there is a mismatch between the two string columns. The issue here is that when not specifying the Collation explicitly via the COLLATE keyword, it is much more likely (though not guaranteed) to run into problems.

   This is not an issue for the XML datatype, table variables, or Contained Databases.

2. Instance-level meta-data. For example, the name field in sys.databases will use the Instance-level default Collation. Other system catalog views are also affected, but I don't have the complete list.

   Database-level meta-data, such as sys.objects and sys.indexes, are not affected.

3. Name resolution for:
   1. local variables (i.e. @variable)
   2. cursors
   3. GOTO labels

For example, if the Instance-level Collation is case-insensitive while the Database-level Collation is binary (i.e. ending in _BIN or _BIN2), then Database-level object name resolution will be binary (e.g. [TableA] <> [tableA]) yet variable names will allow for case-insensitivity (e.g. @VariableA = @variableA).

You can't ALTER DATABASE in a trigger. You'll need to get creative with evaluation and correcting. Something like:

EXEC sp_MSforeachdb N'IF EXISTS 
(
     select top 1 name from sys.databases where collation_name != 
     SQL_Latin1_General_CP1_CI_AS
)
BEGIN
    -- do something
END';

Though you shouldn't use sp_MSforeachdb.