I have two tables in mysql db, one is account master table and the another one is account transaction table. On insert/update/delete on the transaction table I have to update the available balance and the last transaction date in the account master table ( the account master table contains more than 1 account). Is it possible with a trigger?
I have tried with the following Trigger. But trigger is not getting executed, getting syntax error(MSG 1064 LINE 30 MY SQL DB ERROR).
Please help to resolve if this can be handled through a trigger.
DELIMITER $$
CREATE TRIGGER wlt_bal_upd_insert AFTER INSERT ON wallet_txns
FOR EACH ROW
BEGIN
UPDATE wallet_accounts
SET wlt_bal_available = select sum(IF(wlt_txn_type = 'Expense', -wlt_txn_amount, wlt_txn_amount))from wallet_txns where wlt_name = new.wlt_name,wlt_last_txn_date = select MAX(wlt_txn_date)from wallet_txns where wlt_name = NEW.wlt_name
WHERE wlt_holder_id = NEW.wlt_holder_id
and wlt_name = new.wlt_name;
END $$
DELIMITER ;
解决方案
I just forgot to put the brackets (). Its working now. Here is the modified code.
DELIMITER $$
CREATE TRIGGER wlt_bal_upd_insert AFTER INSERT ON wallet_txns
FOR EACH ROW
BEGIN
UPDATE wallet_accounts
SET wlt_bal_available = (select sum(IF(wlt_txn_type = 'Expense', -wlt_txn_amount, wlt_txn_amount))from wallet_txns where wlt_name = new.wlt_name),wlt_last_txn_date = (select MAX(wlt_txn_date)from wallet_txns where wlt_name = NEW.wlt_name)
WHERE wlt_holder_id = NEW.wlt_holder_id
and wlt_name = new.wlt_name;
END $$
DELIMITER ;