PLSQL: BEFORE INSERT Trigger

Question

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);

• Philᵀᴹ: Have you actually tried compiling it so that you know where the syntax errors are?
• Philᵀᴹ: There isn't even a Train_Weight column in the Locomotive table!
• user532339: @Phil I updated the question it was suppose to be MAX_TARE_WEIGHT my fault

Answer 1

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 ) ; 

• HEEN: I also want something like this. have a look at this stackoverflow.com/questions/36300102/…

Answer 2

Your example tables is lacking the Train_Weight column you seem to be referencing in your trigger. I'd also think that the trigger should be on the Locomotive table So, I assume this is the correct structure:

CREATE TABLE Locomotive_Class
 (Loco_Class_No integer PRIMARY KEY,
 Max_Tow_Weight float NOT NULL,
 Loco_Class_Len float NOT NULL);
CREATE TABLE Locomotive
 (Loco_No integer,
 Serial_No integer,
 Loco_Class_No integer References Locomotive_Class(Loco_Class_no),
 Train_Weight float,
 Loco_name varchar2(20));

The easies way is to simply do this ( I reversed the comparison operator for the Train_weight, since that seemed more logical ):

CREATE TRIGGER LOCOWEIGHT 
BEFORE INSERT
ON Locomotive
REFERENCING NEW AS New
FOR EACH ROW
 BEGIN
 IF ( :new.Train_Weight > (SELECT MAX_TOW_WEIGHT FROM locomotive_class where Loco_Class_No = :new.Loco_Class_No) ) THEN
 raise_application_error( -20001, 'Train weight has exceeded max tow weight' );
END;

But if you want, you can do it by first defining a custom exception and then raising it and recatching it within the same code. That seems a little redundant, but then you'd do it like this:

declare cError EXCEPTION;
PRAGMA EXCEPTION_INIT( cError, -20001 );
begin
 raise_application_error( -20001, 'This is a custom error' );
 exception when cError then 
 dbms_output.put_line( sqlerrm );
end;

then you can raise it from within your trigger.

Then your trigger would look something like this :

CREATE TRIGGER LOCOWEIGHT 
BEFORE INSERT
ON Locomotive
REFERENCING NEW AS New
FOR EACH ROW
 BEGIN
 IF ( :new.Train_Weight > (SELECT MAX_TOW_WEIGHT FROM locomotive_class where Loco_Class_No = :new.Loco_Class_No) ) THEN
 RAISE cError; 
 EXCEPTION 
 WHEN cError THEN
 RAISE_APPLICATION_EXCEPTION(-20002, 'Train weight has exceeded max tow weight');
END;

• Philᵀᴹ: (SELECT Train_Weight FROM locomotive) -- You're selecting every row in the table. Why? Certainly not correct.
• druzin: That's true, it lacks the where clause. THe trigger should probably also be on the Locomotive table, since this doesn't really make much sense as is. I'll modify my query