SHARE
TWEET

Untitled

a guest Nov 8th, 2019 85 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. CREATE OR REPLACE TRIGGER maintain_guest_bookings AFTER
  3.     UPDATE OF booking_state ON booking
  4.     FOR EACH ROW
  5.     --WHEN (old.booking_from > sysdate)
  6. DECLARE
  7.     v_guest_bookings   NUMBER(5);
  8. BEGIN
  9.     IF
  10.         :new.booking_from > SYSDATE AND :new.booking_state = 'C'
  11.     THEN
  12.         raise_application_error(-20003,'Future booking cannot have booking state as C (completed).');
  13.     ELSE
  14.         SELECT
  15.             guest_bookings
  16.         INTO
  17.             v_guest_bookings
  18.         FROM
  19.             guest
  20.         WHERE
  21.             guest_no =:new.guest_no;
  22.  
  23.         IF
  24.             :new.booking_state = 'C' AND :old.booking_state <> 'C'
  25.         THEN
  26.             UPDATE guest
  27.                 SET
  28.                     guest_bookings = v_guest_bookings + 1
  29.             WHERE
  30.                 guest_no =:new.guest_no;
  31.  
  32.         ELSIF :old.booking_state = 'C' AND :new.booking_state <> 'C' THEN
  33.             UPDATE guest
  34.                 SET
  35.                     guest_bookings = v_guest_bookings - 1
  36.             WHERE
  37.                 guest_no =:new.guest_no;
  38.  
  39.         END IF;
  40.  
  41.     END IF;
  42. END;
  43. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top