Thread: Oracle Trigger

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Posts
    13
    Rep Power
    0

    Oracle Trigger


    I think I might need some help on creating a trigger. This is for my oracle class which isn't really that easy, that's why I'm asking for help here. I really want to get this working because it's extra credit and I know I'll need all the points I can get. Is there something wrong with this trigger so far? When I run an insert statement it should run the trigger right, and should come to the exception and print out that line and stop the insert but it doesn't, and I'm just not sure why.

    create or replace trigger validate_library
    before insert or update on Item for each row
    DECLARE
    e_book_not_found EXCEPTION;
    e_dvd_not_found EXCEPTION;
    e_bot_not_found EXCEPTION;
    v_item_type Item.Item_Type%TYPE := :NEW.Item_Type;
    v_checkout_period Item.Checkout_Period%TYPE := :NEW.Checkout_Period;
    v_item_book_id Item.Item_Book_ID%TYPE := :NEW.Item_Book_ID;
    v_item_dvd_id Item.Item_DVD_ID%TYPE := :NEW.Item_Dvd_ID;
    v_item_tape_id Item.Item_Tape_ID%TYPE := :NEW.Item_Tape_ID;
    BEGIN
    IF v_item_type = 'Book' THEN
    RAISE e_book_not_found;
    END IF;

    EXCEPTION
    WHEN e_book_not_found THEN
    DBMS_OUTPUT.PUT_LINE('item not in book');
    WHEN e_dvd_not_found THEN
    DBMS_OUTPUT.PUT_LINE('item not in dvd');
    WHEN e_bot_not_found THEN
    DBMS_OUTPUT.PUT_LINE('item not in book_on_tape');
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('item not in any table');
    END;


    here's the insert statement:

    insert into Item values(item_id_seq.NEXTVAL, 'Book', SYSDATE, '001', NULL, NULL);

    and other useful code:

    create sequence item_id_seq start with 1 nocache;

    create table Item (
    Item_ID number(12,0) not null primary key,
    Item_Type varchar2(20) not null,
    Checkout_Period date not null,
    Item_Book_ID number(12, 0) null,
    Item_Dvd_ID number(12, 0) null,
    Item_Tape_ID number(12, 0) null );


    obviously, this isn't the entire program. I've only just started on it, and it's making me mad. Thanks if anyone can help. But if no one can, believe me I'll understand. I barely understand this myself.
  2. #2
  3. #3
  4. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    Why are you doing this:

    IF v_item_type = 'Book' THEN
    RAISE e_book_not_found;

    instead of just:

    IF :new.item_type = 'Book' THEN
    RAISE e_book_not_found;

    I don't think the v_item_type variable has a value.
  5. #4
  6. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Posts
    13
    Rep Power
    0
    why before insert? because that's our assignment.


    IF :new.item_type = 'Book' THEN
    RAISE e_book_not_found;

    I tried this as well, but is it not supposed to display this line:
    item not in book
    but it doesn't...it just says that 1 row created when I insert a row. I thought it's just supposed to display the error line before the insert takes place. arrghh!

IMN logo majestic logo threadwatch logo seochat tools logo