SQL Server EventData Function

Hello folks,

You would have heard about this function while dealing with DDL triggers. If you would have seen my last blog post, it was about “Similarities and Differences between DML and DDL triggers”; I mentioned it while making out the differences between DML and DDL triggers. Well if you want to refer, then follow this link:

As you would also have heard about DML triggers, that they are good about capturing data about the event that caused them to fire, because they have inserted and deleted virtual tables where they automatically stores the data.

Since DDL triggers have to respond to so many different events, therefore they have an EventData() function.

I have made some keynotes about it-

• The main function of EventData () function is to return XML-formatted data about the event.
• EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger.
• EVENTDATA returns null if it is called by other routines, even if those routines are called by a DDL or logon trigger.
• The data returned by EVENTDATA is not valid after a transaction that called EVENTDATA, either implicitly or explicitly, commits or is rolled back.
• Since EventData () returns XML data. So, this data is being sent to the client as Unicode that uses 2 bytes for each character.
• By default, the schema definition for all events is installed in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.
• If you want to extract the schema for any particular event, search the schema for the Complex Type EVENT_INSTANCE_<event_type>.
• The XML Schema also includes the information about the following:
  • The time of the event.
  • The System Process ID (SPID) of the connection when the trigger is executed.
  • The type of event that fired the trigger.
• Therefore, depending on the event type, the schema then includes additional information such as the database in which the event occurred, the object against which the event occurred, and the T-SQL statement of the event. Just follow the below example:

USE TEST
GO
CREATE TRIGGER sample1_ddl
ON DATABASE
FOR CREATE_TABLE
AS
    SELECT EVENTDATA().value
        ('(/EVENT_INSTANCE/SchemaName)[1]','varchar(max)') AS 'Schema',
        EVENTDATA().value
        ('(/EVENT_INSTANCE/ObjectName)[1]','varchar(max)') AS 'Object',
        EVENTDATA().value
        ('(/EVENT_INSTANCE/EventType)[1]','varchar(max)') AS 'EventType'
        ;
GO
CREATE TABLE NewTable (Column1 int);
GO

The result can be seen as:

• With the help of EventData (), you can also be able to generate or raise an error, which is a good practice if you want the user from using the particular DDL statement. This will become more clear if you follows the below example:

We can also use the ROLLBACK command, if there’s an error;

USE TEST
GO
CREATE TRIGGER sample2_ddl 
ON DATABASE
FOR ALTER_PROCEDURE, DROP_PROCEDURE 
AS
    SET NOCOUNT ON
    PRINT 'Either Alter Procedure or Drop Procedure Issued.'
 
    DECLARE @EventData XML = EventData()
     
    SELECT @EventData.value
        ('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
   RAISERROR ('Procedure cannot be altered or dropped in this database.', 16, 1) 
   ROLLBACK
;
GO
DROP PROC dbo.sp_alterdiagram
GO
DROP TRIGGER sample2_ddl 
ON DATABASE;
GO

The result can be seen as:

Well, this was all about EventData () function used with DDL triggers.

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook