Oracle trigger 5-Instead of trigger

Oracle trigger 5-Instead of trigger Oracle trigger 4-database event trigger http://www.bkjia.com/database/201304/201423.html The Instead of trigger can be used to insert, update, delete, and merge tables in a view composed of more than one table. instead of can also be used to create partition tables. CREATE an instead of TRIGGER Syntax: 1 CREATE [or replace] TRIGGER trigger_name 2 intead of operation3 ON view_name4 for each ROW5 BEGIN6... code goes here... 7 END; Next we will test the Instead of insert, instead of update, instead of delete trigger and instead of trigger for nested tables. 1. create test TABLE employee, permission_code, user_roleCREATE TABLE employee (employee_no VARCHAR2 (8), last_name VARCHAR2 (25) not null, first_name VARCHAR2 (10) not null, dept_code VARCHAR2 (3) not null, active_flag VARCHAR2 (1) DEFAULT 'y', mod_user_id VARCHAR2 (30) default user, mod_user_date date default sysdate); create table permission_code (pcode VARCHAR2 (2 ), pcode_description VARCHAR2 (40) not null, mod_user_id VARCHAR2 (30) default user, mod_user_date date default sysdate); create table user_role (dept_code VARCHAR2 (3), pcode VARCHAR2 (2 ), access_level VARCHAR2 (1) DEFAULT 'R', mod_user_id VAR CHAR2 (30) default user, mod_user_date date default sysdate); create table user_permission (employee_no VARCHAR2 (8), pcode VARCHAR2 (2), access_level VARCHAR2 (1) DEFAULT 'R ', mod_user_id VARCHAR2 (30) default user, mod_user_date date default sysdate); create table dept_code (dept_code VARCHAR2 (3), dept_name VARCHAR2 (30 )); create table test (test VARCHAR2 (20); 2. insert test data to the table: -- employee tableINSERT INTO employee (Employee_no, last_name, first_name, dept_code, active_flag) VALUES ('20170101', 'mark', 'townsend', 'lcr ', 'y'); insert into employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('20140901', 'dacko', 'carol ', 'asc', 'y'); insert into employee (employee_no, last_name, first_name, dept_code, active_flag) VALUES ('000000', 'Morgan ', 'Daniel', 'adm ', 'y'); -- permission_code table dataINSERT Permission_code VALUES ('bo', 'billing options', USER, SYSDATE); insert into permission_code VALUES ('cl', 'class codes', USER, SYSDATE ); insert into permission_code VALUES ('cr ', 'crews', USER, SYSDATE); insert into permission_code VALUES ('ct ', 'crew TYPES', USER, SYSDATE ); insert into permission_code VALUES ('cu ', 'customer types', USER, SYSDATE); insert into permission_code VALUES ('dh', 'workorder Dash numbers ', USER, SYSDATE); insert into dept_code (dept_code, dept_name) VALUES ('adm', 'admin'); insert into dept_code (dept_code, dept_name) VALUES ('coo ', 'coordinator'); insert into dept_code (dept_code, dept_name) VALUES ('ese', 'Electrical Service'); insert into dept_code (dept_code, dept_name) VALUES ('asc', 'Electrical service rep '); insert into dept_code (dept_code, dept_name) VALUES ('Eng', 'engine'); insert into dept_code (dept_code, dept_name) VALUES ('lcr ', 'Line crew'); insert into dept_code (dept_code, dept_name) VALUES ('mcr', 'meter crew'); insert into dept_code (dept_code, dept_name) VALUES ('n', 'network engine'); insert into dept_code (dept_code, dept_name) VALUES ('sks', 'sketch artlist'); insert into user_role (dept_code, pcode, access_level) SELECT r. dept_code, p. pcode, 'R' FROM dept_code R, permission_code p; insert into user_permission (employee_no, pcode, access_level) SELECT e. employee_no, r. pcode, r. access_levelFROM employee e, user_role rWHERE e. dept_code = r. dept_code; COMMIT; 3. create role_permission_view and employee_permission_view and test whether View data can be updated or deleted. Create or replace view role_permission_view ASSELECT r. dept_code, r. pcode, p. pcode_description, r. access_levelFROM user_role r, permission_code pWHERE r. pcode = p. pcode; -- this will failINSERT INTO role_permission_view (dept_code, pcode, pcode_description, access_level) VALUES ('dan', 'dm', 'Morgan ', 'w '); -- this will fail tooUPDATE role_permission_viewSET access_level = 'W' WHERE dept_code = 'sks'; -- another relational viewCREATE or replace view employee_permission_view ASSELECT e. employee_no, e. first_name | ''| e. last_name NAME, e. dept_code, r. pcode, r. access_level DEFACCLVL, u. access_level, p. pcode_descriptionFROM employee e, user_role r, user_permission u, permission_code pWHERE e. dept_code = r. dept_codeAND e. employee_no = u. employee_noAND r. pcode = u. pcodeAND r. pcode = p. pcodeORDER BY 1, 3; -- this will fail tooDELETE FROM employee_permission_viewWHERE dept_code = 'lcr '; 4. example Of Instead OF Insert Trigger: create or replace trigger failed Of INSERTON role_permission_viewFOR EACH ROWDECLAREx INTEGER; beginselect count (*) INTO xFROM permission_codeWHERE pcode =: NEW. pcode; IF x = 0 theninsert into permission_code (pcode, pcode_description, mod_user_id, mod_user_date) VALUES (: NEW. pcode, 'new code', USER, SYSDATE); end if; select count (*) INTO xFROM dept_codeWHERE dept_code =: New. dept_code; IF x = 0 theninsert into dept_code (dept_code, dept_name) VALUES (: NEW. dept_code, 'new dept'); end if; insert into user_role (dept_code, pcode, mod_user_id) VALUES (: New. dept_code,: NEW. pcode, 'Morgan '); insert into test (test) VALUES ('Z'); END ioft_insert_role_perm;/SELECT * FROM permission_codeWHERE pcode = 'dm '; SELECT * FROM dept_codeWHERE dept_code = 'dan'; SELECT * FROM user_roleWHERE dept_code = 'dan'; SELECT * FROM test; -- insert worksINSERT INTO partition (dept_code, pcode, pcode_description, access_level) VALUES ('dan', 'dm', 'Morgan ', 'w'); -- view resultsSELECT * FROM permission_codeWHERE pcode = 'dm '; SELECT * FROM dept_codeWHERE dept_code = 'dan'; SELECT * FROM user_roleWHERE dept_code = 'dan'; SELECT * FROM test; 5. example Of Instead OF Update Trigger: create or replace trigger ioft_role_permINSTEAD Of UPDATEON role_permission_viewFOR each rowbeginupdate user_roleSET access_level =: NEW. access_level, mod_user_id = USER, mod_user_date = SYSDATEWHERE dept_code =: OLD. dept_codeAND permission_code =: OLD. permission_code; END partition;/SELECT trigger_name, trigger_type, action_type, descriptionFROM user_triggers; SELECT * FROM region; UPDATE into access_level = 'W' WHERE dept_code = 'sks'; SELECT * FROM region; UPDATE employee_permissionSET access_level = 'Z'; 6. example Of Instead OF Delete Trigger: SELECT * FROM employee_permission_view; SELECT * FROM dept_code; SELECT * FROM employee; create or replace trigger failed Of DELETEON into each rowbegindelete from dept_codeWHERE dept_code =: OLD. dept_code; UPDATE employeeSET dept_code = NULL, mod_user_id = USER, mod_user_date = SYSDATEWHERE dept_code =: OLD. dept_code; delete from testWHERE test = 'Z'; END ioft_emp_perm;/SELECT * FROM employee_permission_view; delete from role dept_code = 'lcr '; desc employeeDELETE FROM role dept_code = 'lcr '; 7. for example, conn scott/tigerCREATE or replace type emp_type as object (empno NUMBER (4), ename VARCHAR2 (10), job VARCHAR2 (9 ), mgr NUMBER (4), hiredate DATE, sal NUMBER (7, 2), comm NUMBER (7, 2);/create or replace type emp_tab_type as table of emp_type; /create or replace type dept_type as object (deptno NUMBER (2), dname VARCHAR2 (14), loc VARCHAR2 (13), emps emp_tab_type ); /create or replace view dept_or OF dept_typeWITH object identifier (deptno) ASSELECT deptno, dname, loc, CAST (MULTISET (SELECT empno, ename, job, mgr, hiredate, sal, commFROM empWHERE emp. deptno = dept. deptno) AS emp_tab_type) FROM dept;/create or replace trigger triggers of update on nested table emps OF dept_orREFERENCING new as new parent as parentfor each evaluate ('new: '|: New. job); dbms_output.put_line ('parent: '|: Parent. dname); END;/set serveroutput onUPDATE TABLE (SELECT p. empsFROM dept_or pWHERE deptno = 10) SET ename = LOWER (ename );