--- Andreas <[EMAIL PROTECTED]> wrote:
> I'm trying to let a Trigger create a View.
...
> Is this doable at all and maybe quoting/escaping is the problem ? Or
> is it a goofy concept ?
It's goofy alright, but possible:
$ ./sqlite3 junk.db
SQLite version 3.5.2
Enter ".help" for instructions
sqlite> PRAGMA writable_schema=ON;
sqlite> .header on
sqlite> .mode csv
sqlite> CREATE TABLE foo(a,b,c);
sqlite> INSERT INTO "foo" VALUES(1,2,3);
sqlite> INSERT INTO "foo" VALUES(6,5,4);
sqlite> create view v1 as select c,a*1000,b from foo;
sqlite> select * from sqlite_master;
type,name,tbl_name,rootpage,sql
table,foo,foo,2,"CREATE TABLE foo(a,b,c)"
view,v1,v1,0,"CREATE VIEW v1 as select c,a*1000,b from foo"
sqlite> insert into sqlite_master values('view','v2','v2',0,'CREATE VIEW v2 as
select a*b ab, b*c
bc from foo');
sqlite> attach database "junk.db" as "db2";
sqlite> select * from db2.v2;
ab,bc
2,6
30,20
So creating a new view is just a matter of adding a new row to
sqlite_master. I haven't tried this in a trigger, but I think it
ought to work.
I couldn't think of a way to get the new view working in the same
connection, which is why I had to attach the same database again
in order to query the dynamically created view.
You could also disconnect and reconnect to achieve the same thing.
Maybe a custom sqlite function could be made to refresh the schema
on the same connection to avoid the need for a new connection.
____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how.
http://overview.mail.yahoo.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
