MySQL 错误 1054 :UNKNOWN COLUMN 'amount' in field list

Question

我想创建一个触发器以便:

1. 如果 AMOUNT 字段 < 100，AMOUNT 字段会自动插入 100。
2. 如果 AMOUNT > 100，则应将输入的值插入 AMOUNT 字段。

表:

CREATE TABLE DB_triggers.acc(
    C_ID INT(3) UNIQUE NOT NULL AUTO_INCREMENT ,
    customer_name CHAR(30),amount FLOAT(10,2) UNSIGNED, 
    date_created DATE,PRIMARY KEY(C_ID,customer_name)
) AUTO_INCREMENT=100;   

触发器:

DELIMITER $$ 
    CREATE TRIGGER trig_acc
    BEFORE INSERT ON acc

    FOR EACH ROW 

    BEGIN
    IF amount <100 THEN

    INSERT INTO acc VALUES(new.c_id,new.customer_name,100,new.now());
    ELSE
    INSERT INTO acc VALUES(new.c_id,new.customer_name,new.amount,new.now());

    END IF;

END $$
DELIMITER ;

但是，当我插入一个值时:

INSERT INTO 
    acc(c_id,customer_name,amount,date_created) 
VALUES
    (DEFAULT,'ABC',1000,NOW()); 

给出错误 1054

Answer 1

将 IF amount <100 THEN 更改为 IF new.amount <100 THEN 然后它将起作用。

试试这个:

DELIMITER $$ 
 CREATE TRIGGER trig_acc
 BEFORE INSERT ON acc
 FOR EACH ROW 

BEGIN
IF new.amount <100 THEN

INSERT INTO acc VALUES(new.c_id,new.customer_name,100,new.now());
ELSE
     INSERT INTO acc VALUES(new.c_id,new.customer_name,new.amount,new.now());

END IF;

END $$
DELIMITER ;