MSSQL: Disable triggers for one INSERT

Question

This question is very similar to SQL Server 2005: T-SQL to temporarily disable a trigger

However I do not want to disable all triggers and not even for a batch of commands, but just for one single INSERT.

I have to deal with a shop system where the original author put some application logic into a trigger (bad idea!). That application logic works fine as long as you don't try to insert data in another way than the original "administration frontend". My job is to write an "import from staging system" tool, so I have all data ready. When I try to insert it, the trigger overwrites the existing Product Code (not the IDENTITY numeric ID!) with a generated one. To generate the Code it uses the autogenerated ID of an insert to another table, so that I can't even work with the @@IDENTITY to find my just inserted column and UPDATE the inserted row with the actual Product Code.

Any way that I can go to avoid extremly awkward code (INSERT some random characters into the product name and then try to find the row with the random characters to update it).

So: Is there a way to disable triggers (even just one) for just one INSERT?

Answer 1

You may find this helpful:

Disabling a Trigger for a Specific SQL Statement or Session

But there is another problem that you may face as well. If I understand the situation you are in correctly, your system by default inserts product code automatically(by generating the value). Now you need to insert a product that was created by some staging system, and for that product its product code was created by the staging system and you want to insert it to the live system manually.

If you really have to do it you need to make sure that the codes generated by you live application in the future are not going to conflict with the code that you inserted manually - I assume they musty be unique.

Other approach is to allow the system to generate the new code and overwrite any corresponding data if needed.