EventData() function
Introduction
From MS SQL Server 2005, we can get data regarding the event that initiated the DDL trigger by accessing the EventData()function.
How it’s Works
The main tasks of the function named EventData() is to return the information about the Server or Database Event and stored in a variable with data type XML. We just capture the data return by EventData() function and store it to a Table object for showing report.
If needed we can store the data to directly a XML data type holder columns of a table or process the data and store it to the different columns according to needs. As the return type of the function named EventData() is in XML format , we can apply XQuery against it.
To understand it properly let’s take an example
Example
IF OBJECT_ID(N'dbo.tbl_EVENTTRACKER', N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EVENTTRACKER];
END
GO
CREATE TABLE [dbo].[tbl_EVENTTRACKER]
(
EVENTTYPE nVARCHAR(50) NULL,
SPID nVARCHAR(50) NULL,
LOGINNAME nVARCHAR(50) NULL,
TSQLCODE nVARCHAR(300) NULL,
SERVERNAME nVARCHAR(50) NULL,
DBNAME nVARCHAR(50) NULL
);
GO
Trigger Definition
IF OBJECT_ID(N'dbo.trg_MYDDLEVENT', N'TR') IS NOT NULL
BEGIN
DROP TABLE [dbo].[trg_MYDDLEVENT];
END
GO
CREATE TRIGGER [dbo].[trg_MYDDLEVENT]
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @Data XML;
SET @Data = EventData();
INSERT INTO [dbo].[tbl_EVENTTRACKER]
(EVENTTYPE, SPID, LOGINNAME, TSQLCODE, SERVERNAME, DBNAME)
SELECT
@Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(50)') ASEVENTTYPE,
@Data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(50)') ASSPID,
@Data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)') ASLOGINNAME,
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') ASTSQLCODE,
@Data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(50)') ASSERVERNAME,
@Data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(50)') ASDBNAME
GO
Now make a DDL statement
ALTER TABLE [dbo].[tbl_EVENTTRACKER]
ADD newColumn SMALLDATETIME NULL
And Observe the Event Log by
SELECT * FROM [dbo].[tbl_EVENTTRACKER];
