How to Solve Mutating Trigger by using Statement Level trigger

How to Solve Mutating Trigger

 

Mutating ERROR TRIGGER code :-

 

CREATE OR REPLACE TRIGGER APPS.XX_EMP_B_U_TRIG 

BEFORE 

UPDATE 

--OF SAL 

ON APPS.XX_EMP

--REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

DECLARE

V_PRESIDENT_SAL NUMBER;

 

BEGIN    

dbms_output.put_line ('============ Row Level  - Before Update Trigger Fired ============');

 

select SAL INTO V_PRESIDENT_SAL  from xx_emp where JOB= 'PRESIDENT';

 

 

    IF :NEW.SAL >= V_PRESIDENT_SAL  THEN

 

    dbms_output.put_line ('*****  You can not assign this salary to anyone  *****');

    RAISE_APPLICATION_ERROR(-20100,'You can not assign this salary to anyone');

 

    ELSE

    dbms_output.put_line ('***** You Can assign this Salary  *****');

    END IF;

  

   

END;

 

 

--===========================================================--

Solution

Code:1:

CREATE OR REPLACE TRIGGER APPS.XX_EMP_B_U_S_TRIG 

BEFORE  UPDATE 

--OF SAL 

ON APPS.XX_EMP

 

DECLARE

--V_PRESIDENT_SAL NUMBER;

 

BEGIN    

dbms_output.put_line ('============ Statement Level  - Before Update Trigger Fired ============');

 

--select SAL INTO V_PRESIDENT_SAL  from xx_emp where JOB= 'PRESIDENT';

 

select SAL INTO   XX_PKG_TRIG_VARIABLE.VAR_PRESIDENT_SALARY  from xx_emp where JOB= 'PRESIDENT';

 

dbms_output.put_line ('PRESIDENT  SALARY fetched : '||XX_PKG_TRIG_VARIABLE.VAR_PRESIDENT_SALARY);

 

END;

 

 

CODE:2:

 

 

 

Create or Replace Package XX_PKG_TRIG_VARIABLE AS

 

VAR_PRESIDENT_SALARY NUMBER;

 

END;

 

 

CODE:3:

 

CREATE OR REPLACE TRIGGER APPS.XX_EMP_B_U_TRIG 

BEFORE 

UPDATE 

--OF SAL 

ON APPS.XX_EMP

--REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

DECLARE

V_PRESIDENT_SAL NUMBER;

 

BEGIN    

dbms_output.put_line ('============ Row Level  - Before Update Trigger Fired ============');

 

 

    IF :NEW.SAL >= XX_PKG_TRIG_VARIABLE.VAR_PRESIDENT_SALARY   THEN

 

    dbms_output.put_line ('*****  You can not assign this salary to anyone  *****');

    RAISE_APPLICATION_ERROR(-20100,'You can not assign this salary to anyone');

 

    ELSE

    dbms_output.put_line ('***** You Can assign this Salary  *****');

    END IF;

  

   

END;

/