I'm having a difficult time trying to do something I thought would have been an obvious thing for DDL triggers to do.

Cut to the chase question: Is there an event type in the EventData() function that catches when a trigger is disabled (I'm a little rusty on the comprehensive list of DDL statements, so I'm not even sure if disable trigger is considered a DDL statement).

I've tried DISABLE_TRIGGER in the CREATE DDL trigger statement and the parser barks that this is not a valid statement.

If this is not an event that can be caught by DDL triggers, either because disable trigger is not a valid DDL statement, or because the developer forgot to include it, I would think that disabling a trigger is AS SIGNIFICANT an event as DROP TRIGGER or ALTER TRIGGER.

My sense of urgency here is last week some idiot turned a host of triggers off in our ERP system, causing a cascade of posting problems on dozens of orders before we caught the root cause.  Of course now all the members of sysadmin cannot fathom why anyone would turn them off ;-)

Anyone have **ANY** suggestions on how to capture information when a trigger is disabled?

Thanks in advance.

I will answer part of my own post ;-) Nope, DISABLE TRIGGER IS NOT an event that can be captured with DDL triggers.

However, I see a possibility in that the catalog view, sys.triggers has a field 'is_disabled' in it.  So, if I can just figure out how to put a DML trigger on it, or on the appropriate table it gets this information from, I'll be able to do what I want to do.

Of course SQL Server 2005 doesn't allow you (at least through studio) to script out how sys.triggers was created, so this is where I am now stuck ;-(

OOh! interesting problem. How would you fire an alert when a trigger is disabled.
Here is the code to list the triggers that are disabled (take out the last condition if you want all disabled triggers in the database listed)....

SELECT o.name
  FROM sysobjects o
 WHERE xtype = 'TR'
   AND OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') <> 0
   AND object_name(parent_obj) = 'MyTable';

...but in order to fire an alert, you'd have to put a regular check on the scheduler which then logs a custom error that you make logged. You'd then be able to define an alert for when it finds a disabled trigger