EVENTDATA: примеры (SQL)

Примеры использования EVENTDATA

Простой триггер DDL для отслеживания создания таблиц:

CREATE TRIGGER Audit_Create_Table
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE @EventData XML = EVENTDATA();
    SELECT @EventData AS 'RawEventData';
    -- Извлечение отдельных элементов
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)') AS EventType,
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)') AS ObjectName,
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)') AS CommandText;
END;
GO

-- Выполняем тестовую команду
CREATE TABLE dbo.TestTable (ID INT);
GO

-- Результат в Messager (пример фрагмента XML):
-- <EVENT_INSTANCE>
--   <EventType>CREATE_TABLE</EventType>
--   <ObjectName>TestTable</ObjectName>
--   <TSQLCommand>
--     <SetOptions ... />
--     <CommandText>CREATE TABLE dbo.TestTable (ID INT)</CommandText>
--   </TSQLCommand>
-- </EVENT_INSTANCE>

-- Результат из SELECT внутри триггера:
EventType    | ObjectName | CommandText
CREATE_TABLE | TestTable  | CREATE TABLE dbo.TestTable (ID INT)

Похожие функции в MS SQL Server

Для аудита изменений данных (DML), а не структуры (DDL), применяются другие механизмы:

Триггеры DML (AFTER INSERT, UPDATE, DELETE): Внутри них доступны специальные таблицы inserted и deleted, содержащие новые и старые значения строк. Это прямой способ отслеживать изменения в данных таблицы.

Функция CHANGE_TRACKING: Позволяет отслеживать, какие строки были изменены в таблице, без сохранения истории самих изменений. Требует меньших затрат ресурсов, чем триггеры, но дает менее детальную информацию.

Система Temporal Tables: Наиболее мощная встроенная альтернатива для аудита данных. Автоматически сохраняет полную историю всех изменений строк в отдельной системной таблице. Используется, когда нужна полная история данных с возможностью запроса на любой момент времени.

Типичные ошибки

1. Вызов вне контекста триггера: Функция возвращает NULL, если вызвана в обычном пакете T-SQL.

-- Неправильно
SELECT EVENTDATA() AS EventData; -- Вернет NULL

EventData
NULL

2. Ошибка извлечения данных из XML: Неправильный XPath или тип данных в методе value() приводит к ошибке или NULL.

DECLARE @EventData XML = EVENTDATA();
-- Если узла NewObjectName для события CREATE_TABLE не существует:
SELECT @EventData.value('(/EVENT_INSTANCE/NewObjectName)[1]', 'NVARCHAR(255)'); -- Вернет NULL
-- Ошибка приведения, если тип указан неверно:
SELECT @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'INT'); -- Вызовет ошибку конвертации

3. Игнорирование XML-пространств имен: Некоторые события возвращают XML с пространствами имен, что требует их учета в XPath.

Изменения в последних версиях

В SQL Server 2022 существенных изменений в синтаксисе или возвращаемой структуре XML функции EVENTDATA не было. Основные изменения связаны с добавлением поддержки новых событий DDL для современных функций ядра СУБД (например, связанных с безопасностью на уровне строк или гибридным буферным пулом). Это означает, что при срабатывании триггера на такие новые события, XML-документ от EVENTDATA будет содержать соответствующие новые элементы и атрибуты, описывающие эти операции.

Расширенные примеры использования

Триггер, логирующий различные события DDL в таблицу с разбором XML:

CREATE TABLE dbo.DDLAuditLog (
    LogID INT IDENTITY PRIMARY KEY,
    EventTime DATETIME DEFAULT GETDATE(),
    EventType NVARCHAR(100),
    LoginName NVARCHAR(255),
    ObjectName NVARCHAR(255),
    CommandText NVARCHAR(MAX),
    RawEventData XML
);
GO

CREATE TRIGGER Audit_DDL_All
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @data XML = EVENTDATA();
    INSERT INTO dbo.DDLAuditLog (EventType, LoginName, ObjectName, CommandText, RawEventData)
    VALUES (
        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)'),
        @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
        @data
    );
END;
GO

-- Выполняем несколько команд
CREATE PROCEDURE dbo.TestProc AS BEGIN SELECT 1 END;
ALTER TABLE dbo.TestTable ADD Name NVARCHAR(50);
DROP PROCEDURE dbo.TestProc;
GO

-- Просмотр лога
SELECT LogID, EventType, ObjectName, LEFT(CommandText, 50) AS CommandPreview FROM dbo.DDLAuditLog;

LogID | EventType        | ObjectName | CommandPreview
1     | CREATE_PROCEDURE | TestProc   | CREATE PROCEDURE dbo.TestProc AS BEGIN SELECT 1 END
2     | ALTER_TABLE      | TestTable  | ALTER TABLE dbo.TestTable ADD Name NVARCHAR(50)
3     | DROP_PROCEDURE   | TestProc   | DROP PROCEDURE dbo.TestProc

Пример триггера входа, блокирующего создание новых сессий для определенного логина:

CREATE TRIGGER Audit_Logon_All
ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @data XML = EVENTDATA();
    DECLARE @LoginName NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)');
    IF @LoginName = 'FORBIDDEN_LOGIN'
    BEGIN
        ROLLBACK;
        INSERT INTO SomeSecurityLog (EventData) VALUES (@data); -- Запись в отдельную таблицу
    END
END;

Аналоги функции в других СУБД

Oracle: Использует системные пакеты, такие как ORA_DICT_OBJ_NAME в триггерах DDL. Контекст события доступен через атрибуты, а не единую XML-функцию.

-- Пример триггера DDL в Oracle
CREATE OR REPLACE TRIGGER audit_ddl
AFTER CREATE ON SCHEMA
BEGIN
    INSERT INTO ddl_audit (obj_name, event_type)
    VALUES (ORA_DICT_OBJ_NAME, ORA_SYSEVENT);
END;

PostgreSQL: Не имеет прямой аналогии. Для аудита DDL обычно используют расширение pgAudit, которое настраивается через параметры сервера и пишет логи в файл.

MySQL: Прямого аналога нет. Аудит DDL возможен через общий аудитный плагин, например, Audit Plugin, который также логирует события в файл.

SQLite: Не поддерживает триггеры DDL и подобные функции аудита на уровне схемы.