Monday, August 13, 2018

Trigger

$$ Trigger with use when condition $$


CREATE OR REPLACE TRIGGER trg_hpms_room
   AFTER INSERT OR UPDATE
   ON hpms_room
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
   WHEN (NEW.occupancy_service IN ('E', 'C', 'D'))
BEGIN
   IF INSERTING
   THEN
      IF :NEW.occupancy_service = 'C'
      THEN
         DECLARE
            sl   NUMBER (3);
         BEGIN
            SELECT MAX (view_sl) + 1
              INTO sl
              FROM op_patdisplay
             WHERE tvdisplay_id = 1;

            INSERT INTO op_patdisplay
                        (room_name, movement_type_no, room_no, view_sl,
                         show_fg, view_page, tvdisplay_id, check_inout
                        )
                 VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
                         '1', '1', '1', '0'
                        );
         END;
      ELSIF :NEW.occupancy_service = 'E'
      THEN
         DECLARE
            sl   NUMBER (3);
         BEGIN
            SELECT MAX (view_sl) + 1
              INTO sl
              FROM op_patdisplay
             WHERE tvdisplay_id = 3;

            INSERT INTO op_patdisplay
                        (room_name, movement_type_no, room_no, view_sl,
                         show_fg, view_page, tvdisplay_id, check_inout
                        )
                 VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
                         '1', '1', '3', '0'
                        );
         END;
      ELSIF :NEW.occupancy_service = 'D'
      THEN
         DECLARE
            sl   NUMBER (3);
         BEGIN
            SELECT MAX (view_sl) + 1
              INTO sl
              FROM op_patdisplay
             WHERE tvdisplay_id = 2;

            INSERT INTO op_patdisplay
                        (room_name, movement_type_no, room_no, view_sl,
                         show_fg, view_page, tvdisplay_id, check_inout
                        )
                 VALUES (:NEW.room_name, '5', :NEW.room_no, sl,
                         '1', '1', '2', '0'
                        );
         END;
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;
/

Related Posts:

  • Read More
  • For Loop : Example : In this example used a pakage pkg_mm.prcinsitemstk <<Pakage>> <<Procedure>>  PROCEDURE prc_insitemstk (       p_column_name   IN   VARCHAR2, &n… Read More
  • Sql Query: Count duplicate value 1. SELECT REG_NO, COUNT(REG_NO)  FROM OP_APPOINTMENT WHERE APPOINT_DATE BETWEEN NVL(:P_DT1,APPOINT_DATE) AND NVL(:P_DT2,APPOINT_DATE) GROUP BY REG_NO HAVING ( COUNT(REG_NO) > 1 ) 2. select PO… Read More
  • Function : A function has a return type in its specification and must return a value specified in that type. … Read More
  • Procedure : A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it… Read More

0 comments:

Post a Comment