CODEWITHSUNDEEP
sqlsql-server-2008triggers
franglais
franglais

Reputation: 938

Can you disable a trigger within a trigger? SQL Server 2008

Can you disable and re-enable triggers within a trigger?

For example, I have a piece of information in a front-end application that can be put in one of two places. If someone puts it in place A, I want to copy it to place B and vice versa.

So, my idea is to put two triggers on the different tables 1) When it's put in A it puts it in B. 2) When it's put in B it puts it in A

This would create a neverending loop (I assume?) so can you disable Trigger 1 while Trigger 2 runs and enable it at the end?

Upvotes: 0

Views: 3822

Answers (3)

Pondlife
Pondlife

Reputation: 16260

Although I agree with Damien's comment that duplicating data in two tables in the same database is generally undesirable, if you really have a good reason for doing this then you could use INSTEAD OF triggers instead of AFTER triggers (which I assume you're looking at now). That way you can 'replace' a single INSERT with two separate INSERTs that you code and control yourself.

Upvotes: 1

podiluska
podiluska

Reputation: 51514

Or you could check whether the information had changed in the trigger before doing the update, which would end the loop?

Upvotes: 0

ericosg
ericosg

Reputation: 4965

Yes you can:

ALTER TABLE tablename DISABLE TRIGGER triggername
ALTER TABLE tablename ENABLE TRIGGER triggername

Although, for your solution, I'd create a trigger that runs the same on each side, and is clever enough not to mess itself up.

i.e. Make the trigger intelligently distinguish between a user/client INSERT and a trigger 'MOVE'.

Upvotes: 0

Related Questions