背景 上一篇中,我介紹了SQL Server 允許訪問資料庫的元數據，為什麼有元數據，如何使用元數據。這一篇中我會介紹如何進一步找到各種有價值的信息。以觸發器為例，因為它們往往一起很多問題。 那麼如何找到觸發器的數據? 以sys.system_viewsis表開始。讓我們查詢出資料庫中使用觸發器的信 ...
背景
上一篇中,我介紹了SQL Server 允許訪問資料庫的元數據，為什麼有元數據，如何使用元數據。這一篇中我會介紹如何進一步找到各種有價值的信息。以觸發器為例，因為它們往往一起很多問題。
那麼如何找到觸發器的數據?
以sys.system_viewsis表開始。讓我們查詢出資料庫中使用觸發器的信息。可以告知你當前SQL Server版本中有什麼觸發器。
SELECT schema_name(schema_ID)+'.'+ name FROM sys.system_views WHERE name LIKE '%trigger%'
---------------------------------------- sys.dm_exec_trigger_stats sys.server_trigger_events sys.server_triggers sys.trigger_event_types sys.trigger_events sys.triggers (6 row(s) affected)
其中sys.triggers看起來信息很多，它又包含什麼列？下麵這個查詢很容易查到：
SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id) + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information FROM sys.system_views AS TheView INNER JOIN sys.system_columns AS TheCol ON TheView.object_ID=TheCol.Object_ID WHERE TheView.name = 'triggers' ORDER BY column_ID;
結果如下:
Column_Information ---------------------------------------- name nvarchar NOT NULL object_id int NOT NULL parent_class tinyint NOT NULL parent_class_desc nvarchar NULL parent_id int NOT NULL type char NOT NULL type_desc nvarchar NULL create_date datetime NOT NULL modify_date datetime NOT NULL is_ms_shipped bit NOT NULL is_disabled bit NOT NULL is_not_for_replication bit NOT NULL is_instead_of_trigger bit NOT NULL
因此我們多這個信息有了更好的理解，有了一個目錄的目錄。這個概念有點讓人頭暈，但是另一方面，它也是相當簡單的。我們能夠查出元數據，再找個查詢中，需要做的就是改變這個單詞‘triggers’來查找你想要的視圖名稱。.
在2012及其以後版本，可以使用一個新的表值函數極大地簡化上述查詢，並可以避免各種連接。在下麵的查詢中，我們將查找sys.triggers 視圖 中的列。可以使用相同的查詢通過更改字元串中的對象名稱來獲取任何視圖的定義。
SELECT name+ ' '+ system_type_name + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information FROM sys.dm_exec_describe_first_result_set ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f ORDER BY column_ordinal;
查詢結果如下:
Column_Information ---------------------------------------- name nvarchar(128) NOT NULL object_id int NOT NULL parent_class tinyint NOT NULL parent_class_desc nvarchar(60) NULL parent_id int NOT NULL type char(2) NOT NULL type_desc nvarchar(60) NULL create_date datetime NOT NULL modify_date datetime NOT NULL is_ms_shipped bit NOT NULL is_disabled bit NOT NULL is_not_for_replication bit NOT NULL is_instead_of_trigger bit NOT NULL
sys.dm_exec_describe_first_result_set函數的最大優勢在於你能看到任何結果的列，不僅僅是表和視圖、存儲過程或者貶值函數。
為了查出任何列的信息，你可以使用稍微修改的版本，只需要改變代碼中的字元串'sys.triggers'即可，如下：
Declare @TheParamater nvarchar(255) Select @TheParamater = 'sys.triggers' Select @TheParamater = 'SELECT * FROM ' + @TheParamater SELECT name+ ' '+ system_type_name + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information FROM sys.dm_exec_describe_first_result_set ( @TheParamater, NULL, 0) AS f ORDER BY column_ordinal;
但是當然一個觸發器是首先是一個對象，因此一定在sys.objects?
在我們使用sys.triggers的信息之前，需要來重覆一遍，所有的資料庫對象都存在於sys.objects中，在SQL Server 中的對象包括以下：聚合的CLR函數，check 約束，SQL標量函數，CLR標量函數,CLR表值函數，SQL內聯表值函數，內部表，SQL存儲過程，CLR存儲過程，計劃指南，主鍵約束，老式規則，複製過濾程式，系統基礎表，同義詞，序列對象，服務隊列，CLR DML 觸發器，SQL表值函數，表類型，用戶自定義表，唯一約束，視圖和擴展存儲過程等。
觸發器是對象所以基礎信息一定保存在sys.objects。不走運的是，有時我們需要額外的信息，這些信息可以通過目錄視圖查詢。這些額外數據有是什麼呢？
修改我們使用過的查詢，來查詢sys.triggers的列，這次我們會看到額外信息。這些額外列是來自於sys.objects。
SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers, coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects FROM (SELECT Thecol.name FROM sys.system_views AS TheView INNER JOIN sys.system_columns AS TheCol ON TheView.object_ID=TheCol.Object_ID WHERE TheView.name = 'triggers') trigger_column FULL OUTER JOIN (SELECT Thecol.name FROM sys.system_views AS TheView INNER JOIN sys.system_columns AS TheCol ON TheView.object_ID=TheCol.Object_ID WHERE TheView.name = 'objects') object_column ON trigger_column.name=object_column.name 查詢結果： In_Sys_Triggers In_Sys_Objects ------------------------------ ---------------------- name name object_id object_id NOT INCLUDED principal_id NOT INCLUDED schema_id NOT INCLUDED parent_object_id type type type_desc type_desc create_date create_date modify_date modify_date is_ms_shipped is_ms_shipped NOT INCLUDED is_published NOT INCLUDED is_schema_published is_not_for_replication NOT INCLUDED is_instead_of_trigger NOT INCLUDED parent_id NOT INCLUDED is_disabled NOT INCLUDED parent_class NOT INCLUDED parent_class_desc NOT INCLUDED
以上這些讓我們知道在sys.triggers的額外信息，但是因為它始終是表的子對象，所以有些不相關信息是不會展示在這些指定的視圖或者sys.triggers中的。現在就要帶大家去繼續找找這些信息。
觸發器的問題
觸發器是有用的，但是因為它們在SSMS對象資源管理器窗格中不是可見的，所以一般用來提醒錯誤。觸發器有時候會有些微妙的地方讓其出問題，比如，當導入過程中禁用了觸發器，並且由於某些原因他們沒有重啟。
下麵是一個關於觸發器的簡要提醒:
觸發器可以在視圖，表或者伺服器上，任何這些對象上都可以有超過1個觸發器。普通的DML觸發器能被定義來執行替代一些數據修改（Insert,Update或者Delete）或者在數據修改之後執行。每一個觸發器與只與一個對象管理。DDL觸發器與資料庫關聯或者被定義在伺服器級別，這類觸發器一般在Create，Alter或者Drop這類SQL語句執行後觸發。
像DML觸發器一樣，可以有多個DDL觸發器被創建在同一個T-SQL語句上。一個DDL觸發器和語句觸發它的語句在同一個事務中運行，所以除了Alter DATABASE之外都可以被回滾。DDL觸發器運行在T-SQL語句執行完畢後，也就是不能作為Instead OF觸發器使用。
兩種觸發器都與事件相關，在DML觸發器中，包含INSERT, UPDATE, 和DELETE，然而很多事件都可以與DDL觸發器關聯，稍後我們將瞭解。
在資料庫中列出觸發器
那麼怎麼獲取觸發器列表？下麵我在AdventureWorks資料庫中進行查詢，註意該庫的視圖中沒有觸發器。
第一個查詢所有信息都在sys.triggers 的目錄視圖中。
SELECT name AS TriggerName, coalesce(object_schema_name(parent_ID)+'.' +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent FROM sys.triggers; TriggerName TheParent ------------------------------ ---------------------------------------- ddlDatabaseTriggerLog Database (AdventureWorks2012) dEmployee HumanResources.Employee iuPerson Person.Person iPurchaseOrderDetail Purchasing.PurchaseOrderDetail uPurchaseOrderDetail Purchasing.PurchaseOrderDetail uPurchaseOrderHeader Purchasing.PurchaseOrderHeader iduSalesOrderDetail Sales.SalesOrderDetail uSalesOrderHeader Sales.SalesOrderHeader dVendor Purchasing.Vendor iWorkOrder Production.WorkOrder uWorkOrder Production.WorkOrder
我使用元數據函數db_name()使SQL保持簡單。db_name()告訴我資料庫的名稱。object_schema_name()用來查詢object_ID代表的對象的架構，以及object_name()查詢對象名稱。這些對對象的引用指向觸發器的所有者，觸發器可以是資料庫本身，也可以是表：伺服器觸發器有自己的系統視圖，稍後我會展示。
如果想要看到所有觸發器，那麼我們最好使用sys.objects 視圖：
SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.' +object_name(parent_object_ID) AS TheParent FROM sys.objects WHERE OBJECTPROPERTYEX(object_id,'IsTrigger') = 1
註意，輸出不包含資料庫級別的觸發器，因為所有的DML觸發器都在sys.objects視圖中，但是你會漏掉在sys.triggers視圖中的觸發器。
上面查詢結果:
name TheParent ------------------------------ ------------------------------- dEmployee HumanResources.Employee iuPerson Person.Person iPurchaseOrderDetail Purchasing.PurchaseOrderDetail uPurchaseOrderDetail Purchasing.PurchaseOrderDetail uPurchaseOrderHeader Purchasing.PurchaseOrderHeader iduSalesOrderDetail Sales.SalesOrderDetail uSalesOrderHeader Sales.SalesOrderHeader dVendor Purchasing.Vendor iWorkOrder Production.WorkOrder uWorkOrder Production.WorkOrder
我的表和視圖有多少個觸發器?
我想知道每個表有多少個觸發器，並且什麼情況下觸發它們。下麵我們列出了具有觸發器的表以及每個事件的觸發器數量。每個表或者視圖對於觸發器行為都有一個INSTEAD OF 觸發器，可能是UPDATE, DELETE, 或者 INSERT
。但是一個表可以有多個AFTER觸發器行為。這些將展示在下麵的查詢中（排除視圖）：
SELECT convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.' +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete', convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert', convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update' FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers WHERE objectpropertyex(parent_ID, N'IsTable') =1 GROUP BY parent_ID )TablesOnly; --查詢結果如下: Table triggers Delete Insert Update -------------------------------- ----------- ------ ------ ------ Purchasing.Vendor 1 0 0 0 Production.WorkOrder 2 0 1 1 Purchasing.PurchaseOrderDetail 2 0 1 1 Purchasing.PurchaseOrderHeader 1 0 0 1 Sales.SalesOrderDetail 1 1 1 1 HumanResources.Employee 1 0 0 0 Sales.SalesOrderHeader 1 0 0 1 Person.Person 1 0 1 1 (8 row(s) affected)
如果超過一個觸發器被觸發在一個表上，它們不保證順序，當然也可以使用sp_settriggerorder來控制順序。通過使用objectpropertyex()元數據函數，需要根據事件輸入參數‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ 或者 ‘ExecIsLastUpdateTrigger’來確認誰是最後一個執行的觸發器 。為了得到第一個觸發器，酌情使用ObjectPropertyEx() 元數據函數，需要輸入參數 ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ 或者 ‘ExecIsFirstUpdateTrigger’。
因此我們現在知道了表有哪些觸發器，哪些事件觸發這些觸發器。可以使用objectpropertyex()元數據函數，這個函數返回很多不同信息，根據指定的參數不同。通過查看MSDN中的文檔，查看其中的一個文檔是否有助於元數據查詢，總是值得檢查的。
觸發器何時觸發事件?
讓我們看一下這些觸發器，DML觸發器可以在所有其他時間發生後觸發，但是可以在約束被處理前並且觸發INSTEAD OF觸發動作。下麵我們就來看看所有的觸發的到底是AFTER 還是INSTEAD OF 觸發器，有事什麼時間觸發了觸發器。
/* 列出觸發器，無論它們是否啟用，以及觸發器事件。*/ SELECT convert(CHAR(25),name) AS triggerName, convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.' +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent, is_disabled, CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END +Stuff (--get a list of events for each trigger (SELECT ', '+type_desc FROM sys.trigger_events te WHERE te.object_ID=sys.triggers.object_ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events FROM sys.triggers;
結果如下:
triggerName TheParent is_disabled events ------------------------- -------------------------------- ----------- --------- ddlDatabaseTriggerLog Database (AdventureWorks2012) 1 AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT t_AB dbo.AB 0 INSTEAD OF INSERT dEmployee HumanResources.Employee 0 INSTEAD OF DELETE iuPerson Person.Person 0 AFTER INSERT, UPDATE iPurchaseOrderDetail Purchasing.PurchaseOrderDetail 0 AFTER INSERT uPurchaseOrderDetail Purchasing.PurchaseOrderDetail 0 AFTER UPDATE uPurchaseOrderHeader Purchasing.PurchaseOrderHeader 0 AFTER UPDATE iduSalesOrderDetail Sales.SalesOrderDetail 0 AFTER INSERT, UPDATE, DELETE uSalesOrderHeader Sales.SalesOrderHeader 0 AFTER UPDATE dVendor Purchasing.Vendor 0 INSTEAD OF DELETE iWorkOrder Production.WorkOrder 0 AFTER INSERT uWorkOrder Production.WorkOrder 0 AFTER UPDATE
As you will notice, we used a FOR XML PATH(‘’) trick here to make a list of the events for each trigger to make it easier to read. These events were pulled from the sys.trigger_events view using a correlated subquery.
註意到我們使用了FOR XML PATH(‘’) 來列出事件的每一個觸發器，更容易讀取理解。sys.trigger_events使用相關子查詢來查詢這些事件。
觸發器的多長?
許多資料庫人員不贊成冗長觸發器的定義，但他們可能會發現，根據定義的長度排序的觸發器列表是研究資料庫的一種有用方法。
SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','') +name) AS TheTrigger, convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.' +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent, len(definition) AS length --the length of the definition FROM sys.SQL_modules m INNER JOIN sys.triggers t ON t.object_ID=m.object_ID ORDER BY length DESC;
訪問sys.SQL_modules視圖可以查看觸發器定義的SQL DDL，並按大小順序列出它們，最上面是最大的。
結果:
TheTrigger theParent length -------------------------------- -------------------------------- -------- Sales.iduSalesOrderDetail Sales.SalesOrderDetail 3666 Sales.uSalesOrderHeader Sales.SalesOrderHeader 2907 Purchasing.uPurchaseOrderDetail Purchasing.PurchaseOrderDetail 2657 Purchasing.iPurchaseOrderDetail Purchasing.PurchaseOrderDetail 1967 Person.iuPerson Person.Person 1498 ddlDatabaseTriggerLog Database (AdventureWorks2012) 1235 Purchasing.dVendor Purchasing.Vendor 1103 Production.uWorkOrder Production.WorkOrder 1103 Purchasing.uPurchaseOrderHeader Purchasing.PurchaseOrderHeader 1085 Production.iWorkOrder Production.WorkOrder 1011 HumanResources.dEmployee HumanResources.Employee 604
好吧，我可能太挑剔了,不太喜歡太長的，但是邏輯有時候會很長。事實上，前三名在我看來是不可靠的，儘管我總是傾向於儘可能少地使用觸發器。
這些觸發器訪問了多少對象
在代碼中，每個觸發器要訪問多少對象(比如表和函數)?
我們只需要檢查表達式依賴項。這個查詢使用一個視圖來列出“軟”依賴項（如觸發器、視圖和函數)。
SELECT coalesce(object_schema_name(parent_id) +'.','')+convert(CHAR(32),name) AS TheTrigger, count(*) AS Dependencies FROM sys.triggers INNER JOIN sys.SQL_Expression_dependencies ON [referencing_id]=object_ID GROUP BY name, parent_id ORDER BY count(*) DESC; --結果: TheTrigger Dependencies ---------------------------------------- ------------ Sales.iduSalesOrderDetail 7 Sales.uSalesOrderHeader 7 Purchasing.iPurchaseOrderDetail 5 Purchasing.uPurchaseOrderDetail 5 Purchasing.uPurchaseOrderHeader 3 Production.iWorkOrder 3 Production.uWorkOrder 3 dbo.t_AB 2 Purchasing.dVendor 2 Person.iuPerson 2 ddlDatabaseTriggerLog 1
居然有兩個觸發器有7個依賴！讓我們就Sales.iduSalesOrderDetail來實際看一下，有哪些依賴。
特定觸發器訪問或者寫入哪些對象?
我們可以列出觸發器在代碼中引用的所有對象
SELECT convert(char(32),name) as TheTrigger, convert(char(32),coalesce([referenced_server_name]+'.','') +coalesce([referenced_database_name]+'.','') +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name]) as referencedObject FROM sys.triggers INNER JOIN sys.SQL_Expression_dependencies ON [referencing_id]=object_ID WHERE name LIKE 'iduSalesOrderDetail'; --查詢結果: TheTrigger referencedObject -------------------------------- -------------------------------- iduSalesOrderDetail Sales.Customer iduSalesOrderDetail Person.Person iduSalesOrderDetail Sales.SalesOrderDetail iduSalesOrderDetail Sales.SalesOrderHeader iduSalesOrderDetail Production.TransactionHistory iduSalesOrderDetail dbo.uspLogError iduSalesOrderDetail dbo.uspPrintError
觸發器里有什麼代碼?
現在讓我們通過檢查觸發器的源代碼來確認這一點。.
SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') );
我們之前的查詢是正確的，掃描源碼可知所有的依賴項。大量依賴項表名對於資料庫的重構等需要非常小心，例如，修改一個基礎表的列。
據需要做什麼，您可能希望檢查來自元數據視圖的定義，而不是使用OBJECT_DEFINITION函數。
SELECT definition FROM sys.SQL_modules m INNER JOIN sys.triggers t ON t.object_ID=m.object_ID WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');
搜索觸發器的代碼
There are always plenty of ways of using the metadata views and functions. I wonder if all these triggers are executing that uspPrintError procedure?
有很多使用元數據視圖和函數的方法。想知道是否所有這些觸發器都執行uspPrintError存儲過程?
/* 在所有觸發器中搜索字元串 */
SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','') +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...' FROM (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit FROM sys.SQL_modules m INNER JOIN sys.triggers t ON t.object_ID=m.object_ID)f WHERE hit>0;
結果如圖：
8個引用正在執行這個過程。我們在sys.SQL_modules中搜索了所有的定義可以找到一個特定的字元串，這種方式很慢很暴力，但是它是有效的!
在所有對象中搜索字元串
我想知道除了觸發器之外是否還有其他對象調用這個過程?我們稍微修改查詢以搜索sys.objects視圖，而不是sys.triggers，以搜索所有具有與之關聯的代碼的對象。我們還需要顯示對象的類型
/* 在所有對象中搜索字元串 */
SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','') +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract FROM (SELECT type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit FROM sys.SQL_modules m INNER JOIN sys.objects o ON o.object_ID=m.object_ID)f WHERE hit>0;
查詢結果如下圖：
From this output we can see that, other than the procedure itself where it is defined, and the triggers, only dbo.uspLogError is executing the uspPrintError procedure. (see the first column, second line down)
從這個輸出中我們可以看到，除了在定義它的過程本身之外，還有觸發器，只有dbo.uspLogError正在執行uspPrintError過程。(見第一列，第二行往下)
列出伺服器級觸發器及其定義
我們可以通過系統視圖瞭解它們嗎?嗯,是的。以下是列出伺服器觸發器及其定義的語句
SELECT name, definition
FROM sys.server_SQL_modules m
INNER JOIN sys.server_triggers t
ON t.object_ID=m.object_ID;
註意，只能看到有許可權看的觸發器
總結
本文討論過觸發器，並且你能查出觸發器，以及潛在的問題。這裡並沒有針對關於觸發器的查詢提供一個全面的工具箱，因為我只是使用觸發器作為示例來展示在查詢系統視圖時可能使用的一些技術。在我們學習了索引、列和參數之後，我們將回到觸發器，並瞭解了編寫訪問系統視圖和information schema視圖的查詢的一些日常用途。表是元數據的許多方面的基礎。它們是幾種類型的對象的父類，其他元數據如索引是表的屬性。我們正在慢慢地努力去發現所有關於表的信息。期待下期
