Sample Header Ad - 728x90

MySQL 1054 Error After Creating Trigger to Update on Insert

1 vote
1 answer
250 views
I am learning basic SQL and have gotten stuck with triggers. I have a database with two tables, customer and invoice. The table characteristics follow:
CREATE TABLE customer (
	CUST_NUM		VARCHAR(8), 
    CUST_LNAME		VARCHAR(25),
    CUST_FNAME		VARCHAR(25),
    CUST_BALANCE	DECIMAL(9,2),
    PRIMARY KEY (CUST_NUM)
    );
    
CREATE TABLE invoice (
		INV_NUM		VARCHAR(10),
        CUST_NUM	VARCHAR(8)	NOT NULL,
        INV_DATE	DATE,
        INV_AMOUNT	DECIMAL(9,2),
        PRIMARY KEY (INV_NUM),
        FOREIGN KEY (CUST_NUM) REFERENCES customer (CUST_NUM)
        );
I am trying to create a trigger that will update the customer.CUST_BALANCE value after an insert in the invoice table. I am testing the trigger by entering values ('8005', '1001', '2018-04-27', '225.40') into Invoice This script:
DELIMITER $$
CREATE TRIGGER trg_updatecustbalance
AFTER INSERT ON invoice
FOR EACH ROW
BEGIN
	UPDATE customer
	SET customer.CUST_BALANCE=customer.CUST_BALANCE+NEW.invoice.INV_AMOUNT
    WHERE customer.CUST_NUM=NEW.invoice.CUST_NUM;
END; 
$$
DELIMITER ;
Yields Error Code: 1054. Unknown column 'new.invoice.CUST_NUM' The script:
DELIMITER $$
CREATE TRIGGER trg_updatecustbalance
	AFTER INSERT
	ON invoice FOR EACH ROW
BEGIN
	IF invoice.CUST_NUM=customer.CUST_NUM THEN 
    INSERT INTO customer
    VALUES (customer.CUST_BALANCE = customer.CUST_BALANCE + NEW.invoice.INV_AMOUNT);
    END IF;
END$$
DELIMITER ;
Yields Error Code: 1109. Unknown table 'invoice' in field list. I am looking for assistance in resolving this. Thanks! **ETA:** Edited to provide script for the tables and clarify script and errors of previous attempts.
Asked by Kegan Smith (11 rep)
Apr 6, 2023, 03:38 PM
Last activity: Jun 22, 2025, 02:03 PM