直接删除基表吧
把删除VIEW的命令分解成几个命令,去处理几个不同的基表。
把删除VIEW的命令分解成几个命令,去处理几个不同的基表。
解决方案 »
- 关于用创建用户登录问题
- Oracle触发器生成订单号,编译错误。解决者追加分数。
- 关于oracle 10g没有监听,提供具体的文件,请高手指点!
- Oracle 存储过程 关于 临时表返回结果集
- DB LINK是什么技术,如何使用它开发
- 大家好!本人刚开始学oracle感觉里面所提到的数据类型很多,如sql,pl/sql,oracle数据类型,可以说一下它们之间的关系吗?谢谢!
- 存储过程中打开一个Cursor,在未进行Fetch之前,怎样取到该Cursor的总行数?
- 谁有比较详细的建立快照复制的资料???????????????????????????????????
- 怎么在sql中删除主健?
- 修改时间请教
- 急!如何备份一个表空间,恢复一个表空间。
- oracle 9.2.0 在Redhat 7.3 下安装出错
instead of insert or update or delete on table_name
for each row
begin
if deleting then
.....
end if;
end;
/
如何启用INSTEAD-OF TRIGGERS?谢谢!
The INSTEAD OF option can also be used in triggers. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger performs UPDATE, INSERT, or DELETE operations directly on the underlying tables. You can write normal UPDATE, INSERT, and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. INSTEAD OF triggers can only be activated for each row. See Also:
"FOR EACH ROW Option"
Usage NotesWith Oracle8i release 8.1.5, INSTEAD OF triggers are only available with the Enterprise Edition. This may also be available in the Standard Edition in future releases. The INSTEAD OF option can only be used for triggers created over views. The BEFORE and AFTER options cannot be used for triggers created over views. The CHECK option for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check. Views That Are Not Modifiable
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs: Set operators Group functions GROUP BY, CONNECT BY, or START WITH clauses The DISTINCT operator Joins (a subset of join views are updatable) If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions. INSTEAD OF Trigger Example--------------------------------------------------------------------------------
Note:
You may need to set up the following data structures for this example to work: CREATE TABLE Project_tab (
Prj_level NUMBER,
Projno NUMBER,
Resp_dept NUMBER);
CREATE TABLE Emp_tab (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);
CREATE TABLE Dept_tab (
Deptno NUMBER(2) NOT NULL,
Dname VARCHAR2(14),
Loc VARCHAR2(13),
Mgr_no NUMBER,
Dept_type NUMBER);
--------------------------------------------------------------------------------
The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view. CREATE OR REPLACE VIEW manager_info AS
SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,
p.projno
FROM Emp_tab e, Dept_tab d, Project_tab p
WHERE e.empno = d.mgr_no
AND d.deptno = p.resp_dept;CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager informationFOR EACH ROW
DECLARE
rowcnt number;
BEGIN
SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno;
IF rowcnt = 0 THEN
INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename);
ELSE
UPDATE Emp_tab SET Emp_tab.ename = :n.ename
WHERE Emp_tab.empno = :n.empno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno;
IF rowcnt = 0 THEN
INSERT INTO Dept_tab (deptno, dept_type)
VALUES(:n.deptno, :n.dept_type);
ELSE
UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type
WHERE Dept_tab.deptno = :n.deptno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM Project_tab
WHERE Project_tab.projno = :n.projno;
IF rowcnt = 0 THEN
INSERT INTO Project_tab (projno, prj_level)
VALUES(:n.projno, :n.prj_level);
ELSE
UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
WHERE Project_tab.projno = :n.projno;
END IF;
END;