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)','varchar(max)') AS 'Schema', EVENTDATA().value ('(/EVENT_INSTANCE/ObjectName)','varchar(max)') AS 'Object', EVENTDATA().value ('(/EVENT_INSTANCE/EventType)','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)','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!!