Whilst working on a conflict between
triggers in an SQL 2000 database, I discovered that Enterprise Manager and
Query Analyzer can yield different SQL statements for the same trigger. Allow
me to elaborate.
I have an UPDATE trigger that creates an audit log of changes. One section of my Access program has a function that requires that the trigger be disabled, apply some updates, then re-enable the trigger. There was a bug in that function - the trigger was not being reenabled.
The users reported that the audit trigger stopped working, which was due to my bug.
When viewing the statement in EM, everything looked as it should. However, when using QA to edit the trigger, SQL Server detected that the trigger was currently disabled, and therefore automatically added two lines to the end of the trigger SQL:
alter table person disable trigger trgAuditLog
I scoured the remaining SQL statements, looking for the code that was altering the trigger, when in fact it was SQL Server that added those two lines to the code.