/*
INSTEAD OF触发器可以把视图的修改应用到视图的基表上。可以使用CREATE TRIGGER来创建语句触发器，语法如下
CREATE OR REPLACE TRIGGER
<触发器名>
INSTEAD OF
<触发事件>
ON
<表名>
*/
--用来测试INSTEAD OF触发器的脚本
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEES;
DROP TRIGGER TESTROWTRIGGER1;
DROP TRIGGER TESTROWTRIGGER2;
DROP VIEW V_EMPLOYEES;
CREATE TABLE DEPARTMENT(
DEPARTID NUMBER(3) NOT NULL UNIQUE,
DEPARTNAME VARCHAR2(20),
EMPNUM NUMBER(3)
);
CREATE TABLE EMPLOYEES(
EMPID NUMBER(3) NOT NULL,
EMPNAME VARCHAR2(20),
DEPARTID NUMBER(3) NOT NULL,
EMPAGE NUMBER(3)
);
--创建视图V_EMPLOYEES
CREATE VIEW V_EMPLOYEES
AS
SELECT A.DEPARTNAME,B.EMPID,B.EMPNAME,B.EMPAGE,A.DEPARTID
FROM DEPARTMENT A,EMPLOYEES B
WHERE A.DEPARTID=B.DEPARTID;
--创建一个触发器,目标：将视图的更新修改反应到实际的表中
CREATE OR REPLACE TRIGGER MYINSTEADOFTRIGGER
INSTEAD OF UPDATE ON V_EMPLOYEES
BEGIN
UPDATE DEPARTMENT T
SET DEPARTNAME = :NEW.DEPARTNAME,
DEPARTID = :NEW.DEPARTID
WHERE T.DEPARTID=:OLD.DEPARTID;
UPDATE EMPLOYEES T
SET T.EMPID = :NEW.EMPID,
T.EMPNAME = :NEW.EMPNAME,
T.EMPAGE = :NEW.EMPAGE
WHERE T.EMPID = :OLD.EMPID;
END;
/
--修改之前
SQL> SELECT * FROM DEPARTMENT A,EMPLOYEES B WHERE A.DEPARTID=B.DEPARTID;
DEPARTID DEPARTNAME EMPNUM EMPID EMPNAME DEPARTID EMPAGE
---------- -------------------- ---------- ---------- -------------------- ---------- ----------
4 JIA 3 1 CHENZW 4 28
4 JIA 3 1 CHENZW 4 28
4 JIA 3 1 CHENZW 4 28
--修改语句
SQL> update v_employees set departid = 5;
已更新3行。
--修改结果
SQL> select * from department;
DEPARTID DEPARTNAME EMPNUM
---------- -------------------- ----------
5 JIA 3
SQL> select * from employees;
EMPID EMPNAME DEPARTID EMPAGE
---------- -------------------- ---------- ----------
1 CHENZW 5 28
1 CHENZW 5 28
1 CHENZW 5 28
