CODEWITHSUNDEEP
sqlsql-serverdatabase-trigger
Jaimie Knox
Jaimie Knox

Reputation: 166

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?

Upvotes: 2

Views: 329

Answers (2)

Terry Carmen
Terry Carmen

Reputation: 3886

You're making this unnecessarily difficult and error prone.

If each data table has a separate log table and it's own trigger, you can ignore the entire issue and just let them work as they're supposed to.

If you want to view the log info together, you can use a JOIN on the two tables when you display the data.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

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.

Upvotes: 1

Related Questions