I cant find solution for the trigger mutating in PL/SQL

user_14260967 0
Viewed 97

So i am a newbie in PL/SQL, And i want to create a trigger in which a specific record salary can not be updated or deleted while other records of the table can. Suppose the record i want not to be able to update or delete its salary is EMPNO = 7839, The trigger gets created but when i update any records in EMP table it gives me error that ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it, Can someone give me a solution for this? This is the code:

CREATE OR REPLACE TRIGGER PRACTICE_TRIGGER
BEFORE DELETE OR UPDATE OF SAL ON EMP
FOR EACH ROW
DECLARE
ROW_NUM NUMBER;
BEGIN
SELECT COUNT(*) INTO ROW_NUM FROM EMP WHERE EMPNO = 7839;
IF UPDATING('ROW_NUM') THEN
RAISE_APPLICATION_ERROR('-20000','CANT UPDATE/DELETE SALARY OF EMPNO = 7839');
END IF;
END PRACTICE_TRIGGER;
/
triggers plsql oracle
2 Answers

You can convert your code into this one :

CREATE OR REPLACE TRIGGER PRACTICE_TRIGGER
BEFORE DELETE OR UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN    
 IF :OLD.EMPNO = 7839 AND :OLD.SAL != NVL(:NEW.SAL,0) THEN
   RAISE_APPLICATION_ERROR('-20000','CAN''T UPDATE SALARY OR DELETE THE ROW FOR EMPNO = '||:OLD.EMPNO);
 END IF;
END;
/

Where

  • No query is not needed. Just new and old versions of the concerned SAL values should be equal for an employee in order to keep that value(7839) to be kept within the table. For DELETING case, the :NEW values for the columns will be NULL.
  • Those conditions are valid for both DELETING and UPDATING, so no need to repeat them within the code. But a column cannot be be deleted, deletion of the whole record will be the case
  • Repeating the trigger name at the end is optional, so might be removed.

Demo

user_58413060

For starters, your query is selecting the number of records, not the record identifier - it will never return "7839", only "1" or "0" for the number of records found. Also, you can't reference the table to which the trigger belongs from within the trigger (that's your mutating table error). Lastly, 'ROW_NUM' is not a column in your table, it is a variable in your trigger, so "IF UPDATING('ROW_NUM') would always be false, assuming it compiles at all.

The most basic form of what you're looking for would be this:

CREATE OR REPLACE TRIGGER PRACTICE_TRIGGER
BEFORE DELETE OR UPDATE OF SAL ON EMP
FOR EACH ROW
BEGIN
  -- check to see if record being updated is restricted, then raise error
  IF :OLD.EMPNO = 7839 THEN
    RAISE_APPLICATION_ERROR('-20000','CANT UPDATE/DELETE SALARY OF EMPNO = 7839');
  END IF;
END PRACTICE_TRIGGER;
/

That said, one obvious flaw in this approach is that the trigger as written doesn't prevent someone from changing the employee id, so theoretically if someone changed that first then the restriction on salary change would not work. A more effective approach would be a boolean column (true/false) that would identify locked records and a check to see if that flag was set. i would also recommend using a table API package to perform the actual DML operations rather than direct SQL commands, and avoid the use of triggers altogether if possible.

user_129134910
Related