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

适用对象:是SQL Server 是Azure SQL 数据库 否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

某些可以在 Transact-SQLTransact-SQL 标识符和数据中显示的字符不能或不允许在 XML 中使用。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\nnn\ C:\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\nnn\ C:\Program Files\Microsoft SQL Server\nnn\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.

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. 请改用“以文本格式显示结果” 。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 和以“and”符转义的回车符和换行符 (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