What is mutating trigger

 

 

 

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	02-Apr-81	2975		20
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7698	BLAKE	MANAGER	7839	01-May-81	2850		30
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20
7900	JAMES	CLERK	7698	03-Dec-81	950		30
7902	FORD	ANALYST	7566	03-Dec-81	3000		20
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10
7839	KING	PRESIDENT		17-Nov-81	5000		10
7934	MILLER	CLERK	7782	23-Jan-82	1300		10
9001	Manoj	CLERK		08-Nov-20	1000	450	40

 

 

CREATE OR REPLACE TRIGGER XX_EMP_B_U_TRIG 

BEFORE 

UPDATE 

--OF SAL 

ON 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;

 

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

LOG:- 

============ Row Level  - Before Update Trigger Fired ============

***** You Can assign this Salary  *****

============ Row Level  - Before Update Trigger Fired ============

*****  You can not assign this salary to anyone  *****

============ Row Level  - Before Update Trigger Fired ============

============ Row Level  - Before Update Trigger Fired ============

============ Row Level  - Before Update Trigger Fired ============