Code to stop a referree from being double booked

Part 1: I have written this code to stop a referee from being double booked on an insert. It all almost works, but I keep getting the error message below. 

SQL> CREATE OR REPLACE TRIGGER  REFCHECK
  2  AFTER INSERT OR UPDATE OF REFEREE_ID ON MATCH
  3  FOR EACH ROW
  4  
  5  
  6  DECLARE
  7    V_Referee_id Match.REFEREE_ID%TYPE;
  8    REFEREE_doublebook_ERROR EXCEPTION;
  9  
 10  BEGIN
 11    SELECT REFEREE_ID
 12    Into V_Referee_id
 13  From MATCH
 14  Where REFEREE_ID= 401.402.403;
 15  
 16  IF REFEREE_ID= V_Referee_id THEN
 17  RAISE REFEREE_doublebook_ERROR;
 18  
 19    END IF;
 20  
 21  EXCEPTION
 22   WHEN REFEREE_doublebook_ERROR THEN
 23  RAISE_APPLICATION_ERROR (-20000, 'This REFEREE HAS ALREADY BEEN BOOKED
STUIPED!');
 24  END;
 25  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER REFCHECK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/26     PLS-00103: Encountered the symbol ".403" when expecting one of
         the following:
         * & - + ; / at for mod rem  and or group
         having intersect minus order start union where connect ||
         The symbol "*" was substituted for ".403" to continue.

Part 2: I have created a trigger, but it mutates. How do I get around it? I read a little on this, and it said somthing about creating a table-level trigger that can read mutating tables. Can you help? The code executes fine. 

CREATE OR REPLACE TRIGGER RecalculatePlayerPoints
AFTER INSERT OR DELETE OR 
UPDATE OF PEN, PLAYER_NO ON Caution
FOR EACH ROW						
								
DECLARE							
	v_Player_NO		PLayerMatch.Player_NO%TYPE;		
 	v_PenaltyPoint	PLayerMatch.PenaltyPoint%TYPE;		
							
BEGIN								
	IF DELETING OR
	   UPDATING THEN
		SELECT PEN
		INTO v_PenaltyPoint		
		FROM caution					
		WHERE Player_NO = :old.Player_NO;		
		
		UPDATE Caution
		SET PEN = v_PenaltyPoint - (:old.Pen)
		WHERE Player_NO = :old.Player_NO;
	END IF;						
								
	IF INSERTING OR 					
	   UPDATING THEN
		SELECT Pen
		INTO v_PenaltyPoint				

		FROM Caution					
		WHERE Player_NO = :new.Player_NO;		

	UPDATE Caution
	SET Pen = v_PenaltyPoint + (:new.Pen)
	WHERE Player_NO = :new.Player_NO;
	END IF;
END;
/
-- I got this error:

SQL> update caution 
  2  set pen=8
  3  where player_no=003;
update caution
       *
ERROR at line 1:
ORA-04091: table U9805165.CAUTION is mutating, trigger/function may not see
it
ORA-06512: at "U9805165.RECALCULATEPLAYERPOINTS", line 10
ORA-04088: error during execution of trigger
'U9805165.RECALCULATEPLAYERPOINTS'


    Requires Free Membership to View

    Login

Part 1: The error is actually here:

14  Where REFEREE_ID= 401.402.403;
You cannot create a list in that manner. (I'm assuming you meant to be checking for referee_id to be either 401 or 402 or 403.) If you change the where clause as follows, it will fix the error: 
 
WHERE REFEREE_ID IN (401, 402, 403);

Part 2: You can't refer to the same table your trigger is based on in a trigger (this will always cause a mutating table error). You can read alot more on this site by doing a search on "mutating tables".

But, it appears to me that if you just code your trigger a bit differently, you can fix your problem easily. I think you may need to check your logic as I don't think what you're trying to do makes sense. But, you do not need to select or update at all in the trigger code. Simply change your trigger to be a BEFORE trigger (instead of AFTER) and change the code to reference both :old and :new column values appropriately. You already have all the values you need so there's no need to requery. If you do the trigger BEFORE the action, they you can override the value of the column by calculating whatever you want and placing it in the :new column. That way when the row is written out, it will have your calculated value.

Just make sure that you're doing what you think. Your logic currently doesn't make any sense. For example, you are attempting to both ADD and SUBTRACT when you update... which is it? Regardless, remove all the SELECTs and UPDATEs from your code and only reference the :new and :old values. Remember that in a trigger the :new values are the values about to be inserted, updated or deleted (obviously the value would be null for a delete, right?) and the :old values are the original values before the user did the insert, update or delete. Armed with both sets of values, you do not need to do additional SELECTs. And if you simply change the value of the :new attribute for your column (PEN) then it will get set appropriately when the trigger finishes and you issue a commit.

For More Information


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top