ストアド・プロシージャとトリガ-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