觸發器以在創建時更改數據庫排序規則

我正在嘗試創建一個觸發器，以在創建數據庫時更改數據庫的排序規則，但是如何擷取要在觸發器中使用的數據庫名稱？

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

顯然，這是行不通的。

一般來說，您不能ALTER DATABASE在觸發器（或其中包含其他語句的任何事務）中發出問題。如果您嘗試這樣做，您將收到以下錯誤：

消息 226，級別 16，狀態 6，行 xxxx

ALTER DATABASE 語句不允許在多語句事務中。

@sp_BlitzErik 的回答中沒有遇到此錯誤的原因是提供的特定測試案例的結果：上面顯示的錯誤是執行時錯誤，而他的回答中遇到的錯誤是編譯時錯誤。該編譯時錯誤阻止了命令的執行，因此沒有“執行時”。我們可以通過執行以下命令來查看差異：

SET NOEXEC ON;

SELECT N'g' COLLATE Latin1;

SET NOEXEC OFF;

上面的批處理會出錯，而下面的不會：

SET NOEXEC ON;

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

SET NOEXEC OFF;

這使您有兩個選擇：

1. 在 DDL 觸發器中送出事務，以便事務中沒有其他語句。如果一個語句可以觸發多個 DDL 觸發器，這不是一個好主意CREATE DATABASE，並且通常可能是一個壞主意，但它確實有效；-)。訣竅是您還需要在觸發器中開始一個新的事務，否則 SQL Server 會注意到的開始和結束值@@TRANCOUNT不匹配，並會引發與此相關的錯誤。下面的程式碼就是這樣做的，並且只發出ALTER如果排序規則不是所需的排序規則，否則它會跳過ALTER命令。

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

測試：

-- 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. SqlConnection使用SQLCLR 在連接字元串中建立一個正常的 / externalEnlist = false;來發出ALTER命令，因為這將不是事務的一部分。

SQLCLR 似乎不是一個真正的選項，儘管不是由於 SQLCLR 的任何特定限制。不知何故，直接在上面輸入“ as that will not be part of the Transaction ”並沒有充分突出這樣一個事實，即事實上，圍繞該CREATE DATABASE操作存在一個活動的 Transaction。這裡的問題是，雖然 SQLCLR可用於跳出目前事務，但在初始事務送出之前，另一個會話仍然無法修改目前正在創建的數據庫。

意思是，會話 A 為創建數據庫和触發觸發器創建事務。觸發器，使用 SQLCLR，將創建會話 B 以修改已創建的數據庫，但事務尚未送出，因為它在會話 B 完成之前暫停，因為它正在等待初始事務到完全的。這是一個死鎖，但 SQL Server 無法檢測到它，因為它不知道 Session B 是由 Session A 中的某些東西創建的。通過替換IF範例中語句的第一部分可以看到這種行為上面#1中的內容如下：

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
...

SQLCMD的-t 15開關設置命令/查詢超時，以便測試不會在預設超時下永遠等待。但是，您可以將其設置為超過 15 秒，然後在另一個會話中檢查以查看所有可愛的阻塞；-)。sys.dm_exec_requests 3. 將事件排隊到某處，然後從該隊列中讀取並執行相應的ALTER DATABASE語句。這將允許CREATE DATABASE語句完成並送出其事務，之後ALTER DATABASE可以執行語句。此處可以使用 Service Broker。或者，創建一個表，將觸發器插入該表，然後讓 SQL Server 代理作業呼叫儲存過程，該儲存過程從該表中讀取並執行ALTER DATABASE語句，然後從隊列表中刪除記錄。

但是，提供上述選項主要是為了幫助某些人確實需要ALTER DATABASE在 DDL 觸發器中執行某種類型的操作。在這種特殊情況下，如果您真的不希望任何數據庫使用系統/實例級別的預設排序規則，那麼您可能會得到最好的服務：

1. 使用所需的排序規則創建一個新實例並將所有使用者數據庫移至該實例。
2. 或者，如果只是系統數據庫屬於非理想排序規則，則通過 setup.exe 從命令行更改系統排序規則可能是安全的（例如Setup.exe /Q /ACTION=Rebuilddatabase /INSTANCENAME=<instancename> /SQLCOLLATION=...；此選項重新創建系統 DB，因此您需要編寫伺服器級對像等腳本以便稍後重新創建，再加上重新應用更新檔等，FUN，FUN，FUN）。
3. 或者，對於有冒險精神的人來說，有一個未記錄的（即不受支持的，在你自己的風險下使用但可能非常好用）的sqlservr.exe -q選項可以更新所有數據庫和所有列（請參閱更改實例、數據庫和所有使用者數據庫中的所有列的排序規則：什麼可能出錯？有關此選項行為的詳細描述以及潛在的影響範圍）。

無論選擇何種選項：始終確保在嘗試此類事情之前進行master備份。msdb

值得努力更改伺服器級預設排序規則的原因是實例的（即伺服器級）預設排序規則控制了一些可能導致意外/不一致行為的功能區域，因為每個人都希望字元串操作能夠正常工作按照所有使用者數據庫的預設排序規則：

1. 臨時表中字元串列的預設排序規則。僅當與其他字元串列進行比較/聯合時，如果兩個字元串列之間存在不匹配，這才是一個問題。這裡的問題是，當不通過COLLATE關鍵字顯式指定排序規則時，更有可能（儘管不能保證）遇到問題。

對於 XML 數據類型、表變數或包含的數據庫，這不是問題。 2. 實例級元數據。例如，中的name欄位sys.databases將使用實例級別的預設排序規則。其他系統目錄視圖也受到影響，但我沒有完整列表。

數據庫級元數據，例如sys.objects和sys.indexes，不受影響。 3. 名稱解析：

1. 局部變數（即@variable）
2. 游標
3. GOTO標籤

例如，如果實例級別的排序規則不區分大小寫，而數據庫級別的排序規則是二進制的（即以_BINor結尾_BIN2），那麼數據庫級別的對象名稱解析將是二進制的（例如[TableA] <> [tableA]）但變數名稱將允許不區分大小寫（例如@VariableA = @variableA）。

引用自：https://dba.stackexchange.com/questions/174219