Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: questions on Trigger

    hi,
    i want create a trigger when i update a reservation table ..it should triggger a insert function on to the charge table.i have tried ...a code ..which is not working ...tell me how to deal with this....

    i am sure ...syntax which is used not correct..so i need ur help for fire this trigger


    create or replace trigger Credit_Card_Charge
    AFTER UPDATE on reservation
    for each row
    declare begin

    select reservationid from reservation
    where
    (floor ((startdatetime-cancellationdatetime )*24) -
    (floor(startdatetime-cancellationdatetime)*24)) < 48;
    // as u check ...as per condition ..i am finding reservation id ...now i
    // have to
    // insert values into charge table ..with date ...category--//'fine',charge,and
    //reservation id...how to enter reservation value into charge table got
    //from reservation
    //table....

    insert into charges values(TO_DATE('12/09/2003',
    'MM/DD/YYYY-HH24-MI-SS''),'FINE',50.00,4);



    end;
    /


    thanx..cya....

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    First of all, let me clear your problem.

    as per your code, the value returned by the WHERE clause will always be 0 because you r substracting the same value. Obviously, your code is syntectically wrong.


    "(floor ((startdatetime-cancellationdatetime )*24) -
    (floor(startdatetime-cancellationdatetime)*24)) < 48"

    I guess either of them shoud be :NEW and :OLD as shown following. I don't know your table data. but you can figure it out.


    "(floor ((:OLD.startdatetime-:OLD.cancellationdatetime )*24) -
    (floor(:NEW.startdatetime-:NEW.cancellationdatetime)*24)) < 48"

    So with correction, it shoud look like this.

    CREATE OR REPLACE TRIGGER Credit_Card_Charge
    AFTER UPDATE ON reservation
    FOR EACH ROW
    DECLARE
    v_temp VARCHAR2 (50);
    BEGIN
    SELECT reservationid //provided if it returns only one value.
    INTO v_temp // otherwise you can use cursor or TYPE.
    FROM reservation
    WHERE
    (FLOOR ((:OLD.startdatetime-:OLD.cancellationdatetime )*24) -
    FLOOR ((:NEW.startdatetime-:NEW.cancellationdatetime)*24)) <
    48;
    INSERT INTO charges VALUES (TO_DATE('12/09/2003',
    'MM/DD/YYYY-HH24-MI-SS'),'FINE',50.00,4);
    END;
    /

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I forgot to mention that you can also include exception handler in your code.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Nov 2003
    Posts
    9

    questions on trigger

    Originally posted by Hings
    I forgot to mention that you can also include exception handler in your code.

    hi,

    i will explain about my table

    we are designing table for hotel reservation:

    i have a table --->
    reservation containing following attributes:

    reservationid,hotelphone,startdate,nights,guestpho ne,creditcard_num,
    checkindate,checkoutdate,cancellationdate,roomnumb er

    charges table containing following attributes

    chargesdate,category(like FINE OR ROOM CHARGES),charge_amount,
    reservation_id

    so i told earlier....i have triggering if person cancel ...before startdate
    time ie 48 hrs before he has to cancel...otherwise ..we have to charge...

    that is y ...i am selecting reservationid from reservation table...where i am finding out ...(i am finding out whether people have cancelled before 48 hrs or not....

    for this only i have used this function....

    (floor ((startdatetime-cancellationdatetime )*24) -
    (floor(startdatetime-cancellationdatetime)*24)) < 48;

    using that reservationid ..i am going to insert values into charge table.....
    chargedate,category,amount,reservationid..

    i have already got a idea about the trigger..but i have given u all details about my table..could give me idea how to do that...
    wat is NEW and OLD..when they will be used...


    thanxx...pls give me a clear idea....

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    You mean to say that if someone cancels reservation before 48 hours of startdate, than it is okey, otherwise you have to charge them. So
    do following.

    CREATE OR REPLACE trigger xxx
    AFTER UPDATE OF cancellationdate ON reservation
    FOR EACH ROW
    DECLARE
    v_flag NUMBER;
    BEGIN
    SELECT 1
    FROM reservation
    WHERE :OLD.cancellationdate IS NULL;
    IF (startdate - :NEW.cancellationdate) > 48 THEN
    INSERT INTO charges VALUES
    (TO_DATE('12/09/2003', 'MM/DD/YYYY-HH24-MI-S'),'FINE',50.00,4);
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    END;
    /

    I guess this would help you out. I haven't checked it syntectically so plese correct it if it has any syntex error.

    For :OLD and :NEW, please read oracle documentation.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I am sorry,

    it should be " < 48 " instead of > 48. please correct it.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •