10/14/2011 6:24:39 AM
Piyush Bajaj -
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-
CREATE TRIGGER sample1_ddl
('(/EVENT_INSTANCE/SchemaName)','varchar(max)') AS 'Schema',
('(/EVENT_INSTANCE/ObjectName)','varchar(max)') AS 'Object',
('(/EVENT_INSTANCE/EventType)','varchar(max)') AS 'EventType'
CREATE TABLE NewTable (Column1 int);
The result can be seen as:
We can also use the ROLLBACK command, if there’s an error;
CREATE TRIGGER sample2_ddl
FOR ALTER_PROCEDURE, DROP_PROCEDURE
SET NOCOUNT ON
PRINT 'Either Alter Procedure or Drop Procedure Issued.'
DECLARE @EventData XML = EventData()
RAISERROR ('Procedure cannot be altered or dropped in this database.', 16, 1)
DROP PROC dbo.sp_alterdiagram
DROP TRIGGER sample2_ddl
Well, this was all about EventData () function used with DDL triggers.
If you liked this post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
And also comments on this!!
Piyush Bajaj (Member since: 5/23/2011 11:25:15 AM)
I am very passionate about SQL Server. I also did certification on MCSA - SQL Server 2012, Querying and Administering; MCTS - SQL Server 2008, Database Development; and MCTS - SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.
Please feel free to drop me any question online or offline, i will try to give you the best possible answer from my side.
Right now i am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well i can only say that "if you have an interest and passion, experience might become a very small thing".
View Piyush Bajaj 's profile
Leave a comment