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

Can you check for SUSER_SNAME() and only run when in context of the administration frontend?

Answer 2

Rather than disabling triggers can you not change the behaviour of the trigger. Add a new nullable column to the table in question called "insertedFromImport".

In the trigger change the code so that the offending bit of the trigger only runs on rows where "insertedFromImport" is null. When you insert your records set "insertedFromImport" to something non-null.

Answer 3

You can disable triggers on a table using:

ALTER TABLE MyTable DISABLE TRIGGER ALL

But that would do it for all sessions, not just your current connection.. which is obviously a very bad thing to do :-)

The best way would be to alter the trigger itself so it makes the decision if it needs to run, whether that be with an "insert type" flag on the table or some other means if you are already storing a type of some sort.

Answer 4

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.

Answer 5

If you insert using BULK INSERT, you can disable triggers just for the insert.

I'm pretty sure bulk insert will require a data file on the file system to import so you can't just use T-SQL.

To use BULK INSERT you need INSERT and ADMINISTRATOR BULK OPERATION permissions. If you disable triggers or constraints, you'll also need ALTER TABLE permission.

If you are using windows authentication, your windows user will need read access from the file. if using Mixed Mode authentication, the SQl Server Service account needs read access from the file.

When importing using BULK IMPORT, triggers are disabled by default.

More information: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Answer 6

I see many things that could create a problem. First change the trigger to consider multiple record imports. That may probably fix your problem. DO not turn off the trigger as it is turned off for everyone not just you. If you must then put the database into single user user mode before you do it and do your task during off hours.

Next, do not under any circumstances ever use @@identity to get the value just inserted! USe scope_identity instead. @@identity will return the wrong value if there are triggers onthe table that also do inserts to other tables with identity fields. If you are using @@identity right now through your system (since we know your system has triggers), your abosolute first priority must be to immediately find and change all instances of @@identity in your code. You can have serious data integrity issues if you do not. This is a "stop all work until this is fixed" kind of problem.

As far as getting the information you just inserted back, consider creating a batchid as part of you insert and then adding a column called batchid (which is nullable so it won't affect other inserts)to the table. Then you can call back what you inserted by batchid.