在另一个表中插入记录后，MySQL触发器将更新可用余额

问题描述：

我在mysql db中有两个表，一个是帐户主表，另一个是帐户交易表.在插入/更新/删除交易表上时，我必须更新帐户主数据表中的可用余额和最后一笔交易日期(帐户主数据表包含多个帐户).扳机有可能吗? 我已经尝试使用以下触发器.但是触发器没有执行，得到语法错误(MSG 1064 LINE 30 MY SQL DB ERROR). 请帮助解决此问题是否可以通过触发器解决.

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 ;

在另一个表中插入记录后，MySQL触发器将更新可用余额

相关推荐