PLSQL: BEFORE INSERT Trigger

plsqltrigger

I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If the comparison fails (that is, TRAIN_WEIGHT > MAX_TOW_WEIGHT) the code would need to throw a user-defined exception, which the code which executed the INSERT or UPDATE would need to be written to handle. I have done the following which is most probably wrong though it is an attempt: 

CREATE TRIGGER LOCOWEIGHT 
BEFORE INSERT
ON Locomotive_Class
REFERENCING NEW AS New
FOR EACH ROW
   BEGIN
      SELECT Train_Weight FROM locomotive
   IF (MAX_TARE_WEIGHT > MAX_TOW_WEIGHT) THEN
       RAISE cError;    
EXCEPTION
WHEN cError THEN
      RAISE_APPLICATION_EXCEPTION('Train weight has exceeded max tow weight');
END;

Tables for reference: 

CREATE TABLE Locomotive
 (Loco_No integer PRIMARY KEY,
  Serial_No integer UNIQUE,
  Loco_Class_No integer
    REFERENCES Locomotive_Class(Loco_Class_No),
  Loco_name varchar2(20));


CREATE TABLE Locomotive_Class
 (Loco_Class_No integer PRIMARY KEY,
  Max_Tow_Weight float NOT NULL,
  Loco_Class_Len float NOT NULL);

Best Answer

Several things.

1) Your trigger doesn't look right - I think you need the trigger to be on the locomotive table, rather than the locamotive_class table, and have a newly inserted/updated train have its maximum weight checked against the maximum weight for the class.

2) There is no train_weight column in the locomotive table.

Assuming the above is true, this is the trigger code you require:

CREATE OR REPLACE TRIGGER LOCOWEIGHT 
BEFORE INSERT OR UPDATE ON locomotive
REFERENCING NEW AS New
FOR EACH ROW
DECLARE
  MAX_WEIGHT NUMBER;
BEGIN
  SELECT max_tow_weight INTO MAX_WEIGHT
  FROM locomotive_class
  WHERE loco_class_no = :new.loco_class_no;

   IF :new.train_weight > MAX_WEIGHT THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded max tow weight');
  END IF;
END;

Along with:

alter table locomotive add ( train_weight number ) ;
Related Question