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'
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002