2

I created a trigger in SQL and it compiles ok but when I run an insert it I get the following error: 

SQL Error: ORA-01403: no data found  
ORA-06512: at "EOCRIBIN.SHIPMENT_CAPACITY", line 5  
ORA-04088: error during execution of trigger 'EOCRIBIN.SHIPMENT_CAPACITY'  
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.

Here is the trigger :

CREATE OR REPLACE TRIGGER Shipment_capacity
BEFORE INSERT or UPDATE on SHIPMENT_TYPE FOR EACH ROW
DECLARE 
  NOT_ENOUGH_CAPACITY EXCEPTION;
  WEIGHT INTEGER;
BEGIN

  SELECT VolumeCapacity
    INTO WEIGHT
    FROM shipment_type
   WHERE ShipmentType = :NEW.ShipmentType;

  IF WEIGHT > :new.WeightCapacity THEN
    RAISE NOT_ENOUGH_CAPACITY;
  END IF;

  EXCEPTION 
  WHEN NOT_ENOUGH_CAPACITY THEN
    RAISE_APPLICATION_ERROR(-200003,'Volume capacity exceeds weight limit');
END;

The table consists of the following :

CREATE TABLE SHIPMENT_TYPE
(
    ShipmentType varchar(25) primary key, 
    VolumeCapacity INT, 
    WeightCapacity INT, 
    Temperature float
);

Any idea why this error is happening?

diziaq
  • 6,881
  • 16
  • 54
  • 96
Eoin Ó Cribín
  • 155
  • 1
  • 3
  • 14
  • 1
    What are you trying to accomplish with your `select` statement? If `ShipmentType` is the primary key, it looks like you just want to check `if :new.VolumeCapacity > :new.weightCapacity then`. That doesn't seem like a particularly reasonable check to make. I would guess that you really want to query a different table to get something to compare against. – Justin Cave Dec 15 '15 at 18:39
  • Think that worked actually. I was following an example that's why I had the select but that makes sense cheers – Eoin Ó Cribín Dec 15 '15 at 18:45

1 Answers1

2

I think your select query return Nothing. You can catch the exception with 

WHEN NO_DATA_FOUND THEN
     --whatever you want to do when the error appear
END;
Matriac
  • 382
  • 3
  • 11