mysql 触发器丢失,如何修复这个MySQL触发器？

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;

This is the error I get from myPHPAdmin.

解决方案

Updated Answer

This is what I think you want, assuming that Account to Transaction is a 1:N relationship keyed on Number/AccountNumber:

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 ;

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.

Original Answer

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