如何修复这个MySQL触发器？ [英] How to fix this MySQL trigger?

问题描述

我尝试让此触发器工作：

I'm trying to get this trigger to work:

CREATE TRIGGER Transaction_insert BEFORE INSERT ON Transaction
FOR EACH ROW WHERE Number = NEW.AccountNumber 
IF Account.CreditBalance + NEW.Amount < Account.CreditLimit THEN
UPDATE Account SET CreditBalance = CreditBalance + NEW.Amount where Number = NEW.AccountNumber;
ELSE
SET NEW.Valid = 0
END IF; 

这是我从myPHPAdmin获得的错误。

This is the error I get from myPHPAdmin.

推荐答案

更新答案

这是我想要的，假设 Account 交易是以数字 / / Account $ ：

DELIMITER //

-- Assumptions:
--   1. Transaction.AccountNumber is F.K. REFERENCES Account(Number)
--   2. Account.Number is UNIQUE
--
CREATE TRIGGER trg_bi_transaction BEFORE INSERT ON Transaction
FOR EACH ROW
BEGIN
  -- Adjust account balance (if permitted)
  --
  UPDATE Account
     SET CreditBalance = CreditBalance + NEW.Amount
   WHERE Number = NEW.AccountNumber
         AND
         (CreditBalance + NEW.Amount) < CreditLimit;

  -- Was the adjustment valid/permitted?
  --
  SET NEW.Valid = (ROW_COUNT() = 1);
END //

DELIMITER ;

触发器将尝试更新正确的帐户 CreditLimit 允许的任何给定事务 Valid 字段将在UPDATE成功时设置为1，如果不成功则设置为0.

That trigger will attempt to UPDATE the proper Account for any given Transaction if the CreditLimit permits. The Valid field will be set to 1 if the UPDATE succeeded, and 0 if it did not.

MySQL触发器不支持触发级WHERE子句。在触发器正文中移动Number / NEW.AccountNumber检查。

MySQL triggers do not support trigger-level WHERE clauses. Move the Number/NEW.AccountNumber check inside the trigger body.

这篇关于如何修复这个MySQL触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！