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

最新推荐文章于 2020-11-27 11:55:35 发布

最新推荐文章于 2020-11-27 11:55:35 发布 · 102 阅读

本文通过创建视图及触发器展示了如何使用INSTEAD OF触发器将视图上的更新操作同步到基表中。示例包括了表结构定义、视图创建和触发器实现的具体SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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