Oracle Trigger BEFORE INSERT has No data found

Question

I am trying to make a trigger that will prevent you from inserting a new Bill Item into the "Bill_Item" table if the Bill has been paid. The Bill Table has a Paid_YN and a Posted_YN column within. So depending on which bill you will be adding to, it will deny it if it has been paid or posted. My update and delete sections work within this code, but not the "Inserting" section. Please help! My Apologies for lack of indentation, Could not get the code to copy in properly. please let me know where I am going wrong! is it my logic? I have tried googling and searching here for the error messages, but still cannot figure it out!

TABLES::

CREATE TABLE Bill (
Bill_Number NUMBER (6,0)
CONSTRAINT Bill_pk PRIMARY KEY
CONSTRAINT Bill_Number_NN NOT NULL,
Bill_Date DATE DEFAULT SYSDATE
CONSTRAINT Bill_Date_NN NOT NULL,
Waiter_Number NUMBER (5,0)
CONSTRAINT Bill_Waiter_Number_FK REFERENCES Waiter (Waiter_Number)
CONSTRAINT Bill_Waiter_Number_NN NOT NULL,
Table_Number NUMBER (2,0)
CONSTRAINT Bill_Table_Number_NN NOT NULL,
Customer_Count NUMBER (2,0) DEFAULT 1
CONSTRAINT Bill_Customer_Count_CK CHECK (Customer_Count > 0)
CONSTRAINT Bill_Customer_Count_NL NULL,
Paid_YN CHAR (1) DEFAULT 'N'
CONSTRAINT Bill_Paid_YN_CK CHECK ((Paid_YN = 'Y') OR (Paid_YN = 'N'))
CONSTRAINT Bill_Paid_YN_NN NOT NULL,
Posted_YN CHAR (1) DEFAULT 'N'
CONSTRAINT Bill_Posted_YN_CK CHECK ((Posted_YN = 'Y') OR (Posted_YN = 'N'))
CONSTRAINT Bill_Posted_YN_NN NOT NULL,
Bill_GST NUMBER (6,2) DEFAULT 0
CONSTRAINT Bill_GST_CK CHECK (Bill_GST >= 0)
CONSTRAINT Bill_GST_NN NOT NULL,
Bill_Total NUMBER (8,2) DEFAULT 0
CONSTRAINT Bill_Total_CK CHECK (Bill_Total >= 0)
CONSTRAINT Bill_Total_NN NOT NULL
);

CREATE TABLE Bill_Item (
Bill_Number NUMBER (6,0)
CONSTRAINT BItem_Bill_Number_FK REFERENCES Bill (Bill_Number)
CONSTRAINT BItem_Bill_Number_NN NOT NULL,
Menu_Item_Number NUMBER (5,0)
CONSTRAINT BItem_Menu_Item_Number_FK REFERENCES Menu_Item (Menu_Item_Number)
CONSTRAINT BItem_Menu_Item_Number_NN NOT NULL,
Discount NUMBER (5,2) DEFAULT 0
CONSTRAINT BItem_Discount_CK CHECK ((Discount >= 0 ) AND (Discount <= 100))
CONSTRAINT BItem_Discount_NL NULL,
Quantity_Sold NUMBER (3,0) DEFAULT 1
CONSTRAINT BItem_Quantity_Sold_CK CHECK (Quantity_Sold > 0)
CONSTRAINT BItem_Quantity_Sold_NN NOT NULL,
Selling_Price NUMBER (6,2) DEFAULT 0
CONSTRAINT BItem_Selling_Price_CK CHECK (Selling_Price >= 0)
CONSTRAINT BItem_Selling_Price_NN NOT NULL,
CONSTRAINT Bill_Item_PK PRIMARY KEY (Bill_Number, Menu_Item_Number)
);

Trigger::

CREATE OR REPLACE TRIGGER TR_Q5 
BEFORE 
  UPDATE OR
  INSERT OR
  DELETE ON
  BILL_ITEM
  FOR EACH ROW

DECLARE
 v_Paid_YN CHAR(1);
 v_Posted_YN CHAR(1);

BEGIN
  IF UPDATING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :OLD.Bill_Number;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20001, 'Cannot update the bill once its been paid!');
    END IF;
    IF v_Posted_YN = 'Y' AND
      v_Paid_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20002, 'Cannot update the bill once its been posted!');
    END IF;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'Y' THEN
      RAISE_APPLICATION_ERROR(-20003, 'Cannot update the bill once its been paid and posted!');
    END IF;
  ELSIF INSERTING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :OLD.Bill_Number;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20004, 'Cannot add to the bill once its been paid!');
    END IF;
    IF v_Posted_YN = 'Y' AND
      v_Paid_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20005, 'Cannot add to the bill once its been posted!');
    END IF;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'Y' THEN
      RAISE_APPLICATION_ERROR(-20006, 'Cannot add to the bill once its been paid and posted!');
    END IF;
  ELSIF DELETING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :OLD.Bill_Number;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20007, 'Cannot delete from the bill once its been paid!');
    END IF;
    IF v_Posted_YN = 'Y' AND
      v_Paid_YN = 'N' THEN
      RAISE_APPLICATION_ERROR(-20008, 'Cannot delete from the bill once its been posted!');
    END IF;
    IF v_Paid_YN = 'Y' AND
      v_Posted_YN = 'Y' THEN
      RAISE_APPLICATION_ERROR(-20009, 'Cannot delete from the bill once its been paid and posted!');
    END IF;
END IF;
END TR_Q5;
/
SHOW ERRORS;

AND This insert returns:

Insert Into Bill_Item
    (Bill_Number, Menu_Item_Number, Discount, Quantity_Sold, Selling_Price)
Values
  (4945, 2, 0, 1, 1.03);


Error starting at line : 234 in command -
Insert Into Bill_Item
    (Bill_Number, Menu_Item_Number, Discount, Quantity_Sold, Selling_Price)
Values
   (4945, 2, 0, 1, 1.03)
Error report -
SQL Error: ORA-01403: no data found
ORA-06512: at "ORCL1_05.TR_Q5", line 24
ORA-04088: error during execution of trigger 'ORCL1_05.TR_Q5'
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of 
fetch.

Answer 1

Here is your bloomer:

  ELSIF INSERTING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :OLD.Bill_Number;

During INSERT the :old namespace is null. This is perfectly logical: we're inserting a record, what else could the old record be?

So the solution is quite straightforward

  ELSIF INSERTING THEN
    SELECT Paid_YN, Posted_YN
    INTO v_paid_YN, v_Posted_Yn
    FROM Bill
    WHERE Bill_Number = :NEW.Bill_Number;

:old is valid when UPDATING and DELETING, which is why those branches work. (It is usual to test for the current - :new - values when UPDATING except when checking whether a value has changed.)