Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Location
    Chennai, India
    Posts
    7

    Unanswered: Trigger Return Value

    DECLARE
    --TYPE RECORD IS REFCURSOR;
    v_checkin numeric(5);


    BEGIN

    IF(TG_OP = 'INSERT') THEN
    SELECT count(check_in) INTO v_checkin FROM hm_room_status
    WHERE ( check_in <= new.check_out AND check_out >= new.check_out )
    AND (status_list ='CheckIn' or status_list = 'Reservation')
    AND hm_room_details_id = new.hm_room_details_id;
    IF(v_checkin >= 1 ) THEN
    RAISE EXCEPTION '%', v_checkin;
    END IF;
    END IF;

    IF(TG_OP = 'UPDATE') THEN
    SELECT count(check_in) INTO v_checkin FROM hm_room_status
    WHERE ( check_in <= new.check_out AND check_out >= new.check_out )
    AND (status_list ='CheckIn' or status_list = 'Reservation')
    AND hm_room_details_id = new.hm_room_details_id;
    IF(v_checkin >= 1) THEN
    RAISE EXCEPTION '%', v_checkin;
    END IF;
    END IF;
    IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;

    END;

    1. This is my trigger it return v_checkin value is 1, but the database has no record,
    2. If it has a record by the time i add a new record with new checkout value between old checkin and old checkout, it gives v_checkin value 2
    why it return like that ?
    Anyone can tell me?

  2. #2
    Join Date
    May 2008
    Posts
    277
    You haven't provided many details, but this looks like some kind of reservation check. Here's a simple, sample reservation table:

    Code:
    CREATE TABLE reservation (
        reservation_id SERIAL PRIMARY KEY,
        check_in DATE NOT NULL,
        check_out DATE NOT NULL,
        room_num INT NOT NULL REFERENCES room,
        ...
    );
    So you want to ensure that no two reservations overlap for the same room. Keep in mind that:
    - check-out and check-in can occur on the same day
    - when updating a reservation, you don't want to generate a conflict with the old reservation

    So your trigger function should look something like this:

    Code:
    PERFORM reservation_id
    FROM reservation
    WHERE 
        reservation_id <> NEW.reservation_id
        AND room_num = NEW.room_num
        AND (check_in, check_out) OVERLAPS (NEW.check_in, NEW.check_out);
    
    IF FOUND THEN
        RAISE EXCEPTION 'double-booked';
    END IF;
    
    RETURN NEW;
    Make this a BEFORE INSERT OR UPDATE trigger on your reservation table.

  3. #3
    Join Date
    Dec 2009
    Location
    Chennai, India
    Posts
    7

    Getting datas between two dates with datewise

    thank you
    by
    mani

Posting Permissions

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