[sqlite] trigger in self-attached db

[Jan]

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.

[Federico Granata]

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]
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

Re: [sqlite] Trigger on an attached db.

[P Kishor]

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.

[Federico Granata]

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.

[Igor Tandetnik]

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

[sqlite] Trigger on an attached db.

[Federico Granata]

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]
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