Re: [HACKERS] about RULES

> > I tried your example Jan but it doesn't work...
> >
> > create rule "_INSvista" as on insert to vista
> >       do instead
> >       insert into emp select new.empno, new.ename, new.job
> >       where new.job='SALESMAN';
> > ERROR:  Type of empno does not match target column ename
> >
> > -Jose'-
>
> I had no reply to this message probably because a problem with my mail.

    Sorry,

    I  just  wrote  it  down quickly.  Of cause the insert action
    must be a valid statement.  In the above case I  assume,  the
    table  'emp'  didn't had the empno first. When looking at the
    schema of emp you gave below, the rule should read:

        create rule "_INSvista" as on insert to vista
          do instead
          insert into emp (empno, ename, job)
          values (new.empno, new.ename, new.job)
          where new.job = 'SALESMAN';

>
> I'm trying to make a view updatable using triggers, INSERT works fine
> but UPDATE/DELETE
> doesn't because the WHERE condition is evaluated FALSE all the time.
> Here an example:

    A trigger could never work for UPDATE or DELETE. A trigger is
    only  fired  when  there  is actually a row in a table to get
    updated or deleted.  Views  don't  (or  at  least  shouldn't)
    contain any data, so there is never a row to fire them.

>
> create table emp (
>         empno    int,
>         ename    char(10),
>         job      char(12),
>         hiredate date,
>         sal      money,
>         comm     int,
>         deptno   int,
>         level    int,
>         mgr      int
> );
> CREATE
>
> [...]
>
> -- The where condition is all the time evaluated as FALSE...
>
> delete from vista where ename='MANUEL'; --why this condition isn't true
> ?
> DELETE 0

    The  rule  system  redirected  the  scan  for the DELETE from
    'vista' to a scan from 'emp' because vista is a view on  emp.
    The  resulting  query  is a scan from emp who's result tupels
    should be deleted from vista - a whole  lot  of  nonsens  and
    thus absolutely nothing happens.

>
> delete from vista ;
> NOTICE:  trigger fired: BEFORE on DELETE
> DELETE 0
>
>
> --Is there a way to make views updatable ?

    Read  section  8  of the programmers manual to understand all
    the details of the rewrite  rule  system.  Then  do  it  with
    rules.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #