ALTER TRIGGER behavior change in SQL Server 2014

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  June 2, 2015 at 8:05 am

  #297427

  I was perusing old QotDs, and came across the following quite interesting one: http://www.sqlservercentral.com/Forums/Topic1492971-3440-1.aspx.

  It pointed out that after disabling a trigger, running an ALTER TRIGGER on the disabled trigger to change its definition also re-enabled it. As discussed in the conversation, that behavior did not seem to be documented, despite promises that it soon would be.

  Well, wait no more! The behavior is now documented at https://msdn.microsoft.com/en-us/library/ms189748.aspx, but not in the 2012 or previous versions of that page. There's just one catch. That behavior was apparently removed in SQL Server 2014.

  I've tested on a few 2014 instances, and none of them re-enable a disabled trigger when its definition is changed by ALTER TRIGGER.

  So, Microsoft finally updated the documentation, but only after it no longer applied. :crazy: C'est la vie.

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  June 2, 2015 at 10:09 am

  #1802354

  Thanks for the heads up Jacob

  😎