"Schema changed after the target table was created. Rerun the Select Into query.":

Question

I have some code that disables a trigger on a table, runs an update, and reenables the trigger. It doesn't drop the source table or update the schema.

This would all be fine, but at the same time there are separate queries running that use that table as the source for a SELECT INTO. For some reason, when the two queries are running at the same time, I get this error:

Schema changed after the target table was created. Rerun the Select Into query.

(I'm selecting into a temp table.)

So, my first question:

Does calling "DISABLE TRIGGER" or "ENABLE TRIGGER" change the schema of a table in TSQL?

And my second question:

Why would the second query report a schema change, when the destination is a temp table?

Answer 1

apparently calling "DISABLE TRIGGER" or "ENABLE TRIGGER" does change the schema of a table. there is probably a catch all bit flag in a system table that tracks this change and is causing you grief.

possibly you could "tell" the trigger to "not run" in a different way. create a table and insert a row, within the trigger check existance of a row in that table, if it exists, return 0, otherwise do the processing. now just insert/delete this row as necessary...

Answer 2

You do not ever want to disable triggers on a production system that is not in single user mode or you may have serious data integrity issues. It disables the tirgger fo all users not just you. I think you need to find a differnt way to handle your problem if you want your daqtabase to operate properly.