I have the following two Triggers in my MySQL DB that fire BEFORE/UPDATE and AFTER/UPDATE on the house_report_details table. The first deletes all records in my scratch table tmp_house_rpt_details and the second inserts a single record into this scratch table for use in subsequent PHP code:
Trigger 1 (BEFORE/UPDATE on house_report_details):
DELETE FROM tmp_house_report_details
Trigger 2 (AFTER/UPDATE on house_report_details):
CREATE TRIGGER `trg_create_tmp_house_rpt_detail` AFTER UPDATE ON `house_report_details` FOR EACH ROW INSERT INTO tmp_house_report_details (rpt_week_num, client_id, residence_id, curr_step, curr_step_dur, prev_wk_step, prev_wk_step_dur, prev_bal, wkly_rent, curr_bal) SELECT (NEW.rpt_week_num + 1), NEW.client_id, vw_next_wk_house_assignments.residence_id, NEW.curr_step, (NEW.curr_step_dur + 1), NEW.curr_step, NEW.curr_step_dur, NEW.curr_bal, vw_next_wk_house_assignments.rent_amt, (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt) FROM vw_next_wk_house_assignments WHERE vw_next_wk_house_assignments.client_id = NEW.client_id ON DUPLICATE KEY UPDATE residence_id = vw_next_wk_house_assignments.residence_id, curr_step = NEW.curr_step, curr_step_dur = (NEW.curr_step_dur + 1), prev_wk_step = NEW.curr_step, prev_wk_step_dur = NEW.curr_step_dur, prev_bal = NEW.curr_bal, wkly_rent = vw_next_wk_house_assignments.rent_amt, curr_bal = (NEW.curr_bal + vw_next_wk_house_assignments.rent_amt)
Here are sample rows from the 'master' table - house_report_details - that has the two triggers. The temp/scratch table, tmp_house_report_details, that the triggers delete/insert into has the exact same schema as the master:
SELECT * FROM `house_report_details` LIMIT 3 id rpt_week_id rpt_week_num client_id residence_id curr_step curr_step_dur prev_wk_step prev_wk_step_dur num_mtgs sponsor_contact chores_flg prev_bal wkly_rent other_chgs amt_paid curr_bal last_updated_by last_updated_dt 9 10 10 3 1 3 2 3 1 9 5 Y 0.00 -125.00 0.00 125.00 0.00 Elvis Presley Mar-6-2019 03:37pm CST 10 10 10 6 1 3 3 3 2 9 7 Y -150.00 -75.00 0.00 225.00 0.00 Elvis Presley Mar-6-2019 03:22pm CST 11 10 10 9 1 7 1 6 4 9 6 Y 0.00 -100.00 0.00 100.00 0.00 Elvis Presley Mar-13-2019 08:26am CST
The triggers work perfectly in my local dev environment and work on my hosted server environment if I manually update information in the house_report_details table using phpMyAdmin.
The BEFORE/UPDATE trigger to delete the scratch table record also works when the table updates are made from my PHP web pages but the AFTER/UPDATE trigger does not work for some reason and causes my entire table update transaction to fail (as failed trigger execution should).
I cannot find anything to help tell me why a trigger will work when manually invoked (via phpMyAdmin, for example) but not when the same DB update is performed via PHP/MySQL code in my web pages.
My hosting provider is BlueHost and I am running MySQL v5.6.41-84.1 and PHP v7.0.33.
I really want to be able to use the power that triggers provide and hope I can get this resolved quickly so I don't have to convert my triggers into PHP/MySQL code in my PHP pages instead.
This is my first question here, so I hope I have provided enough information to troubleshoot this issue. Happy to provide additional details, if needed.
Thank you, Steve.