Wednesday, August 7, 2013

SQL Server 2005

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