PLSQL Tutorial#21 Instead of trigger in oracle database
In this plsql tutorial we will learn Instead of trigger in oracle database
Oracle SQL Complete Tutorial/Training/Course for the beginner:
• Tutorial#1 Basics Of Oracle SQL for Beginn...
------------------------------------------
Instead of trigger theory
------------------------------------------
reate or replace view emp_dept_view
as select e.employee_id , e.employee_name , e.salary , d.department_name , d.department_id
from employees e , departments d
where e.department_id = d.department_id;
Views can't be updated in following of the case so that is the reason insteadof trigger has introduced
Set operators
Aggregate functions
GROUP BY, CONNECT BY, or START WITH clauses
The DISTINCT operator
Joins (however, some join views are updatable)
create or replace view emp_dept_view_1 as
select * from employees;
select e.employee_id , e.salary ,
d.department_name , d.location
from employees e , departments d
where e.department_id = d.department_id;
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF {INSERT | UPDATE | DELETE}
ON view_name
FOR EACH ROW
BEGIN
-- SQL queries
END;
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE
ON emp_dept_view
FOR EACH ROW enable
BEGIN
dbms_output.put_line('Hey I have truncated table'||:new.employee_id);
CASE
WHEN INSERTING THEN
Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION)
values (:new.department_id,:new.DEPARTMENT_NAME,:new.LOCATION);
Insert into EMPLOYEES (EMPLOYEE_ID,EMPLOYEE_NAME,JOB,MANAGER_ID,
HIREDATE,SALARY,COMMISSION,DEPARTMENT_ID)
values (:new.employee_id,:new.employee_name,'dummy',0,
to_date('20-FEB-81','DD-MON-RR'),:new.salary,0,
:new.department_id);
END CASE;
END;
insert into emp_dept_view (EMPLOYEE_ID,salary, DEPARTMENT_NAME,DEPARTMENT_ID,location)
values(123, 100 , 'IT dep' , 100 , 'DELHI');
follow me on:
Facebook Page:
https://www.facebook.com/Equalconnect...
/ equalconnect
/ lrnwthr