存储过程和触发器-INSTEAD OF触发器

/*
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