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)')    AS EVENTTYPE,

@Data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(50)')         AS SPID,

@Data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')    AS LOGINNAME,

@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') AS TSQLCODE,

@Data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(50)')   AS SERVERNAME,

@Data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(50)') AS DBNAME 

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];

Hope you like it.

Posted by: MR. JOYDEEP DAS