2

I have a database trigger that logs DDL changes. It has the folowing format

CREATE TRIGGER [Log_DDL_Changes]

ON DATABASE
FOR 
CREATE_TABLE, ALTER_TABLE, DROP_TABLE, 
CREATE_INDEX, ALTER_INDEX, DROP_INDEX, 
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, RENAME

--Fires only for CREATE / ALTER / DROP Table and PROCs
AS 

BEGIN
.
.
.
END

I'd like to add special handling whenever a table, view or index is created or changed.

How can I determine which event triggered the trigger for a TRIGGER that handles multiple events?

AdamL
  • 12,421
  • 5
  • 50
  • 74
Chad
  • 23,658
  • 51
  • 191
  • 321

1 Answers1

1

You use something like this in your trigger:

SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks for the reply. While this may work, I'm thinking that there ias a better way than to parse the SQL command. I feel like I should be able to key off of teh EVENT names that appear in the FOR CLAUSE. If I have to, I'll sooner break up my trigger into separate triggers so I can tell what event got me to execute – Chad Dec 03 '13 at 19:04
  • @ChadD That's why I said "*something like this*". There's a lot of options, but I have changed it to to more closely reflect your comment's preferences. – RBarryYoung Dec 03 '13 at 19:12
  • Ah, I now see your edit. That is exactly what i was looking for. TY! – Chad Dec 03 '13 at 19:27