Systems Engineering and RDBMS

EVENTDATA() function in SQL Server 2005

Posted by decipherinfosys on October 9, 2007

Normally the EVENTDATA() function is called when an event notification is fired. It returns information about the server or database events. In Transact-SQL, it has limited but very important usage. As far as Transact-SQL goes, it can be used only in DDL triggers or logon triggers which was introduced in SP2 of SQL Server 2005.

Two things to keep in mind when using EVENTDATA() function are:

• It will return values only when it is directly referenced in DDL or Logon trigger. If it is used outside of the trigger scope, it returns null. This holds true for any routine called from trigger too.
• When transaction that calls EVENTDATA() gets committed or rolled back, data returned by EVENTDATA() becomes invalid.

EVENTDATA() returns an XML value. Most of the time when called from DDL trigger or Logon trigger, XML schema returned by function contains following element values along with other element values which gets changed based on the event type.

• SPID
• EventType
• Time of the event
• ServerName

Let us create a simple DDL trigger at database level which prevents creation of a stored procedure. Connect to the appropriate database using SQL Server management studio.

CREATE TRIGGER TEST_TRIG
ON DATABASE
FOR CREATE_PROCEDURE
AS

SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(100)’) as Type,
EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]’,’nvarchar(100)’) as SPID,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(100)’) as Object_Name,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]’,’nvarchar(100)’) as Object_Type

RAISERROR (‘User cannot create new procedure in the database.’, 16, 1)
ROLLBACK

GO

Let us try to create a stored procedure now. Issue following command and see the error and information returned by EVENTDATA() function.

CREATE PROCEDURE TEST_PROC
AS
BEGIN
SELECT GETDATE()
END
GO

Here is the error returned by DDL trigger. It also returns information from EVENTDATA() function.

Type SPID Object_Name Object_Type
——————- —— ————- ———–
CREATE_PROCEDURE 52 TEST_PROC PROCEDURE

(1 row(s) affected)

Msg 50000, Level 16, State 1, Procedure TEST_TRIG, Line 12
User cannot create new procedure in the database.
Msg 3609, Level 16, State 2, Procedure TEST_PROC, Line 4
The transaction ended in the trigger. The batch has been aborted.

Now drop and re-create the trigger. This time we will call EVENTDATA() function, once we rollback the transaction. Change the trigger as shown below. Look at the syntax of dropping the DDL trigger. We need to specify whther it is on the database or on the server. If you omit the ‘ON’ clause, you will get the error that either trigger does not exist or you do not have any permission.

DROP TRIGGER TEST_TRIG ON DATABASE
GO

CREATE TRIGGER TEST_TRIG
ON DATABASE
FOR CREATE_PROCEDURE
AS

RAISERROR (‘User cannot create new procedure in the database.’, 16, 1)
ROLLBACK

SELECT EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’,’nvarchar(100)’) as Type,
EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]’,’nvarchar(100)’) as SPID,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’,’nvarchar(100)’) as Object_Name,
EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectType)[1]’,’nvarchar(100)’) as Object_Type

GO

Now try to create stored procedure again by issuing statement shown above. Following is the error text.

Msg 50000, Level 16, State 1, Procedure TEST_TRIG, Line 7
User cannot create new procedure in the database.

Type SPID Object_Name Object_Type
——————- —— ————- ———–
NULL NULL NULL NULL

(1 row(s) affected)

Msg 3609, Level 16, State 2, Procedure TEST_PROC, Line 4
The transaction ended in the trigger. The batch has been aborted.

Results shown above clearly indicates that it is important to use EVENTDATA() function before committing or rolling back the transaction. Once the transaction is rolled back,  the values returned by the EVENTDATA() function became invalid and hence it returns null.

3 Responses to “EVENTDATA() function in SQL Server 2005”

  1. […] instance, then you need to make sure that those are moved as well to the other instance. Another link for […]

  2. […] Service Pack 2.  We had also covered the usage of the EVENTDATA() function in another blog post here.  One of the questions that a reader had asked recently was to have the capability of preventing […]

  3. […] here.   As you know, in order to capture the XML data about LOGON events, we will make use of the EVENTDATA() function.  And as per BOL, the LOGON event returns this event data schema – and it has the […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: