EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此函數會傳回伺服器或資料庫事件的相關資訊。This function returns information about server or database events. 當事件通知引發,且指定的 Service Broker 收到結果時,會呼叫 EVENTDATAWhen an event notification fires, and the specified service broker receives the results, EVENTDATA is called. DDL 或登入觸發程序也支援在內部使用 EVENTDATAA DDL or logon trigger also support internal use of EVENTDATA.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
EVENTDATA( )  

RemarksRemarks

只有在 DDL 或登入觸發程序內直接參考時,EVENTDATA 才會傳回資料。EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger. 如果 EVENTDATA 是由其他常式所呼叫,它便會傳回 NULL,即使那些常式是由 DDL 或登入觸發程序所呼叫亦然。EVENTDATA returns null if other routines call it, even if a DDL or logon trigger calls those routines.

EVENTDATA 傳回的資料在以下交易之後無效:Data returned by EVENTDATA is invalid after a transaction that

  • 明確呼叫 EVENTDATAcalled EVENTDATA explicitly
  • 隱含呼叫 EVENTDATAcalled EVENTDATA implicitly
  • 認可commits
  • 會回復is rolled back

警告

EVENTDATA 會傳回 XML 資料,這項資料會以 Unicode 的方式傳給用戶端,每個字元佔 2 個位元組。EVENTDATA returns XML data, sent to the client as Unicode that uses 2 bytes for each character. EVENTDATA 會傳回可代表這些 Unicode 字碼指標的 XML:EVENTDATA returns XML that can represent these Unicode code points:

0x0009

0x000A

0x000D

>= 0x0020 && <= 0xD7FF

>= 0xE000 && <= 0xFFFD

XML 無法表示且不允許使用 Transact-SQLTransact-SQL 識別碼和資料中所能使用的某些字元。XML cannot express, and will not permit, some characters that can appear in Transact-SQLTransact-SQL identifiers and data. 有上述清單所未顯示之字碼指標的字元或資料會對應至問號 (?)。Characters or data that have code points not shown in the previous list are mapped to a question mark (?).

執行 CREATE LOGINALTER LOGIN 陳述式時,不會顯示密碼。Passwords do not display when CREATE LOGIN or ALTER LOGIN statements execute. 這可以保護登入安全性。This protects login security.

傳回的結構描述Schemas Returned

EVENTDATA 會傳回 xml 資料類型的值。EVENTDATA returns a value of data type xml. 根據預設,所有事件的結構描述定義都會安裝在以下目錄:C:\Program Files\Microsoft SQL Server\nnnC:\Program Files\Microsoft SQL Server\nnn\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd。By default, the schema definition for all events installs in this directory: C:\Program Files\Microsoft SQL Server\nnnC:\Program Files\Microsoft SQL Server\nnn\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.

Microsoft SQL Server XML Schemas (Microsoft SQL Server XML 結構描述) 網頁也會有事件結構描述。The Microsoft SQL Server XML Schemas web page also has the event schema.

若要針對任何特定的事件擷取結構描述,請搜尋複雜類型 EVENT_INSTANCE_<event_type> 的結構描述。To extract the schema for any particular event, search the schema for the Complex Type EVENT_INSTANCE_<event_type>. 例如,若要擷取 DROP_TABLE 事件的結構描述,請搜尋 EVENT_INSTANCE_DROP_TABLE 的結構描述。For example, to extract the schema for the DROP_TABLE event, search the schema for EVENT_INSTANCE_DROP_TABLE.

範例Examples

A.A. 查詢 DDL 觸發程序中的事件資料Querying event data in a DDL trigger

此範例會建立可防止建立新資料庫資料表的 DDL 觸發程序。This example creates a DDL trigger that prevents creation of new database tables. 針對 EVENTDATA 所產生的 XML 資料使用 XQuery ,可擷取引發觸發程序的 Transact-SQLTransact-SQL 陳述式。Use of XQuery against the XML data generated by EVENTDATA captures the Transact-SQLTransact-SQL statement that fires the trigger. 如需詳細資訊,請參閱 XQuery 語言參考 (SQL Server)See XQuery Language Reference (SQL Server) for more information.

注意

當您使用 SQL Server Management StudioSQL Server Management Studio 中的 [以方格顯示結果] 來查詢 <TSQLCommand> 元素時,命令文字中不會出現分行符號。When using Results to Grid in SQL Server Management StudioSQL Server Management Studio to query the <TSQLCommand> element, line breaks in the command text do not appear. 請改用 [Results to Text] (以文字顯示結果) 。Use Results to Text instead.

USE AdventureWorks2012;  
GO  
CREATE TRIGGER safety   
ON DATABASE   
FOR CREATE_TABLE   
AS   
    PRINT 'CREATE TABLE Issued.'  
    SELECT EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('New tables cannot be created in this database.', 16, 1)   
   ROLLBACK  
;  
GO  
--Test the trigger.  
CREATE TABLE NewTable (Column1 int);  
GO  
--Drop the trigger.  
DROP TRIGGER safety  
ON DATABASE;  
GO  

注意

若要傳回事件資料,請使用 XQuery value() 方法,不要使用 query() 方法。To return event data, use the XQuery value() method instead of the query() method. query() 方法會在輸出中傳回 XML 和逸出 & 符號的歸位字元及換行字元 (CR/LF) 執行個體,value() 方法則會在輸出中將 CR/LF 轉譯為不可見。The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output.

B.B. 利用 DDL 觸發程序中的事件資料來建立記錄資料表Creating a log table with event data in a DDL trigger

此範例會建立一份資料表來儲存和所有資料庫層級事件有關的資訊,且會以 DDL 觸發程序來擴展該資料表。This example creates a table for information storage about all database level events, and populates that table with a DDL trigger. 針對 EVENTDATA 所產生的 XML 資料使用 XQuery ,可擷取事件類型和 Transact-SQLTransact-SQL 陳述式。Use of XQuery against the XML data generated by EVENTDATA captures the event type and the Transact-SQLTransact-SQL statement.

USE AdventureWorks2012;  
GO  
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));  
GO  
CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT ddl_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(nvarchar(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;  
GO  
--Test the trigger.  
CREATE TABLE TestTable (a int);  
DROP TABLE TestTable ;  
GO  
SELECT * FROM ddl_log ;  
GO  
--Drop the trigger.  
DROP TRIGGER log  
ON DATABASE;  
GO  
--Drop table ddl_log.  
DROP TABLE ddl_log;  
GO  

另請參閱See Also

使用 EVENTDATA 函式 Use the EVENTDATA Function
DDL 觸發程序 DDL Triggers
事件通知 Event Notifications
登入觸發程序Logon Triggers