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

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0

    newbie Trigger problem


    Hi, I've been trying to create a trigger in oracle 8i but oracle just hangs at a line marker and does nothing after i feed it the code to create the trigger.
    I have the following tables
    family(id, name, street, city, state, zipcode, reciprocity)
    gift(sender_id, receiver_id, occasion, presentType, reciprocal, order_id)
    and my trigger is the following.
    Code:
    CREATE TRIGGER handleReciprocity
    AFTER INSERT ON gift
    REFERENCING
          NEW ROW AS nt
    FOR EACH ROW
          WHEN( ((SELECT reciprocity FROM family
                 WHERE id = nt.receiver_id) = 'YES') AND
                 NOT EXISTS ( SELECT * FROM gift
                              WHERE (sender_id = nt.receiver_id AND
                                     receiver_id = nt.sender_id AND
                                     occasion = nt.occasion AND
                                     reciprocal = 'YES')))
          INSERT INTO gift
                 VALUES(nt.receiver_id, nt.sender_id, nt.occasion, nt.presentType, 'YES', nt.orderId + 1);
    Any help will be appreciated,
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    I did not test it, but shouldn't it be:
    Code:
    CREATE TRIGGER handleReciprocity
    AFTER INSERT ON gift
    REFERENCING
          NEW ROW AS nt
    FOR EACH ROW
          WHEN( ((SELECT reciprocity FROM family
                 WHERE id = nt.receiver_id) = 'YES') AND
                 NOT EXISTS ( SELECT * FROM gift
                              WHERE (sender_id = nt.receiver_id AND
                                     receiver_id = nt.sender_id AND
                                     occasion = nt.occasion AND
                                     reciprocal = 'YES')))
    BEGIN
          INSERT INTO gift
                 VALUES(nt.receiver_id, nt.sender_id, nt.occasion, nt.presentType, 'YES', nt.orderId + 1);
    END;
    /
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0
    Uhmm i was missing the / at the end of the script. I had no idea you have to use it.
    At least i'm getting error messages now
    Do you have to use a / at the end of every script? or just scripts containing triggers?

    Thanks for the help
    Last edited by Vortrex; December 22nd, 2003 at 02:42 PM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Especially in scripts with triggers!

    Your trigger will eventually have more then one statement, and thus more then one ; in it.
    How should the parser know which ; terminates the triger and which ; just terminates a statement
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0
    Thanks a lot!

IMN logo majestic logo threadwatch logo seochat tools logo