Oracle触发器5-Instead of触发器

Oracle触发器4- 数据库事件触发器

http://www.2cto.com/database/201304/201423. html

Instead of 触发器可以在由一个以上的表组成的视图上进行insert,update,delete,merge.也可以使用instead of 来创建分区表等。

创建instead of 触发器语法：

1 CREATE [OR REPLACE] TRIGGER trigger_name

2 INTEAD OF operation

3 ON view_name

4 FOR EACH ROW

5 BEGIN

6 ...code goes here...

7 END;

下面开始测试 Instead of insert,instead of update,instead of delete触发器以及嵌套表的instead of 触发器

1.创建测试表employee,permission_code,user_role

CREATE 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 VARCHAR2(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.向表中插入测试数据：

-- employee table

INSERT INTO employee

(employee_no, last_name, first_name, dept_code, active_flag)

VALUES

('5001', 'Mark', 'Townsend', 'LCR', 'Y');

INSERT INTO employee

(employee_no, last_name, first_name, dept_code, active_flag)

VALUES

('3996', 'Dacko', 'Carol', 'ESR', 'Y');

INSERT INTO employee

(employee_no, last_name, first_name, dept_code, active_flag)

VALUES

('6842', 'Morgan', 'Daniel', 'ADM', 'Y');

-- permission_code table data

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