Korzystanie z funkcji EVENTDATA
Dotyczy:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Informacje o zdarzeniu, które uruchamia wyzwalacz DDL, są przechwytywane za pomocą funkcji EVENTDATA. Ta funkcja zwraca wartość xml. Schemat XML zawiera informacje o następujących kwestiach:
Czas zdarzenia.
Identyfikator procesu systemowego (SPID) połączenia po wykonaniu wyzwalacza.
Typ zdarzenia, które wyzwoliło wyzwalacz.
W zależności od typu zdarzenia schemat zawiera następnie dodatkowe informacje, takie jak baza danych, w której wystąpiło zdarzenie, obiekt, względem którego wystąpiło zdarzenie, oraz instrukcja Transact-SQL zdarzenia. Aby uzyskać więcej informacji, zobacz wyzwalacz DDL.
Na przykład następujący wyzwalacz DDL jest tworzony w przykładowej bazie danych AdventureWorks2022:
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
;
Następnie zostanie uruchomiona następująca instrukcja CREATE TABLE:
CREATE TABLE NewTable (Column1 int);
Instrukcja EVENTDATA() w wyzwalaczu DDL zawiera tekst instrukcji CREATE TABLE, która nie jest dozwolona. Jest to osiągane przy użyciu instrukcji XQuery względem danych xml generowanych przez zdarzenie EVENTDATA i pobierania elementu <CommandText>. Aby uzyskać więcej informacji, zobacz XQuery Language Reference (SQL Server).
Ostrożność
EVENTDATA przechwytuje dane zdarzeń CREATE_SCHEMA, a także <schema_element> odpowiadający definicji CREATE SCHEMA, jeśli taka istnieje. Ponadto funkcja EVENTDATA rozpoznaje definicję <schema_element> jako osobne zdarzenie. W związku z tym wyzwalacz DDL utworzony zarówno dla zdarzenia CREATE_SCHEMA, jak i zdarzenie reprezentowane przez <schema_element> definicji CREATE SCHEMA, może zwrócić te same dane zdarzenia dwa razy, takie jak dane TSQLCommand. Rozważmy na przykład wyzwalacz DDL, który jest tworzony zarówno na zdarzeniach CREATE_SCHEMA, jak i CREATE_TABLE, a następnie uruchamiana jest następująca partia:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Jeśli aplikacja pobiera dane TSQLCommand zdarzenia CREATE_TABLE, należy pamiętać, że te dane mogą pojawić się dwa razy: raz po wystąpieniu zdarzenia CREATE_SCHEMA i ponownie, gdy wystąpi zdarzenie CREATE_TABLE. Unikaj tworzenia wyzwalaczy DDL zarówno dla zdarzeń CREATE_SCHEMA, jak i <schema_element> tekstów odpowiadających definicjom CREATE SCHEMA, lub zaprogramuj logikę w aplikacji tak, aby to samo zdarzenie nie było przetwarzane dwa razy.
ALTER TABLE i ALTER DATABASE Events
Dane zdarzenia dla zdarzeń ALTER_TABLE i ALTER_DATABASE zawierają również nazwy i typy innych obiektów, których dotyczy instrukcja DDL i akcja wykonywana na tych obiektach. Dane zdarzenia ALTER_TABLE obejmują nazwy kolumn, ograniczeń lub wyzwalaczy, których dotyczy instrukcja ALTER TABLE, oraz akcje (tworzenie, zmienianie, usuwanie, włączanie lub wyłączanie) wykonywane na tych obiektach. pl-PL: Dane zdarzenia ALTER_DATABASE zawierają nazwy wszystkich plików lub grup plików, których dotyczy instrukcja ALTER DATABASE, oraz operację (tworzenie, zmienianie lub usuwanie) wykonaną na dotkniętych obiektach.
Na przykład utwórz następujący wyzwalacz DDL w przykładowej bazie danych AdventureWorks:
CREATE TRIGGER ColumnChanges
ON DATABASE
FOR ALTER_TABLE
AS
-- Detect whether a column was created/altered/dropped.
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);
ROLLBACK;
Następnie wykonaj następującą instrukcję ALTER TABLE, która narusza ograniczenie:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
Instrukcja EVENTDATA() w wyzwalaczu DDL przechwytuje tekst instrukcji ALTER TABLE, która nie jest dozwolona.
Przykład
Za pomocą funkcji EVENTDATA można utworzyć dziennik zdarzeń. W poniższym przykładzie zostanie utworzona tabela do przechowywania informacji o zdarzeniach. Wyzwalacz DDL jest następnie tworzony w bieżącej bazie danych, która wypełnia tabelę następującymi informacjami przy każdym wystąpieniu zdarzenia DDL na poziomie bazy danych:
Czas zdarzenia (przy użyciu funkcji GETDATE).
Użytkownik bazy danych, w sesji którego wystąpiło zdarzenie (przy użyciu funkcji CURRENT_USER).
Typ zdarzenia.
Instrukcja Transact-SQL, która tworzyła to zdarzenie.
Ponownie dwa ostatnie elementy są przechwytywane przy użyciu zapytania XQuery względem danych xml generowanych przez zdarzenie EVENTDATA.
USE AdventureWorks2022;
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
Notatka
Aby zwrócić dane zdarzenia, zalecamy użycie metody XQuery value() zamiast metody query(). Metoda query() zwraca w danych wyjściowych wystąpienia XML i ampersand-escaped powrotu karetki i znaku nowej linii (CRLF), podczas gdy metoda value() renderuje wystąpienia CRLF jako niewidoczne w danych wyjściowych.
Podobny przykład wyzwalacza DDL jest udostępniany z przykładową bazą danych AdventureWorks2022. Aby uzyskać przykład, znajdź folder Wyzwalacze bazy danych przy użyciu programu SQL Server Management Studio. Ten folder znajduje się w folderze Programmability bazy danych AdventureWorks2022. Kliknij prawym przyciskiem myszy ddlDatabaseTriggerLog i wybierz wyzwalacz bazy danych skryptów jako. Domyślnie wyzwalacz DDL ddlDatabaseTriggerLog jest wyłączony.