Nested Trigger problem

I am working on a solution where sql triggers are called in a nested fashion, I will explain the problem later on let me first introduce you the tables first.

1. BillDetails
2. SubProductAtLocation

Yes, this is an erp designed for a retail store, Bill details table consist of rows bill Items

billDetails

1. RowID
2. BillID
3. SubProductID
4. MRP
5. quantity
6. Tax
7. Discount

subproductLocation

1. RowID
2. SubproductID
3. LocationID
4. Quantity开发者_如何转开发
5. ProductID

**now** There is a trigger which runs after BillDetails Insertion, whose function is to deduct quantity from SubProductAtLocation, further more there are triggers after insert, update in subproductAtLocation, which writes Auditing log.

**Problem** I would not want subproductAtLocation triggers to run if its called due to update done by billdetails trigger, usually I can disable triggers temporarily or can sp_configure 'nested triggers' n but can not do this as sql server is shared and doing this is nor feasible neither permitted.

**Possible Solution**

If I can store a flag value some where which is check by subproductatLocation Triggers and can rollback transaction if flag is set. This flag can be cleared in last line of BillDetails Trigger.

Kindly pardon my poor knowledge Thanks

---

if you put the 'flag' in the second table, then set that to a 'special' value only during the triggered insert, then the second trigger would just have an if check to not write the log if that value was set...