Creating a trigger that fires when any of the specified columns are changed

Question

I'm trying to get my trigger to fire when any of the columns listed is changed. I would like specific columns on the bb_prodchg_audit table to be updated when any of the listed columns on the bb_product table are changed. My code compiled before I added the column names to the INSERT INTO lines (though it didn't actually cause the trigger to fire when any of the columns were altered) but once I added column names, I started getting the error "Error(5,18): PLS-00201: identifier 'PRODUCTNAME' must be declared", "Error(9,18): PLS-00201: identifier 'PRICE' must be declared" and so on.

I'm new and not great with PL/SQL and I'm having trouble figuring out how to declare the identifiers, or if this is even the correct approach to the situation.

CREATE OR REPLACE TRIGGER BB_PRODCHG_AUDIT 
AFTER UPDATE OF productname, price, salestart, saleend, saleprice ON bb_product
FOR EACH ROW

BEGIN
    IF (UPDATING(productname)) THEN
        INSERT INTO bb_prodchg_audit (userid, chg_date, name_old, name_new)
        VALUES (USER, SYSDATE, :OLD.productname, :NEW.productname);
    END IF;
    IF (UPDATING(price)) THEN
        INSERT INTO bb_prodchg_audit (userid, chg_date, price_old, price_new)
            VALUES(USER, SYSDATE, :OLD.price, :NEW.price);
    END IF;
    IF (UPDATING(salestart)) THEN
        INSERT INTO bb_prodchg_audit (userid, chg_date, start_old, start_new)
            VALUES(USER, SYSDATE, :OLD.salestart, :NEW.salestart);
    END IF;
    IF (UPDATING(saleend)) THEN
        INSERT INTO bb_prodchg_audit (userid, chg_date, end_old, end_new)
            VALUES(USER, SYSDATE, :OLD.saleend, :NEW.saleend);
    END IF;
    IF (UPDATING(saleprice)) THEN
        INSERT INTO bb_prodchg_audit (userid, chg_date, sale_old, sale_new)
            VALUES(USER, SYSDATE, :OLD.saleprice, :NEW.saleprice);
    END IF;
END;

Edit for clarity: My goal is to add userID, sysdate, and old & new values to the bb_prodchg_audit table with each change from the bb_product table.

Answer 1

In your UPDATING predicates the column name must be specified as a string. So use e.g.

IF UPDATING('SALEPRICE')...

rather than

IF UPDATING(SALEPRICE)...

Keep in mind that column names are case-sensitive and default to UPPERCASE in Oracle.

Best of luck.