MySql trigger not works

Question

I have 3 tables

tbl_payments(pay_id,date,amount,description,-------)
tbl_pay_trans(pay_id,trans_id)
tbl_transactions(trans_id,trans_date,trans_amount,trans_description,-----)

'tbl_transaction' has the same data from the 'tbl_payments' along with some other values. To maintain the relationship between the two tables I use 'tbl_pay_trans'. What I want to do is, when an update done on tbl_payments(amount,description) the same changes need to do in tbl_transactions(trans_amount,trans_description). I wrote a trigger to do that, but it dose not update the tbl_transaction table values as it supposed to.

My trigger is

DELIMITER $$
CREATE TRIGGER update_trans 
    AFTER UPDATE on tbl_payments
    FOR EACH ROW
BEGIN
    DECLARE new_amount VARCHAR(50);
    DECLARE new_description TEXT;
    DECLARE new_pay_id,new_trans_id INT;

    SET new_pay_id = OLD.pay_id;
    SET new_amount = OLD.amount;
    SET new_description = OLD.description;
    SELECT trans_id INTO new_trans_id FROM tbl_pay_trans WHERE pay_id = new_pay_id;

    UPDATE tbl_transactions SET 
    trans_amount = new_amount,
    trans_description = new_description
    WHERE trans_id = new_trans_id;
END$$
DELIMITER ;

Please someone help me to figure out what I did wrong.

Your missing a length on your `VARCHAR` it should be e.g. `VARCHAR(50)` — Nick, Aug 09 '18 at 23:52
Thnx @Nick, That was the error. I corrected it. but the trigger doesn't do what it supposed to do. It didn't update the values on tbl_transaction. Dosen't give an error now though. I updated my question for the new problem aroused. — ashen25, Aug 10 '18 at 01:13
I figured that the problem is in this line. SELECT trans_id INTO new_trans_id FROM tbl_pay_trans WHERE pay_id = new_pay_id; — ashen25, Aug 10 '18 at 01:46
You do have a problem with your `new_amount` and `new_description` lines. Your `SELECT` looks fine to me — Nick, Aug 10 '18 at 01:49

score 0 · Accepted Answer · answered Aug 10 '18 at 01:42

It's not updating because you are using OLD on the amount and description columns where you need to use NEW i.e.

SET new_amount = NEW.amount;
SET new_description = NEW.description;

OLD refers to the column value before the update occurred in tbl_payments. See the manual.

MySql trigger not works

1 Answers1