Menggunakan Fungsi EVENTDATA
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Informasi tentang peristiwa yang mengaktifkan pemicu DDL diambil dengan menggunakan fungsi EVENTDATA. Fungsi ini mengembalikan nilai xml . Skema XML menyertakan informasi tentang hal berikut:
Waktu peristiwa.
ID Proses Sistem (SPID) koneksi saat pemicu dijalankan.
Jenis peristiwa yang menembakkan pemicu.
Bergantung pada jenis peristiwa, skema kemudian menyertakan informasi tambahan seperti database tempat peristiwa terjadi, objek tempat peristiwa terjadi, dan pernyataan Transact-SQL dari peristiwa tersebut. Untuk informasi selengkapnya, lihat Pemicu DDL.
Misalnya, pemicu DDL berikut dibuat dalam AdventureWorks2022 database sampel:
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
;
Pernyataan berikut CREATE TABLE kemudian dijalankan:
CREATE TABLE NewTable (Column1 int);
Pernyataan EVENTDATA() dalam pemicu DDL menangkap teks CREATE TABLE pernyataan yang tidak diizinkan. Ini dicapai dengan menggunakan pernyataan XQuery terhadap data xml yang dihasilkan oleh EVENTDATA dan mengambil <elemen CommandText> . Untuk informasi selengkapnya, lihat Referensi Bahasa XQuery (SQL Server).
Perhatian
EVENTDATA mengambil data peristiwa CREATE_SCHEMA serta <schema_element> definisi CREATE SCHEMA yang sesuai, jika ada. Selain itu, EVENTDATA mengenali <definisi schema_element> sebagai peristiwa terpisah. Oleh karena itu, pemicu DDL yang dibuat pada peristiwa CREATE_SCHEMA, dan peristiwa yang diwakili oleh <schema_element> definisi CREATE SCHEMA, dapat mengembalikan data peristiwa yang sama dua kali, seperti TSQLCommand data. Misalnya, pertimbangkan pemicu DDL yang dibuat pada peristiwa CREATE_SCHEMA dan CREATE_TABLE dan batch berikut dijalankan:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Jika aplikasi mengambil TSQLCommand data peristiwa CREATE_TABLE, ketahuilah bahwa data ini mungkin muncul dua kali: sekali ketika peristiwa CREATE_SCHEMA terjadi, dan sekali lagi ketika peristiwa CREATE_TABLE terjadi. Hindari membuat pemicu DDL pada peristiwa CREATE_SCHEMA dan <teks schema_element> dari definisi CREATE SCHEMA yang sesuai, atau buat logika ke dalam aplikasi Anda sehingga peristiwa yang sama tidak diproses dua kali.
UBAH TABEL dan UBAH Peristiwa DATABASE
Data peristiwa untuk peristiwa ALTER_TABLE dan ALTER_DATABASE juga mencakup nama dan jenis objek lain yang terpengaruh oleh pernyataan DDL dan tindakan yang dilakukan pada objek ini. Data peristiwa ALTER_TABLE mencakup nama kolom, batasan, atau pemicu yang terpengaruh oleh pernyataan ALTER TABLE dan tindakan (membuat, mengubah, menghilangkan, mengaktifkan, atau menonaktifkan) yang dilakukan pada objek yang terpengaruh. Data peristiwa ALTER_DATABASE mencakup nama file atau grup file apa pun yang terpengaruh oleh pernyataan ALTER DATABASE dan tindakan (buat, ubah, atau letakkan) yang dilakukan pada objek yang terpengaruh.
Misalnya, buat pemicu DDL berikut di database sampel 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;
Kemudian jalankan pernyataan ALTER TABLE berikut yang melanggar batasan:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
Pernyataan EVENTDATA() dalam pemicu DDL menangkap teks ALTER TABLE pernyataan yang tidak diizinkan.
Contoh
Anda dapat menggunakan fungsi EVENTDATA untuk membuat log peristiwa. Dalam contoh berikut, tabel dibuat untuk menyimpan informasi peristiwa. Pemicu DDL kemudian dibuat pada database saat ini yang mengisi tabel dengan informasi berikut setiap kali peristiwa DDL tingkat database terjadi:
Waktu peristiwa (menggunakan fungsi GETDATE).
Pengguna database terhadap sesi yang peristiwanya terjadi (menggunakan fungsi CURRENT_USER).
Jenis peristiwa.
Pernyataan Transact-SQL yang terdiri dari peristiwa tersebut.
Sekali lagi, dua item terakhir diambil dengan menggunakan XQuery terhadap data xml yang dihasilkan oleh 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
Catatan
Untuk mengembalikan data peristiwa, kami sarankan Anda menggunakan metode nilai XQuery() alih-alih metode query(). Metode query() mengembalikan instans XML dan ampersand-escaped carriage return dan line-feed (CRLF) dalam output, sementara metode value() merender instans CRLF tidak terlihat dalam output.
Contoh pemicu DDL serupa disediakan dengan AdventureWorks2022 database sampel. Untuk mendapatkan contoh, temukan folder Pemicu Database dengan menggunakan SQL Server Management Studio. Folder ini terletak di bawah folder Programmability database AdventureWorks2022. Klik kanan ddlDatabaseTriggerLog dan pilih Pemicu Database Skrip sebagai. Secara default, pemicu DDL ddlDatabaseTriggerLog dinonaktifkan.