Oracle trigger 5-Instead of trigger

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.