1

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.

2
  • The table characteristics follow his info must be provided as complete CREATE TABLE scripts.
    – Akina
    Apr 6, 2023 at 16:44
  • new.customer.CUST_BALANCE - what is this??? NEW means "current row of current (invoice) table".
    – Akina
    Apr 6, 2023 at 16:47

1 Answer 1

0

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

1
  • Thank you. This eliminates the errors, however the trigger does not work as expected. The CUST_BALANCE field did not update after inserting new values in the invoice table. Apr 6, 2023 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.