Re: updatable view question

From: Cantonese Boy <waynewan_at_yahoo.com>
Date: Sun, 28 Oct 2001 21:23:10 +0800
Message-ID: <3BDC06BE.5540DBE5@yahoo.com>

Hi,

After I study more, I found that I maybe wrong to use updatable view in my case.
oracle said that I can use "instead of trigger" to update the view.

I copy the following sample from oracle doc and try it

CREATE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information FOR EACH ROW
BEGIN
IF NOT EXISTS SELECT * FROM emp
WHERE emp.empno = :n.empno
THEN
INSERT INTO emp
VALUES(:n.empno, :n.name);
ELSE
UPDATE emp SET emp.name = :n.name
WHERE emp.empno = :n.empno;
END IF;
IF NOT EXISTS SELECT * FROM dept
WHERE dept.deptno = :n.deptno
THEN
INSERT INTO dept
VALUES(:n.deptno, :n.dept_type);
ELSE
UPDATE dept SET dept.dept_type = :n.dept_type WHERE dept.deptno = :n.deptno;
END IF;
IF NOT EXISTS SELECT * FROM project
WHERE project.projno = :n.projno
THEN
INSERT INTO project
VALUES(:n.projno, :n.project_level);
ELSE
UPDATE project SET project.level = :n.level WHERE project.projno = :n.projno;
END IF;
END; However, I face the following error:

SQL> show error
Errors for TRIGGER EMP_DEPT_INSERT:

LINE/COL ERROR

---

---

2/16 PLS-00103: Encountered the symbol "SELECT" when expecting one of

         the following:
         (
         The symbol "(" was substituted for "SELECT" to continue.

3/1      PLS-00103: Encountered the symbol "THEN" when expecting one of
         the following:
         ) , and or
         The symbol ")" was substituted for "THEN" to continue.

Is it possible to use "not exists" in a if statement within the executable block?

Thanks

W. Received on Sun Oct 28 2001 - 07:23:10 CST