mysql 触发器流水号,在另一个表中插入记录后，MySQL触发器更新可用余额

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 ;