Jaimie Knox Jaimie Knox - 4 months ago 8
SQL Question

How do you prevent two triggers on separate tables from both firing at the same time?

I have one table called ClientInfo that stores a client's personal information and another table called EmergencyContactInfo that stores their emergency contact information. Right now I have an update trigger on my ClientInfo table that inserts a single record into a third table called ClientLog if any of their personal information changes. I would also like to create a ClientLog record if the client's emergency contact information changes.

The problem is that the user can change both a client's personal information and their emergency contact information with a single save from my webpage. If I put an update trigger on my EmergencyContact table, then both my triggers will fire and the ClientLog table will insert two new records when I only want one.

From what I understand there is no such thing as an update trigger that spans across multiple tables. What is an alternative approach I could take that would insert a single record when both the client's personal information AND their emergency contact information changes?

Answer

Your understanding is correct that a trigger cannot span multiple tables.

There is also no such thing as preventing a trigger from firing.

What you CAN do is include logic in your trigger that prevents it from doing anything if certain conditions are true.

Now I don't know what you're writing to your log table, and what logic you would want to enforce, but an example would be, when the trigger fires, if there is already a log entry (row in the Log Table) for the same Client in the past x hours, then simply don't insert a new row in the log table.

You can even handle multiple row insert/updates with an OUTER JOIN to the log table, or a WHERE NOT EXISTS() clause.

But basically what I'm saying is, let both triggers fire, and in both triggers, check to see if some condition is true/false before writing to the Log Table. Otherwise, do nothing.

Comments