Oracle SQL beginner tutorials With exercise/ Database Basic to Advance Training - 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: https://www.youtube.com/watch?v=3v4s3XMw4q0&list=PLiLpmqwkwkCt0QeXD8j7BwIoOaBGBRrZC ------------------------------------------ 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-Coach-791404517889358/?modal=admin_todo_tour https://www.instagram.com/equalconnect/ https://twitter.com/LrnWthR