1

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.

  • Please post CREATE TRIGGER statements – Lennart Mar 13 at 15:56
  • Does the DB user that is used for your web pages have the same authority as the user that are you are using for PHPMyAdmin? I've run into the same problem myself since, when developing locally, I used to use a user with full authority. In production the user did not have delete authority which was the source of the problem. – Dave Mar 13 at 16:04
  • @Lennart I created the triggers in phpMyAdmin, so the only code I entered was in the ‘script’ box as defined above. I can provide a screen shot of the phpMyAdmin trigger description if that will help. – S. Carter Mar 13 at 17:20
  • @Dave I’m not sure about the potential user privilege mismatch. I can check on my web page capabilities by creating a quick delete script. But, my BEFORE/UPDATE trigger that deletes the records from my scratch table does work when it is triggered via PHP code on my site. Only the INSERT INTO trigger is not completing successfully. Thanks! – S. Carter Mar 13 at 17:23
  • No that does not help at all, to avoid guess work you should post code that can be used to reproduce the problem. – Lennart Mar 13 at 17:24
0

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.

  • Updated original post with results from 'SHOW CREATE TRIGGER' query/command. – S. Carter Mar 14 at 12:54
  • Can you also show a couple sample rows for each table? – Granny Aching Mar 14 at 13:08
  • I meant, add to the question – Granny Aching Mar 14 at 14:10
  • Done - thank you. Tried to format it for readability but limited formatting capabilities in the comment window :-). – S. Carter Mar 14 at 14:35
  • Where is vw_next_wk_house_assignments.rent_amt coming from? – Granny Aching Mar 14 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.