11 Replies Latest reply: Feb 27, 2013 10:45 PM by Toon Koppelaars RSS

    Before Update trigger.. can anybody help whats wrong in this trigger!

    992549
      SQL> CREATE OR REPLACE TRIGGER UR
      2 BEFORE UPDATE ON BOOKING
      3 REFERENCING NEW AS n
      4 FOR EACH ROW
      5 DECLARE
      6 temp INT;
      7
      8 BEGIN
      9 SELECT COUNT(*) INTO TEMP FROM BOOKING WHERE HOTELID = :n.HOTELID AND ROOMNO = :n.ROOMNO AND((STARTDATE < :n.STARTDATE AND ENDDATE >= :n.STARTDATE) OR (STARTDATE BETWEEN :n.ST
      ARTDATE AND :n.ENDDATE));
      10 IF temp > 0 THEN
      11 RAISE_APPLICATION_ERROR(-20000,'Booking already exists.');
      12 END IF;
      13 END;
      14 .
      SQL> /

      Trigger created.


      When i try updating the booking table im getting the following error:


      SQL> UPDATE BOOKING SET STARTDATE = '2013-04-06', ENDDATE = '2013-04-13' WHERE GUESTID = '0002';
      UPDATE BOOKING SET STARTDATE = '2013-04-06', ENDDATE = '2013-04-13' WHERE GUESTID = '0002'
      *
      ERROR at line 1:
      ORA-04098: trigger 'SYSTEM.UB' is invalid and failed re-validation
        • 1. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
          992549
          I have found some errors and have fixed them. But now im getting this error :

          *
          ERROR at line 1:
          ORA-04091: table SYSTEM.BOOKING is mutating, trigger/function may not see it
          ORA-06512: at "SYSTEM.UR", line 5
          ORA-04088: error during execution of trigger 'SYSTEM.UR'
          • 2. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
            sb92075
            989546 wrote:
            I have found some errors and have fixed them. But now im getting this error :

            *
            ERROR at line 1:
            ORA-04091: table SYSTEM.BOOKING is mutating, trigger/function may not see it
            ORA-06512: at "SYSTEM.UR", line 5
            ORA-04088: error during execution of trigger 'SYSTEM.UR'
            you can NOT do SQL against the table upon which this trigger is based!
            • 3. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
              992549
              Oh! so is there any other way i can implement this trigger? because as per my problem i need to do sql for the same trigger table!
              • 4. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                rp0428
                >
                ERROR at line 1:
                ORA-04098: trigger 'SYSTEM.UB' is invalid and failed re-validation
                >
                You did not post code for 'SYSTEM.UB'.

                What you are trying to do won't work anyway especially when there can be more than one user updating this table. No user can see the uncommited changes being made by other users. So each user might think the 'booking' doesn't exist but after one user commits their changes it will exist; but the other user won't know it.

                Don't try to perform transaction-related work in a trigger; they are not designed for that and will not work properly for that.
                • 5. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                  rp0428
                  >
                  Oh! so is there any other way i can implement this trigger? because as per my problem i need to do sql for the same trigger table!
                  >
                  NO! Don't perform transaction-related work in a trigger; they aren't designed to do that.

                  You need to use a transaction-based process for this type of work.
                  • 6. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                    992549
                    I have dropped UB trigger. And im done with that error. Now im only with mutating error table.
                    • 7. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                      rp0428
                      All of the comments that SB and I made still apply. You are trying to do something that triggers are not designed to do.
                      • 8. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                        992549
                        IS DER ANY WAY THE FOLLOWING TRIGGER HELP?

                        CREATE OR REPLACE TRIGGER UR
                        BEFORE UPDATE ON BOOKING
                        REFERENCING NEW AS n
                        FOR EACH ROW
                        DECLARE
                             temp INT;
                             mutatingTable EXCEPTION;
                             PRAGMA EXCEPTION_INIT(mutatingTable,-4091);
                        BEGIN
                        SELECT COUNT(*) INTO temp FROM BOOKING WHERE HOTELID = :n.HOTELID AND ROOMNO = :n.ROOMNO AND((STARTDATE < :n.STARTDATE AND ENDDATE >= :n.STARTDATE) OR (STARTDATE BETWEEN :n.STARTDATE AND :n.ENDDATE));
                        IF temp > 0 THEN
                        RAISE_APPLICATION_ERROR(-20000,'Booking already exists.');
                        END IF;
                        EXCEPTION
                        WHEN mutatingTable THEN NULL;
                        END;


                        BECAUSE I NEED TO ANY HOW IMPLEMENT THE TRIGGER!!
                        • 9. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                          sb92075
                          Below is the FIRST rule regarding holes.

                          When you find yourself in a hole, the FIRST thing you should do is STOP digging!
                          • 10. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                            rp0428
                            >
                            IS DER ANY WAY THE FOLLOWING TRIGGER HELP?
                            >
                            No - what part of "Don't perform transaction-related work in a trigger" are you having trouble with?

                            Transaction-related processes like your booking process are more properly dealt with by finding and locking the row of interest so that other users CANNOT possibly modify it until you get done.

                            Your trigger doesn't even DO anything other than attempt to validate data. But your trigger cannot see uncommitted data that other users have that may represent a 'booking' for that same room.

                            You need to serialize the booking process for a single room. You can write a PL/SQL procedure that tries to SELECT . . . FOR UPDATE a room and, if successful, can then take whatever time is needed to complete the transaction and COMMIT the changes. No other user will be able to book that same room using a query or by calling the same procedure because you will have it LOCKED until you are done with it.

                            You CANNOT use a trigger to do that.
                            • 11. Re: Before Update trigger.. can anybody help whats wrong in this trigger!
                              Toon Koppelaars
                              It seems that you are trying to implement a (data) constraint that says something about bookings for a room not being allowed to overlap.

                              You can do this with triggers, for sure. However it's a bit more complex that you might think at first glance.

                              - For this type of 'multi-row' constraint your code will always have to query the 'mutating' table, which is the table that has the constraint.
                              - Row-lever triggers have a restriction in that they're simply not allowed to do that. I could tell you why right here now, but won't. Just take it as a fact.
                              - The way around this, is that you 'defer' querying the mutating table to a post-statement level trigger (which do not have this restriction).
                              - That post-statement level trigger however does not have the context of the :new and :old built-in variables that the row-level trigger does.
                              - The way around this is that you, from within your row-level trigger, 'store' the required values of :new/:old, somewhere such that your statement-level trigger code can access these values.
                              - Possible ways of doing this is to use (persistent) global package variables as holders for these values (you'd have to create an auxiliary package along with your row + statement trigger).
                              - Or store these values in a GTT (global temporary table).

                              Now this is only one bit of the complexity involved when implementing multi-row constraints using triggers. Other aspects you'd have to cater for are:
                              - You'd need an insert trigger also (not just an update trigger), that does similar checking to maintain this constraint.
                              - You'd need to devise some manner of making this all work correctly in case mutiple transactions simultaneously update or insert rows that are involved in this constraint. This is a whole topic in itself...