question about instead of delete triggers

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

question about instead of delete triggers

Mark de Vries
Hi,

I guess my question is: are conditions in the where clause
of a instead of delte trigger ignored if the referenced
columns are not in the where clause of the orig delete?

But perhaps an example of my 'problem':

create table item (
    id integer primary key,
    name text,
    catid integer
);

create table category (
    id integer primary key,
    name text
);

create view v_items as
    select i.name as item,c.name as cat
    from item i,category c
    where i.catid=c.id;

insert into category values (1,'good');
insert into category values (2,'bad');
insert into item values (1,'me',1);
insert into item values (2,'you',1);
insert into item values (3,'the rest',2);

Now I want to be able to delete using the view so I

create trigger del_v_items instead of delete on v_items
begin
    delete from item
    where name=old.item and
    catid=(select catid from category where name=old.cat);
end;

Now I can "delete from v_items where item='me'" or "delete from v_items
where cat='good'". Both seem to work as I would like it to, but I don't
quite understand why. In both cases I don't specify a value for a column
that is used in the where clause inside the trigger. Assuming and using
'NULL' would not work, so what does sqlite do? Just ignore those parts of
the where clause that it does not have all the values for?

Thnx for your time & Regards,
Mark.

Reply | Threaded
Open this post in threaded view
|

Re: question about instead of delete triggers

Dan Kennedy
When you execute this SQL: "delete from v_items where item='me'",
SQLite essentially does:

FOR EACH ROW IN "select <row> FROM v_items where item='me'" {

Execute trigger program

}

--- Mark de Vries <[hidden email]> wrote:

> Hi,
>
> I guess my question is: are conditions in the where clause
> of a instead of delte trigger ignored if the referenced
> columns are not in the where clause of the orig delete?
>
> But perhaps an example of my 'problem':
>
> create table item (
>     id integer primary key,
>     name text,
>     catid integer
> );
>
> create table category (
>     id integer primary key,
>     name text
> );
>
> create view v_items as
>     select i.name as item,c.name as cat
>     from item i,category c
>     where i.catid=c.id;
>
> insert into category values (1,'good');
> insert into category values (2,'bad');
> insert into item values (1,'me',1);
> insert into item values (2,'you',1);
> insert into item values (3,'the rest',2);
>
> Now I want to be able to delete using the view so I
>
> create trigger del_v_items instead of delete on v_items
> begin
>     delete from item
>     where name=old.item and
>     catid=(select catid from category where name=old.cat);
> end;
>
> Now I can "delete from v_items where item='me'" or "delete from v_items
> where cat='good'". Both seem to work as I would like it to, but I don't
> quite understand why. In both cases I don't specify a value for a column
> that is used in the where clause inside the trigger. Assuming and using
> 'NULL' would not work, so what does sqlite do? Just ignore those parts of
> the where clause that it does not have all the values for?
>
> Thnx for your time & Regards,
> Mark.
>
>



       
               
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
Reply | Threaded
Open this post in threaded view
|

Re: question about instead of delete triggers

Mark de Vries
On Sun, 30 Oct 2005, Dan Kennedy wrote:

> When you execute this SQL: "delete from v_items where item='me'",
> SQLite essentially does:
>
> FOR EACH ROW IN "select <row> FROM v_items where item='me'" {
>
> Execute trigger program
>
> }

That makes perfext sense. Thnx.

Rgds,
Mark.

> --- Mark de Vries <[hidden email]> wrote:
>
> > Hi,
> >
> > I guess my question is: are conditions in the where clause
> > of a instead of delte trigger ignored if the referenced
> > columns are not in the where clause of the orig delete?
> >
> > But perhaps an example of my 'problem':
> >
> > create table item (
> >     id integer primary key,
> >     name text,
> >     catid integer
> > );
> >
> > create table category (
> >     id integer primary key,
> >     name text
> > );
> >
> > create view v_items as
> >     select i.name as item,c.name as cat
> >     from item i,category c
> >     where i.catid=c.id;
> >
> > insert into category values (1,'good');
> > insert into category values (2,'bad');
> > insert into item values (1,'me',1);
> > insert into item values (2,'you',1);
> > insert into item values (3,'the rest',2);
> >
> > Now I want to be able to delete using the view so I
> >
> > create trigger del_v_items instead of delete on v_items
> > begin
> >     delete from item
> >     where name=old.item and
> >     catid=(select catid from category where name=old.cat);
> > end;
> >
> > Now I can "delete from v_items where item='me'" or "delete from v_items
> > where cat='good'". Both seem to work as I would like it to, but I don't
> > quite understand why. In both cases I don't specify a value for a column
> > that is used in the where clause inside the trigger. Assuming and using
> > 'NULL' would not work, so what does sqlite do? Just ignore those parts of
> > the where clause that it does not have all the values for?
> >
> > Thnx for your time & Regards,
> > Mark.
> >
> >
>
>
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>


Regards,
Mark