ORA-01407: cannot update to NULL Trying to get actual number

Question

I have a trigger that statement that compares to date values and subtracts them. Depending on which number is greater it will multiply different numbers. This should store an actual value but currently it is trying to store a null value, which I do not want.

CREATE OR REPLACE TRIGGER BALANCE_FEE
BEFORE INSERT OR UPDATE OR DELETE ON CHARTERS
FOR EACH ROW 
DECLARE
FEE NUMBER;
ACL_DATE DATE;
EXP_DATE DATE;
GRP_ID VARCHAR2(20);

BEGIN

ACL_DATE := :NEW.ACL_RETURN_DATE;
EXP_DATE := :NEW.EXP_RETURN_DATE;
GRP_ID := :NEW.GRP_ID;


IF ACL_DATE > EXP_DATE
THEN
FEE := ((ACL_DATE - EXP_DATE) * 75);

IF ACL_DATE < EXP_DATE
THEN
FEE := ((ACL_DATE - EXP_DATE)* -20);

END IF;
END IF;
UPDATE CUSTOMER
     SET CUSTOMER.BALANCE = CUSTOMER.BALANCE + FEE
     WHERE CUSTOMER.GRP_ID = GRP_ID;
END;
/
SHOW ERROR;

Tables used in Trigger.

CREATE TABLE CUSTOMER (
    CUS_FNAME VARCHAR(20) NOT NULL,
    CUS_LNAME VARCHAR(20) NOT NULL,
    GENDER VARCHAR(20) NOT NULL,
    PHONENUM NUMBER(10) NOT NULL,
    CITY VARCHAR(20) NOT NULL,
    PARTY_COUNT INT NOT NULL,
    GRP_ID VARCHAR(20) NOT NULL PRIMARY KEY,
    CAN_RENT VARCHAR(20),
    BALANCE NUMBER NOT NULL);

CREATE TABLE CHARTERS (
    CHARTER_ID VARCHAR(20) NOT NULL,
    BOAT_ID VARCHAR(20) NOT NULL REFERENCES BOAT(BOAT_ID),
    GRP_ID VARCHAR(20) NOT NULL REFERENCES CUSTOMER(GRP_ID),
    EXP_RETURN_DATE DATE NOT NULL,
    ACL_RETURN_DATE DATE NOT NULL);

I am getting this error

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T002','B002',TO_DATE ('2019/03/10', 'yyyy/mm/dd'),TO_DATE ('2019/03/08', 'yyyy/mm/dd'),'G001')
Error report -
ORA-01407: cannot update ("ADMIN_BF"."CUSTOMER"."BALANCE") to NULL
ORA-06512: at "ADMIN_BF.BALANCE_FEE", line 24
ORA-04088: error during execution of trigger 'ADMIN_BF.BALANCE_FEE'

When running these insert statements I get the error

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T002','B002',TO_DATE ('2019/03/10', 'yyyy/mm/dd'),TO_DATE ('2019/03/08', 'yyyy/mm/dd'),'G001');

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T001','B001',TO_DATE ('2019/01/20', 'yyyy/mm/dd'),TO_DATE ('2019/01/20', 'yyyy/mm/dd'),'G002');

When I run this statement I get no error

INSERT INTO CHARTERS (CHARTER_ID,BOAT_ID,EXP_RETURN_DATE,ACL_RETURN_DATE,GRP_ID) VALUES ('T003','B003',TO_DATE ('2019/05/05', 'yyyy/mm/dd'),TO_DATE ('2019/05/07', 'yyyy/mm/dd'),'G003');

Answer 1

In your trigger, You are missing the equal condition.

You have IF ACL_DATE > EXP_DATE and IF ACL_DATE < EXP_DATE in your trigger code but Fee will be null if ACL_DATE = EXP_DATE as it will not satisfy any IF condition.

so FEE will be null as you have not even initialized it and

CUSTOMER.BALANCE + FEE(which is null) --> results in null

and CUSTOMER.BALANCE has not null constraint which is violating.

The best solution is to initialize the FEE variable with some initial value (0).

...
...
FOR EACH ROW 
DECLARE
FEE NUMBER := 0; -- this
ACL_DATE DATE;
EXP_DATE DATE;
...
...