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 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?

share|improve this question
    
Alter table train drop constraint train_len; –  FreshPhilOfSO May 6 '13 at 13:21
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
1  
Phil is correct. In addition, you should almost always prefer constraints to triggers. You should also consider having triggers call packages to do the bulk of the functionality. Unlike triggers, packages can be compiled natively and are significantly easier to unit test. You would still need to test the trigger, but the tests would be better focused. –  Leigh Riffel May 6 '13 at 17:17
add comment

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.