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(at)debis(dot)com (Jan Wieck) #