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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

Disable the trigger, insert, commit.

SET IDENTITY_INSERT Test ON
GO

BEGIN TRAN

DISABLE TRIGGER trg_Test ON Test

INSERT INTO Test (MyId, MyField) 
    VALUES (999, 'foo')

ENABLE TRIGGER trg_Test ON Test

COMMIT TRAN

SET IDENTITY_INSERT Test OFF
GO

Solution 5

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.

View more solutions

Comments

[Nishan]

Only one user is used to access the database. So unfortunately: No.

[Nishan]

"which is obviously a very bad thing to do" -> which is why I don't want to do it ;)

[andynormancx]

Glad we're all agreed on that ;)

[Nishan]

Thanks for the hint with SCOPE_IDENTITY. I come from "oldschool" SQL Server 2000 (and older) which didn't have that. :)

[Nishan]

I'll go the way with changing the trigger to monitor a column, though.

[Nishan]

Actually the final goal is to disable editing features on the production system altogether. Otherwise the note about code collisions is a very good one!

[Nick van Esch]

I really liked this one as it was very neat. I however altered it slightly to also check if this column was updated, which ran the trigger for other table updates IF NOT (UPDATE(insertedFromImport) AND EXISTS (SELECT insertedFromImport FROM INSERTED WHERE insertedFromImport IS NOT NULL)) BEGIN

[JohnLBevan]

Could you put a table lock on the affected table first, then disable all triggers on the table, run insert, re-enable the triggers and finally remove the lock - i.e. use the lock to prevent side effects of not having a trigger?

[roaima]

Is DISABLE TRIGGER really a transactional statement? As far as I can tell this will disable triggers for any operation on table Test while the transaction is in operation (i.e. you've a race condition).

[roaima]

SQL Server 2017 - you cannot have DISABLE TRIGGER inside a transaction. (I appreciate it's now 2018 and your post is from 2011, but did this work in an earlier version of SQL Server?)

[roaima]

That offsite link uses CONTEXT_INFO() which solves the issue really neatly indeed