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
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'
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.