What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

Question

My company logs DDL on one database using a Database Trigger, looking for DDL including:

• ALTER_TABLE
• CREATE_TRIGGER
• ALTER_TRIGGER
• DROP_TRIGGER

When I disable or enable triggers using ALTER TABLE x DISABLE TRIGGER tiud_x, this change is logged with the Database Trigger.

When I disable or enable triggers using DISABLE TRIGGER tiud_x ON x, this change is not logged.

To me, DISABLE TRIGGER and ENABLE TRIGGER look like "one weird trick" to avoid firing a database trigger, since they seem to be functionally equivalent. What's the difference between ALTER TABLE DISABLE TRIGGER and DISABLE TRIGGER?

Answer 1

Sadly:

1. Disabling a trigger is not altering the trigger, just like turning off a light is not altering the lightbulb. However it is funny that it is not captured by ALTER_TRIGGER but something still manages to update modify_date when it happens. ¯\-(ツ)-/¯

2. Your DDL trigger catches the ALTER TABLE variation because that is explicitly altering the table - it says so in the command. While you could argue that disabling the trigger is altering the table regardless of the syntax you use (and I would agree with you, especially since the net result is the same), the difference is that this is currently a gap in what DDL triggers capture, and there is no DISABLE_TRIGGER event (you can vote for them to fix it in this feedback item, but that has been sitting there completely ignored for over a decade). Here is a related, understandably unanswered question on Stack Overflow.
   
   Similarly, a long time ago I remember the ALTER_ events didn't capture RENAME, and this was added as an event later.

You'll have to catch DISABLE TRIGGER in other ways, like audit or Extended Events (I talked about this here):

• When was a table trigger disabled and re-enabled?

In which case, you may as well capture the ALTER TABLE variation the same way so they're both logged in the same place.

A really dumb idea would be to poll sys.triggers every n seconds/minutes/hours and log whenever the state of is_disabled changes. This won't let you see who did it but at least you'll know that it happened and, along with other auditing, you may be able to narrow it down.