MySQL 1054 Error After Creating Trigger to Update on Insert

Question

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.

Answer 1

Try this

DELIMITER $$
CREATE TRIGGER trg_updatecustbalance AFTER INSERT ON invoice 
FOR EACH ROW 
BEGIN 
    UPDATE customer
    SET customer.CUST_BALANCE=customer.CUST_BALANCE+new.INV_AMOUNT
    WHERE customer.CUST_NUM=new.CUST_NUM;
END;
DELIMITER $$;

You have to use the new.column_name for invoice, not customer