CODEWITHSUNDEEP
sqloracle-databaseplsqltriggersoracle-sqldeveloper
stacker
stacker

Reputation: 97

How to use Oracle trigger (and a domain table) in place of check constraint to enforce column range

This trigger (Oracle 12c) is intended to stop the insertion and updating of rows in a table (MainTable aka Room) where a column (price) is larger than a variable. The value of the variable depends on another column (type). There are three 'type's (S, D, K) and the allowed max price for the 'type's are 100, 150, and 200, respectively. The trigger works by referencing a domain table (DomainTable aka RoomType) with two columns and three rows as follows [roomTypeCode(S, D, K), maxPrice(100, 150, 200)] and ensuring that:

...IF new MainTable.type = 'S', THEN new MainTable.price < DomainTable.maxPrice(S);

...IF new MainTable.type = 'D', THEN new MainTable.price < DomainTable.maxPrice(D);

...IF new MainTable.type = 'K', THEN new MainTable.price < DomainTable.maxPrice(K);

Here is my attempt that does not work.


CREATE TRIGGER Room_Type_Price_Range
  BEFORE INSERT OR UPDATE ON room
  REFERENCING NEW AS newRec
  FOR EACH ROW
  DECLARE
    SELECT maxPrice INTO singleRmMax FROM RoomType WHERE RoomTypeCode = 'S';
    SELECT maxPrice INTO doubleRmMax FROM RoomType WHERE RoomTypeCode = 'D';
    SELECT maxPrice INTO kingRmMax FROM RoomType WHERE RoomTypeCode = 'K';
  BEGIN
     IF (   (:newRec.type = 'S' AND :newRec.price > singleRmMax)
        OR  (:newRec.type = 'D' AND :newRec.price > doubleRmMax)
        OR  (:newRec.type = 'K' AND :newRec.price > kingRmMax)
        )
        RAISE_APPLICATION_ERROR(-20001, 'Price constraint violated.  
          \nCannot Insert/Update in this table.');
  END;

My error message: 


04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Thanks for your help!

Upvotes: 0

Views: 269

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

When you created the trigger you would have seen a message like 'compiled with warnings' or 'Errors: check compiler log'. At that point you can do show errors to see why the compilation failed, or look at SQL Developer's compiler log window.

When you insert or update the invalid trigger is automatically recompiled, but as it's still invalid you get the ORA-04098 error. You can still see what is wrong by querying the user_errors view:

select line, position, text
from user_errors
where type = 'TRIGGER'
and name = 'ROOM_TYPE_PRICE_RANGE'
order by sequence;

Which with your code gives three errors; showing only the first line of each:

LINE POSITION TEXT
---- -------- -------------------------------------------------------------------------------------------------
   2        5 PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
  10        9 PLS-00103: Encountered the symbol "RAISE_APPLICATION_ERROR" when expecting one of the following:
  11       50 PLS-00103: Encountered the symbol ";" when expecting one of the following:

As David Faber pointed out in a comment, the first error is because you have a select statement in your declare section; perhaps reviewing the structure of a subprogram would be useful at this point.

The second error is because your IF doesn't have a THEN keyword, and the third is because you don't have an END IF either. Just cleaning up what you have to declare and populate the variables properly, you'd get something like:

DECLARE
  singleRmMax RoomType.MaxPrice%TYPE;
  doubleRmMax RoomType.MaxPrice%TYPE;
  kingRmMax RoomType.MaxPrice%TYPE;
BEGIN
  SELECT maxPrice INTO singleRmMax FROM RoomType WHERE RoomTypeCode = 'S';
  SELECT maxPrice INTO doubleRmMax FROM RoomType WHERE RoomTypeCode = 'D';
  SELECT maxPrice INTO kingRmMax FROM RoomType WHERE RoomTypeCode = 'K';

  IF (   (:newRec.type = 'S' AND :newRec.price > singleRmMax)
    OR  (:newRec.type = 'D' AND :newRec.price > doubleRmMax)
    OR  (:newRec.type = 'K' AND :newRec.price > kingRmMax)
    ) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Price constraint violated.  
      \nCannot Insert/Update in this table.');
  END IF;
END;

You don't really need three variables though, you can just query the room type you're interested in:

DECLARE
  roomTypeMax RoomType.MaxPrice%TYPE;
BEGIN
  SELECT maxPrice INTO roomTypeMax
  FROM RoomType
  WHERE RoomTypeCode = :newRec.type;

  IF :newRec.price > roomTypeMax THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Price constraint violated. Cannot Insert/Update in this table.');
  END IF;
END;

I've also taken the \n out of the error message as whatever does the insert is likely to treat that as two literal characters rather than a line break.

You might also want to consider catching no_data_found and raising your own exception, as that would indicate that the new room type doesn't exist and therefore isn't valid at any price.

Upvotes: 2

Related Questions