Testing AFTER INSERT Trigger

oracleplsqltrigger

I have the following table: 

CREATE TABLE Train(
    Train_No integer PRIMARY KEY, 
    Loco_No integer REFERENCES Locomotive(Loco_No), 
    Back_Loco_No float REFERENCES Locomotive(Loco_No),  
    Train_Length float CONSTRAINT Train_Len CHECK(Train_Length <= 400), 
    Train_Weight float CONSTRAINT Train_Weight CHECK(Train_Weight <= 10000)
);

What I have done is written a trigger to test the Train_Length is less than 400 as followed: 

create or replace 
trigger TRAIN_LENGTH 
AFTER INSERT ON TRAIN 
REFERENCING NEW AS New
FOR EACH ROW
BEGIN
   IF :new.Train_Length> 400 THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded maximum train length');
  END IF;
END;

I believe the only way to test that this trigger is working is by inserting some data into my train table. However I did notice that looking at my CREATE_TABLE statement I have a CHECK constraint on my Train_Length <= 400. I have temporarily tried inserting some data into my table by doing: 

INSERT INTO Train VALUES (9,1,NULL,500,100);

This throws up: 

SQL Error: ORA-02290: check constraint (K0954342.TRAIN_LEN) violated
02290. 00000 -  "check constraint (%s.%s) violated"

I believe that the cause of this would be because the value 500 violates the check constraint as the Train_Length <= 400. Also I noticed that my trigger is called AFTER INSERT ON TRAIN which means that really and truly the trigger is only ever called after insert on the train table. BUT! Because I have my check constraint on my Train table being Train_Len CHECK(Train_Length <= 400) this means that this is being called before the trigger is even called.

SO I believe the only way to check that my trigger does work is to drop my Train table and remove the check constraint Train_Len CHECK(Train_Length <= 400) and then try to re-run the following INSERT: 

INSERT INTO Train VALUES (9,1,NULL,500,100);

If someone can tell me if this is correct?

Best Answer

Your analysis is correct. You don't need the trigger because the constraint is doing the same job. Anyway, to drop the constraint use:

Alter table train drop constraint train_len;
Related Question