Re: updatable view question

"Cantonese Boy" <waynewan_at_yahoo.com> wrote in message news:3BDC06BE.5540DBE5_at_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.

It is not. You are mixing up pl/sql and sqlserver code. You still would need to use
begin
<some boolean> := true;
select <key column>
into ...
from <table>
where key_column = :new...
exception
when no_data_found then

   <some_boolean> := false;
end;

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Sun Oct 28 2001 - 07:52:11 CST