TransWikia.com

How to create a trigger that updates a table that may be joined to the table being queried

Database Administrators Asked by Jonathan Hurdman on October 10, 2020

I am having trouble crafting a trigger. I have a database that has contacts in it, and the contacts table joins to a second table, contact_addresses. They are big tables, but here is the basic premise:

Contacts table has fields contact_id, first_name, last_name, primary_email, last_updated
Contacts_addresses has contact_id, address1, address2, city, province, postal_code

The problem is that when address1 in contacts_address gets updated, I need last_updated in contacts to be set to current_timestamp. I have a trigger set up to run BEFORE UPDATE on contacts_addresses, as below:

BEGIN
  UPDATE `contacts` 
   SET `contacts`.`last_updated` = CURRENT_TIMESTAMP 
   WHERE `contacts`.`contact_id` = NEW.`contact_id`;
END

The problem is, sometimes I need to update fields in both tables, for example, I get a new address and email address. If I update the two tables using a join, it fails, because the updated table is referenced in the query. Is there some way I can use triggers that will still allow me to update using a join on these two tables?

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP