[sqlite] trigger in self-attached db
Hi, if I attach a database to itself the trigger in the second db seem not to work as expected (as I wrongly expected? .-): Does anyone no why and how I could work around this? sqlite3 adb .headers on create table a ( pk integer primary key ); create table b ( pk integer primary key, fk integer constraint fk_c references a (pk) on delete cascade on update cascade); .genfkey --exec insert into a values (1); insert into b values (1, 1); --test trigger update a set pk=2; -- ok select * from b; .exit sqlite3 adb .headers on attach database adb as bdb; --test trigger update bdb.a set pk=3; -- fails update a set pk=3; -- ok select * from bdb.b; select * from b; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger on an attached db.
On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik [EMAIL PROTECTED] wrote: Each connection has its own independent temp database. You've created a temporary trigger which exists in the temp database for your connection. The trigger simply doesn't exist on the other connection. damn ... Yes, if that other session attaches the appropriate database and creates an appropriate trigger. No, you cannot magically alter the behavior of another application you have no control over. I don't want to alter the behavior of the other app nor I want to alter his db. If I create mine table and trigger in the B db everything run smooth but I try to hook on some event without disturbing the original behavior. It's possible at all ? Tnx. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger on an attached db.
On 4/23/08, Federico Granata [EMAIL PROTECTED] wrote: On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik [EMAIL PROTECTED] wrote: Each connection has its own independent temp database. You've created a temporary trigger which exists in the temp database for your connection. The trigger simply doesn't exist on the other connection. damn ... Yes, if that other session attaches the appropriate database and creates an appropriate trigger. No, you cannot magically alter the behavior of another application you have no control over. I don't want to alter the behavior of the other app nor I want to alter his db. If I create mine table and trigger in the B db everything run smooth but I try to hook on some event without disturbing the original behavior. It's possible at all ? .. do it in your application rather than at the db level. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger on an attached db.
Doing it in my app means polling the other db instead of receive an interrupt via trigger ... I can do it but it's the polling vs interrupt ... Obviously I prefer to sit and wait for data instead of looping looking for data but if it's the only way ... -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). On Wed, Apr 23, 2008 at 3:01 PM, P Kishor [EMAIL PROTECTED] wrote: On 4/23/08, Federico Granata [EMAIL PROTECTED] wrote: On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik [EMAIL PROTECTED] wrote: Each connection has its own independent temp database. You've created a temporary trigger which exists in the temp database for your connection. The trigger simply doesn't exist on the other connection. damn ... Yes, if that other session attaches the appropriate database and creates an appropriate trigger. No, you cannot magically alter the behavior of another application you have no control over. I don't want to alter the behavior of the other app nor I want to alter his db. If I create mine table and trigger in the B db everything run smooth but I try to hook on some event without disturbing the original behavior. It's possible at all ? .. do it in your application rather than at the db level. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger on an attached db.
Hi, I hope this question isn't a noob one like my last one ... I have two db, the main one is used from mine sw (call this db A), the other is used from another sw (call this db B). I open A, attach B, create a temp trigger in A triggered by insert into a table in B and writing in a table in A If I insert into the table in B the trigger is triggered but if the sw (working on B) insert the same thing in the same table the trigger do nothing. db A CREATE TABLE original(id integer primary key, data text); db B attach database 'A.db' as A; CREATE TABLE sync(id integer primary key, original_id integer, flag integer); CREATE TRIGGER to_sync after insert on A.original begin insert into sync (original_id,flag) values(new.id,1); end; running from B sqlite insert into original(data) values(test); sqlite select * from original; 1|test sqlite select * from sync; 1|1|1 if I open another instance of sqlite3 for A running another insert sqlite insert into original (data) values(test2); in B I see sqlite select * from original; 1|test 2|test2 sqlite select * from sync; 1|1|1 It's possible to get the trigger run when insert is lunched from another session ? -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger on an attached db.
Federico Granata [EMAIL PROTECTED] wrote: I have two db, the main one is used from mine sw (call this db A), the other is used from another sw (call this db B). I open A, attach B, create a temp trigger in A triggered by insert into a table in B and writing in a table in A If I insert into the table in B the trigger is triggered but if the sw (working on B) insert the same thing in the same table the trigger do nothing. Each connection has its own independent temp database. You've created a temporary trigger which exists in the temp database for your connection. The trigger simply doesn't exist on the other connection. It's possible to get the trigger run when insert is lunched from another session ? Yes, if that other session attaches the appropriate database and creates an appropriate trigger. No, you cannot magically alter the behavior of another application you have no control over. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
