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
