Why does my trigger keep asking me for bind variables in Oracle SQL

June 8, 2023

I have this trigger where I try to change SALESTOTALCOST AND PRODUCTACTUALCOST whenever a value is inserted in FACTPRODUCTSALES using the PRODUCTACTUALCOST AND PRODUCESALESCOST from DIMPRODUCT table.

Here’s the code:

CREATE TRIGGER FACTPRODUCTSALES_INSERT 
AFTER INSERT ON FACTPRODUCTSALES
FOR EACH ROW
DECLARE
    ACTUALCOST NUMBER;
    SALESCOST  NUMBER;
BEGIN
    SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
    
    :NEW.SALESTOTALCOST = SALESCOST * :NEW.QUANTITY;
    :NEW.PRODUCTACTUALCOST = ACTUALCOST * :NEW.QUANTITY;
END;

But it keeps asking me for bind variables; what is this? Why’s that? And what should I do about it?

I’ve already tried doing SET DEFINE OFF base on the answers in this and this and it didn’t work.

>Solution :

Presuming that tables exist and contain columns involved, then:

trigger must be before (line #1) (if you want to modify :new values)
use := instead of = (lines #10, 11)

SQL> CREATE OR REPLACE TRIGGER FACTPRODUCTSALES_INSERT before INSERT ON FACTPRODUCTSALES
  2  FOR EACH ROW
  3  DECLARE
  4      ACTUALCOST NUMBER;
  5      SALESCOST  NUMBER;
  6  BEGIN
  7      SELECT PRODUCTACTUALCOST INTO ACTUALCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  8      SELECT PRODUCTSALESCOST INTO SALESCOST FROM DIMPRODUCT WHERE PRODUCTKEY = :NEW.PRODUCTID;
  9
 10      :NEW.SALESTOTALCOST := SALESCOST * :NEW.QUANTITY;
 11      :NEW.PRODUCTACTUALCOST := ACTUALCOST * :NEW.QUANTITY;
 12  END;
 13  /

Trigger created.

SQL>