OracleVillage

What is instead of trigger and where we can use it?

Normally the complex view is not updatable.
A complex view is a view that contains joins, group by expression, distinct keyword or group functions etc.
Consider a scenario where you want your complex view updatable, to make this happen we will use instead of trigger.
Lets create a view on EMP and DEPT table of SCOTT schema.

create or replace force view EMP_INFO

as
select e.empno
,e.ename
,d.deptno
,d.dname
,D.LOC
from emp e, dept d
where E.DEPTNO = D.DEPTNO;

Now lets create the instead of trigger. The idea behind the trigger is that we will insert data into DEPT and EMP tables when its a new record otherwise we will update the tables, so that it seems like we are performing DML on the view.

create or replace trigger instead_test
instead of insert or delete
on emp_info
referencing new as n old as o
for each row
declare
v_count number;
begin
if inserting
then
select count ( * )
into v_count
from dept
where deptno = :n.deptno;


if v_count = 0
then
insert into dept
values
(
:n.deptno, :n.dname, :n.loc
);
else
update dept
set loc = :n.loc;
end if;

select count ( * )
into v_count
from emp
where empno = :n.empno;

if v_count = 0
then
insert into emp
(
empno, ename, DEPTNO
)
values
(
:n.empno, :n.ename, :n.deptno
);
else
update emp
set ename = :n.ename, deptno = :n.deptno;
end if;

elsif deleting
then
delete from emp where empno=:o.empno;
end if;


end;
/

Ok, now try inserting the row...
insert into emp_info
values(1234,'USMAN',20,'RESEARCH','DALLAS');

similarly we can delete the row as

delete from emp_info where empno=1234;

How to call external procedure within trigger body?

The call statement can be used to call an external procedure within a trigger body. The external procedure called using the CALL statement can be either a PL/SQL stored procedure or a Java procedure. The statement will be execute if the condition associated with the trigger evaluates to true. The following code shows an example of trigger that uses the CALL statement to call the log_proc procedure:

Before creating the database trigger you must have ADMINISTER DATABASE TRIGGER privilege.

CONNECT system/manager
GRANT ADMINISTER DATABASE TRIGGER TO scott;
CONNECT scott/tiger

CREATE OR REPLACE TRIGGER test_logoff
BEFORE LOGOFF ON DATABASE
CALL log_proc
/

CREATE OR REPLACE PROCEDURE log_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_name,
'Not Allowed',
USER,
SYSDATE
FROM DUAL;

COMMIT;
END log_proc;
/
Example 2:
Assume you have defined a procedure salary_check, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON employees
FOR EACH ROW
WHEN (new.job_id <> 'AD_VP')
CALL check_sal(:new.job_id, :new.salary, :new.last_name);


The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action.
NOTE: A table is never considered mutating for STATEMENT triggers.

The trigger table itself is mutating table, as well as any table referencing it with FOREIGN KEY constraint. This restriction prevents a row trigger from seeing inconsistent set of data.

Example:
CREATE OR REPLACE TRIGGER check_sal
BEFORE INSERT OR UPDATE OF sal, job
ON emp
FOR EACH ROW
DECLARE
v_min_sal emp.sal%TYPE;
v_max_sal emp.sal%TYPE;
BEGIN
SELECT MIN (sal), MAX (sal)
INTO v_min_sal, v_max_sal
FROM emp
WHERE job = :new.job;

IF :new.sal < v_min_sal OR :new.sal > v_max_sal
THEN
raise_application_error (-20505, 'Out of Range');
END IF;
END;
/

Lets try to update EMP sal column...

update emp
set sal =1500
where ename ='SMITH';


We will show you the Mutating error work around in next post.

Constraining Table:
A constraining table is a table that the triggering event might need to read, either directly for a SQL statement or indirectly for a declarative referential integrity constraint.
NOTE: Tables are not considered constraining for STATEMENT triggers.

Example:
Try changing data in a constraining table.

When the value of DEPTNO changes in the DEPT parent table, trying to cascade the update to the corresponding rows in the EMP child table produces runtime error.

CREATE OR REPLACE TRIGGER cascade_updates
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
UPDATE emp
SET emp.deptno = :new.deptno
WHERE emp.deptno = :old.deptno;
END;

Now let’s try it.

UPDATE dept
SET deptno = 1
WHERE deptno = 20;

ORA-04091: table name is mutating, trigger/function may not see it.

In the above example, cascade_updates trigger tries to change the data in the constraining table, which is not allowed.