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

博主分享了一个关于如何使用MySQL触发器在交易表插入/更新/删除操作时自动更新账户主表中可用余额和最后交易日期的问题。起初遇到了语法错误,但通过修改代码解决了。文章详细描述了触发器的创建过程和修正后的代码。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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 ;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值