ストアド・プロシージャとトリガ-INSEAD OFトリガ

16145 ワード

/*
INSTEAD OF 。 CREATE TRIGGER , 
CREATE OR REPLACE TRIGGER 
INSTEAD OF  ON 
<pl>
*/</pl>
-- 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