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?
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP