MySQL 触发器需要先前事件中的字段
我还有另一个触发器问题，在解释之前，我将向您展示一段代码：
DELIMITER $$
CREATE PROCEDURE transferFunds ( receiver INT, sender INT, amount FLOAT )
BEGIN
DECLARE senderBalance FLOAT;
DECLARE receiverBalance FLOAT;
SELECT balance INTO senderBalance
FROM accounts
WHERE accountNumber = sender;
SELECT balance INTO receiverBalance
FROM accounts
WHERE accountNumber = receiver;
SET autocommit = 0;
UPDATE accounts
SET balance = senderBalance - amount
WHERE accountNumber = sender;
UPDATE accounts
SET balance = receiverBalance + amount
WHERE accountNumber = receiver;
IF senderBalance < amount THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
CREATE TRIGGER transferTrigger AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
-- Insert the Receiver and Sender and Amount to transfer_log table
END$$
我想创建一个触发器来记录刚刚发生的传输，但我不能，因为触发器在每行事件中工作。如果我想在transfer_log表中记录转账事件并插入To和From帐号以及转账金额，我该怎么做？
I have another trigger question, before i explain, i'm gonna show you a code:
DELIMITER $
CREATE PROCEDURE transferFunds ( receiver INT, sender INT, amount FLOAT )
BEGIN
DECLARE senderBalance FLOAT;
DECLARE receiverBalance FLOAT;
SELECT balance INTO senderBalance
FROM accounts
WHERE accountNumber = sender;
SELECT balance INTO receiverBalance
FROM accounts
WHERE accountNumber = receiver;
SET autocommit = 0;
UPDATE accounts
SET balance = senderBalance - amount
WHERE accountNumber = sender;
UPDATE accounts
SET balance = receiverBalance + amount
WHERE accountNumber = receiver;
IF senderBalance < amount THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$
CREATE TRIGGER transferTrigger AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
-- Insert the Receiver and Sender and Amount to transfer_log table
END$
I wanted to create a Trigger that would record the transfer that just happened, but I can't because Trigger works in a per row Event. How would I do that if I want to record the transfer event in a transfer_log table and insert the To and From account numbers and the amount that was transferred?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如您在问题中所述，触发器是按行工作的。
您应该直接在存储过程中进行日志记录。但是，您可以使用解决方法，尽管我认为它不安全 - 特别是在处理金钱交易时（如OP所示）。
在你的存储过程中：
在你的触发器中：
As you stated in your question, triggers work on a per row basis.
You should do the logging in your stored procedure directly. However you could use a workaround, though i do not think its safe - specially when dealing with money transactions (as shown in the OP).
Inside your stored proc:
And inside your trigger: