Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik schrieb: >> You don't have a single row in dbapp view that has fields='surname'. >> The only record you have is one where fields='name,surname'. So no >> row matches condition, and thus no row gets deleted. Your trigger >> never even runs. > > That's weird. So Sqlite first does read from the view before actually > perform a DELETE? Of course. You seem to expect it to infer trigger parameters from the WHERE clause, but that is, in general, impossible. The WHERE clause could be arbitrarily complex (e.g. containing subselects or performing calculations on view fields). E.g. delete from dbapp where tablename||','||fields = 'employees,surname'; Or it may not have a WHERE clause at all: delete from dbapp; How would you expect this to work? > And how could I then implement what I wanted my view > to do? As far as I see, you can't. I guess it's back to the drawing board. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
Igor Tandetnik schrieb: > You don't have a single row in dbapp view that has fields='surname'. The > only record you have is one where fields='name,surname'. So no row > matches condition, and thus no row gets deleted. Your trigger never even > runs. That's weird. So Sqlite first does read from the view before actually perform a DELETE? And how could I then implement what I wanted my view to do? Thanks for your fast reply! ;) Greetings, Fabiano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > sqlite> select * from dbapp; > employees|name,surname > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> select * from dbapp; > employees|name,surname You don't have a single row in dbapp view that has fields='surname'. The only record you have is one where fields='name,surname'. So no row matches condition, and thus no row gets deleted. Your trigger never even runs. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
Ok, I've removed that nested DELETE command. Now I have still the problem
that I can't delete tablenames with multiple field entries. The full sql
I have is as follows:
---
create table dbapp_tablenames (
tablenameID integer primary key,
tablename text not null unique on conflict ignore
);
create table dbapp_tablefields (
tablenameID integer not null,
tablefield text not null
);
insert into dbapp_tablenames (tablename)
values ('employees');
insert into dbapp_tablenames (tablename)
values ('offices');
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'administration' from dbapp_tablenames where
tablename='offices';
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'name' from dbapp_tablenames where tablename='employees';
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'surname' from dbapp_tablenames where
tablename='employees';
create view dbapp as
select tablename,group_concat(tablefield) as fields
from dbapp_tablefields as fs
join dbapp_tablenames as ts on (ts.tablenameID=fs.tablenameID)
group by ts.tablenameID;
create trigger dbapp_insert instead of insert on dbapp begin
select raise(rollback, 'field already in table')
where (
select tablefield from dbapp_tablefields f
join dbapp_tablenames t on (f.tablenameID=t.tablenameID)
where tablefield=new.fields and t.tablename=new.tablename) is not null;
insert into dbapp_tablenames (tablename) values (new.tablename);
insert into dbapp_tablefields (tablenameID, tablefield)
select tablenameID,new.fields from dbapp_tablenames
where tablename=new.tablename;
end;
create trigger dbapp_delete instead of delete on dbapp begin
delete from dbapp_tablefields where tablefield=old.fields; /* HERE */
/*
where tablenameID=(
select tablenameID from dbapp_tablenames
where tablename=old.tablename)
and tablefield=old.fields;
*/
--where tablefield=old.fields;
delete from dbapp_tablenames
where tablename=old.tablename and not exists (
select * from dbapp_tablefields f
where f.tablenameID = dbapp_tablenames.tablenameID
and tablefield=old.fields);
end;
---
So consider the following output of this database:
---
sqlite> select * from dbapp;
employees|name,surname
offices|administration
sqlite> delete from dbapp where tablename='offices' and fields='administration';
sqlite> select * from dbapp;
employees|name,surname
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> select * from dbapp;
employees|name,surname
---
I don't get a clue how to find the error? Or is that indeed a Sqlite bug?
Thanks for answering...
Greetings,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > create trigger dbapp_delete instead of delete on dbapp begin > delete from dbapp_tablefields where tablenameID=( > select tablenameID from dbapp_tablenames > where tablename=old.tablename > and tablefield=old.tablefield); > > select case when (( > select tablefield from dbapp_tablefields f > join dbapp_tablenames t on (f.tablenameID=t.tablenameID) > where tablefield=old.fields and tablname=old.tablename) is null) > then (delete from dbapp_tablenames where tablename=old.tablename) > end; > > end; > --- > > But the trigger produces a syntax error at "delete" in the first line. The problem is with the DELETE statement you are trying to nest into a select statement (the second statement in the trigger). You can't do that - DELETE can only appear at the top level. You need something like this: delete from dbapp_tablenames where tablename=old.tablename and not exists ( select * from dbapp_tablefields f where f.tablenameID = dbapp_tablenames.tablenameID and tablefield=old.fields ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] delete on view
Hi folks! After having UPDATE and INSERT on a view working, I'd also like to DELETE FROM a view. I have the following tables and a view: --- create table dbapp_tablenames ( tablenameID integer primary key, tablename text not null unique on conflict ignore ); create table dbapp_tablefields ( tablenameID integer not null, tablefield text not null ); create view dbapp as select tablename,group_concat(tablefield) as fields from dbapp_tablefields as fs join dbapp_tablenames as ts on (ts.tablenameID=fs.tablenameID) group by ts.tablenameID; create trigger dbapp_delete instead of delete on dbapp begin delete from dbapp_tablefields where tablenameID=( select tablenameID from dbapp_tablenames where tablename=old.tablename and tablefield=old.tablefield); select case when (( select tablefield from dbapp_tablefields f join dbapp_tablenames t on (f.tablenameID=t.tablenameID) where tablefield=old.fields and tablname=old.tablename) is null) then (delete from dbapp_tablenames where tablename=old.tablename) end; end; --- But the trigger produces a syntax error at "delete" in the first line. What's wrong? Greetings, Fabiano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
