Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
Have you actually tried compiling it so that you know where the syntax errors are? –  Phil Mar 26 '13 at 14:17
4  
There isn't even a Train_Weight column in the Locomotive table! –  Phil Mar 26 '13 at 14:34
    
@Phil I updated the question it was suppose to be MAX_TARE_WEIGHT my fault –  user532339 Mar 26 '13 at 15:44

2 Answers 2

up vote 3 down vote accepted

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 ) ; 
share|improve this answer

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;
share|improve this answer
    
(SELECT Train_Weight FROM locomotive) -- You're selecting every row in the table. Why? Certainly not correct. –  Phil Mar 26 '13 at 15:07
    
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 –  druzin Mar 26 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.