MySQL Triggers not working from PHP page but work for manual DB updates via phpMyAdmin [closed]

Question

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.

Answer 1

Type SHOW CREATE TRIGGER trigger_name into the query window. This will give you the SQL code that creates trigger.

You can then add it to your question so we can help you with it.

Also, when modifying your trigger, instead of using GUI, use the query window. Just put DROP TRIGGER trigger_name in the top of your trigger script.