hsqldb v 1.9 and triggers

View: New views
8 Messages — Rating Filter:   Alert me  

hsqldb v 1.9 and triggers

by mathieu.estratat :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi,

I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got an error.

These two triggers are define on two different tables, on the same db.

Is it a known issue ? Did anyone success to have two or more trigger in this version ?

Thanks a lot,

Mathieu


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy.
It shouldn't be a propblem. Use different names for the triggers. If there is a problem, report on the bug tracker with details of how you create the triggers.
 
Fred
----- Original Message -----
Sent: 08 October 2009 10:23
Subject: [Hsqldb-user] hsqldb v 1.9 and triggers

Hi,

I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got an error.

These two triggers are define on two different tables, on the same db.

Is it a known issue ? Did anyone success to have two or more trigger in this version ?

Thanks a lot,

Mathieu


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference


_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by mathieu.estratat :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Thanks a lot Fred, I have the problem with the two triggers only in FILE mode But I have an other problem with triggers even ON NETWORK mode : Assume the following table and trigger : CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID VARCHAR(255),A_CHILD_ID VARCHAR(255),A_LAST_VERSION BOOLEAN) CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID Lets do a first insert, where the trigger has nothing to insert : insert into TABLE_A VALUES('1','1',null,null,true); The second insert should lead to the update of the first row insert into TABLE_A VALUES('2','1','1',null,true); But after this second insert the following SQL statement "select * from TABLE_A" returns : '1','1',null,null,true '2','1','1',null,true while we expect to have : '1','1',null,null,false '2','1','1',null,true What is really astonishing is that the following trigger : CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID = D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID = INSERTED2.A_ORIG_DID provides the expected result (for the child/parent relation): '1','1',null,2,true '2','1','1',null,true Do you have any idea why the second trigger provides the expected result while the first one do not ? Thanks a lot, Mathieu
Fred Toussi-2 wrote:
It shouldn't be a propblem. Use different names for the triggers. If there is a problem, report on the bug tracker with details of how you create the triggers. Fred ----- Original Message ----- From: Mathieu Estratat To: hsqldb-user@lists.sourceforge.net Sent: 08 October 2009 10:23 Subject: [Hsqldb-user] hsqldb v 1.9 and triggers Hi, I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got an error. These two triggers are define on two different tables, on the same db. Is it a known issue ? Did anyone success to have two or more trigger in this version ? Thanks a lot, Mathieu ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference ------------------------------------------------------------------------------ _______________________________________________ Hsqldb-user mailing list Hsqldb-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/hsqldb-user ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Hsqldb-user mailing list Hsqldb-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

1. Triggers should work the same way in file mode and network mode.
Please report the problem with an example of the triggers that work in
one mode and not in the other mode.
2. Triggers that modify the trigger table are not currently supported.
They should not be used even if they works in some situations. We will
probably support them in the next minor version.

Fred

On Mon, 16 Nov 2009 09:10 -0800, "mathieu.estratat"
<mestratat@...> wrote:

>
> Thanks a lot Fred,
> I have the problem with the two triggers only in FILE mode
>
> But I have an other problem with triggers even ON NETWORK mode :
>
> Assume the following table and trigger :
>
> CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY
> KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID VARCHAR(255),A_CHILD_ID
> VARCHAR(255),A_LAST_VERSION BOOLEAN)
>
> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET
> D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID
>
> Lets do a first insert, where the trigger has nothing to insert :
> insert into TABLE_A  VALUES('1','1',null,null,true);
>
> The second insert should lead to the update of the first row
> insert into TABLE_A  VALUES('2','1','1',null,true);
>
> But after this second insert the following SQL statement "select * from
> TABLE_A" returns :
> '1','1',null,null,true
> '2','1','1',null,true
>
> while we expect to have :
> '1','1',null,null,false
> '2','1','1',null,true
>
>
> What is really astonishing is that the following trigger :
> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET
> D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID =
> D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID =
> INSERTED2.A_ORIG_DID  
>
> provides the expected result (for the child/parent relation):
> '1','1',null,2,true
> '2','1','1',null,true
>
>
> Do you have any idea why the second trigger provides the expected result
> while the first one do not ?
>
> Thanks a lot,
>
> Mathieu
>
>
>
> Fred Toussi-2 wrote:
> >
> > It shouldn't be a propblem. Use different names for the triggers. If there
> > is a problem, report on the bug tracker with details of how you create the
> > triggers.
> >
> > Fred
> >   ----- Original Message -----
> >   From: Mathieu Estratat
> >   To: hsqldb-user@...
> >   Sent: 08 October 2009 10:23
> >   Subject: [Hsqldb-user] hsqldb v 1.9 and triggers
> >
> >
> >   Hi,
> >
> >   I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got an
> > error.
> >
> >   These two triggers are define on two different tables, on the same db.
> >
> >   Is it a known issue ? Did anyone success to have two or more trigger in
> > this version ?
> >
> >   Thanks a lot,
> >
> >   Mathieu
> >
> >
> >
> > ------------------------------------------------------------------------------
> >
> >
> >  
> > ------------------------------------------------------------------------------
> >   Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >   is the only developer event you need to attend this year. Jumpstart your
> >   developing skills, take BlackBerry mobile applications to market and
> > stay
> >   ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >   http://p.sf.net/sfu/devconference
> >
> >
> > ------------------------------------------------------------------------------
> >
> >
> >   _______________________________________________
> >   Hsqldb-user mailing list
> >   Hsqldb-user@...
> >   https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> > is the only developer event you need to attend this year. Jumpstart your
> > developing skills, take BlackBerry mobile applications to market and stay
> > ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26375659.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by mathieu.estratat :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Thanks a lot for your answer,

2. Does that mean that a trigger like "AFTER UPDATE ON TABLE_A" can not insert data on TABLE_A ?

Thanks again
Mathieu

Fred Toussi-2 wrote:
1. Triggers should work the same way in file mode and network mode.
Please report the problem with an example of the triggers that work in
one mode and not in the other mode.
2. Triggers that modify the trigger table are not currently supported.
They should not be used even if they works in some situations. We will
probably support them in the next minor version.

Fred

On Mon, 16 Nov 2009 09:10 -0800, "mathieu.estratat"
<mestratat@smart-trade.net> wrote:
>
> Thanks a lot Fred,
> I have the problem with the two triggers only in FILE mode
>
> But I have an other problem with triggers even ON NETWORK mode :
>
> Assume the following table and trigger :
>
> CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY
> KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID VARCHAR(255),A_CHILD_ID
> VARCHAR(255),A_LAST_VERSION BOOLEAN)
>
> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET
> D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID
>
> Lets do a first insert, where the trigger has nothing to insert :
> insert into TABLE_A  VALUES('1','1',null,null,true);
>
> The second insert should lead to the update of the first row
> insert into TABLE_A  VALUES('2','1','1',null,true);
>
> But after this second insert the following SQL statement "select * from
> TABLE_A" returns :
> '1','1',null,null,true
> '2','1','1',null,true
>
> while we expect to have :
> '1','1',null,null,false
> '2','1','1',null,true
>
>
> What is really astonishing is that the following trigger :
> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET
> D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID =
> D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID =
> INSERTED2.A_ORIG_DID  
>
> provides the expected result (for the child/parent relation):
> '1','1',null,2,true
> '2','1','1',null,true
>
>
> Do you have any idea why the second trigger provides the expected result
> while the first one do not ?
>
> Thanks a lot,
>
> Mathieu
>
>
>
> Fred Toussi-2 wrote:
> >
> > It shouldn't be a propblem. Use different names for the triggers. If there
> > is a problem, report on the bug tracker with details of how you create the
> > triggers.
> >
> > Fred
> >   ----- Original Message -----
> >   From: Mathieu Estratat
> >   To: hsqldb-user@lists.sourceforge.net
> >   Sent: 08 October 2009 10:23
> >   Subject: [Hsqldb-user] hsqldb v 1.9 and triggers
> >
> >
> >   Hi,
> >
> >   I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got an
> > error.
> >
> >   These two triggers are define on two different tables, on the same db.
> >
> >   Is it a known issue ? Did anyone success to have two or more trigger in
> > this version ?
> >
> >   Thanks a lot,
> >
> >   Mathieu
> >
> >
> >
> > ------------------------------------------------------------------------------
> >
> >
> >  
> > ------------------------------------------------------------------------------
> >   Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >   is the only developer event you need to attend this year. Jumpstart your
> >   developing skills, take BlackBerry mobile applications to market and
> > stay
> >   ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >   http://p.sf.net/sfu/devconference
> >
> >
> > ------------------------------------------------------------------------------
> >
> >
> >   _______________________________________________
> >   Hsqldb-user mailing list
> >   Hsqldb-user@lists.sourceforge.net
> >   https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> > is the only developer event you need to attend this year. Jumpstart your
> > developing skills, take BlackBerry mobile applications to market and stay
> > ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> > http://p.sf.net/sfu/devconference
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26375659.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

That's correct. Such a trigger can insert data into other tables only.

Fred

On Wed, 25 Nov 2009 06:06 -0800, "mathieu.estratat"
<mestratat@...> wrote:

>
> Thanks a lot for your answer,
>
> 2. Does that mean that a trigger like "AFTER UPDATE ON TABLE_A" can not
> insert data on TABLE_A ?
>
> Thanks again
> Mathieu
>
>
> Fred Toussi-2 wrote:
> >
> > 1. Triggers should work the same way in file mode and network mode.
> > Please report the problem with an example of the triggers that work in
> > one mode and not in the other mode.
> > 2. Triggers that modify the trigger table are not currently supported.
> > They should not be used even if they works in some situations. We will
> > probably support them in the next minor version.
> >
> > Fred
> >
> > On Mon, 16 Nov 2009 09:10 -0800, "mathieu.estratat"
> > <mestratat@...> wrote:
> >>
> >> Thanks a lot Fred,
> >> I have the problem with the two triggers only in FILE mode
> >>
> >> But I have an other problem with triggers even ON NETWORK mode :
> >>
> >> Assume the following table and trigger :
> >>
> >> CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY
> >> KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID VARCHAR(255),A_CHILD_ID
> >> VARCHAR(255),A_LAST_VERSION BOOLEAN)
> >>
> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID
> >>
> >> Lets do a first insert, where the trigger has nothing to insert :
> >> insert into TABLE_A  VALUES('1','1',null,null,true);
> >>
> >> The second insert should lead to the update of the first row
> >> insert into TABLE_A  VALUES('2','1','1',null,true);
> >>
> >> But after this second insert the following SQL statement "select * from
> >> TABLE_A" returns :
> >> '1','1',null,null,true
> >> '2','1','1',null,true
> >>
> >> while we expect to have :
> >> '1','1',null,null,false
> >> '2','1','1',null,true
> >>
> >>
> >> What is really astonishing is that the following trigger :
> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID =
> >> D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID =
> >> INSERTED2.A_ORIG_DID  
> >>
> >> provides the expected result (for the child/parent relation):
> >> '1','1',null,2,true
> >> '2','1','1',null,true
> >>
> >>
> >> Do you have any idea why the second trigger provides the expected result
> >> while the first one do not ?
> >>
> >> Thanks a lot,
> >>
> >> Mathieu
> >>
> >>
> >>
> >> Fred Toussi-2 wrote:
> >> >
> >> > It shouldn't be a propblem. Use different names for the triggers. If
> >> there
> >> > is a problem, report on the bug tracker with details of how you create
> >> the
> >> > triggers.
> >> >
> >> > Fred
> >> >   ----- Original Message -----
> >> >   From: Mathieu Estratat
> >> >   To: hsqldb-user@...
> >> >   Sent: 08 October 2009 10:23
> >> >   Subject: [Hsqldb-user] hsqldb v 1.9 and triggers
> >> >
> >> >
> >> >   Hi,
> >> >
> >> >   I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got
> >> an
> >> > error.
> >> >
> >> >   These two triggers are define on two different tables, on the same
> >> db.
> >> >
> >> >   Is it a known issue ? Did anyone success to have two or more trigger
> >> in
> >> > this version ?
> >> >
> >> >   Thanks a lot,
> >> >
> >> >   Mathieu
> >> >
> >> >
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> >
> >> >
> >> >  
> >> >
> >> ------------------------------------------------------------------------------
> >> >   Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >> >   is the only developer event you need to attend this year. Jumpstart
> >> your
> >> >   developing skills, take BlackBerry mobile applications to market and
> >> > stay
> >> >   ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >> >   http://p.sf.net/sfu/devconference
> >> >
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> >
> >> >
> >> >   _______________________________________________
> >> >   Hsqldb-user mailing list
> >> >   Hsqldb-user@...
> >> >   https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> > Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >> > is the only developer event you need to attend this year. Jumpstart
> >> your
> >> > developing skills, take BlackBerry mobile applications to market and
> >> stay
> >> > ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >> > http://p.sf.net/sfu/devconference
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > Hsqldb-user@...
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26375659.html
> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> >
> > ------------------------------------------------------------------------------
> > Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> > 30-Day
> > trial. Simplify your report design, integration and deployment - and focus
> > on
> > what you do best, core application coding. Discover what's new with
> > Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26513228.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by mathieu.estratat :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Ok thanks,

and what about the update ?
Is it possible to update rows in the TABLE_A in a trigger like "AFTER UPDATE ON TABLE_A" ?
Thanks


Fred Toussi-2 wrote:
That's correct. Such a trigger can insert data into other tables only.

Fred

On Wed, 25 Nov 2009 06:06 -0800, "mathieu.estratat"
<mestratat@smart-trade.net> wrote:
>
> Thanks a lot for your answer,
>
> 2. Does that mean that a trigger like "AFTER UPDATE ON TABLE_A" can not
> insert data on TABLE_A ?
>
> Thanks again
> Mathieu
>
>
> Fred Toussi-2 wrote:
> >
> > 1. Triggers should work the same way in file mode and network mode.
> > Please report the problem with an example of the triggers that work in
> > one mode and not in the other mode.
> > 2. Triggers that modify the trigger table are not currently supported.
> > They should not be used even if they works in some situations. We will
> > probably support them in the next minor version.
> >
> > Fred
> >
> > On Mon, 16 Nov 2009 09:10 -0800, "mathieu.estratat"
> > <mestratat@smart-trade.net> wrote:
> >>
> >> Thanks a lot Fred,
> >> I have the problem with the two triggers only in FILE mode
> >>
> >> But I have an other problem with triggers even ON NETWORK mode :
> >>
> >> Assume the following table and trigger :
> >>
> >> CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY
> >> KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID VARCHAR(255),A_CHILD_ID
> >> VARCHAR(255),A_LAST_VERSION BOOLEAN)
> >>
> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID
> >>
> >> Lets do a first insert, where the trigger has nothing to insert :
> >> insert into TABLE_A  VALUES('1','1',null,null,true);
> >>
> >> The second insert should lead to the update of the first row
> >> insert into TABLE_A  VALUES('2','1','1',null,true);
> >>
> >> But after this second insert the following SQL statement "select * from
> >> TABLE_A" returns :
> >> '1','1',null,null,true
> >> '2','1','1',null,true
> >>
> >> while we expect to have :
> >> '1','1',null,null,false
> >> '2','1','1',null,true
> >>
> >>
> >> What is really astonishing is that the following trigger :
> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID =
> >> D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID =
> >> INSERTED2.A_ORIG_DID  
> >>
> >> provides the expected result (for the child/parent relation):
> >> '1','1',null,2,true
> >> '2','1','1',null,true
> >>
> >>
> >> Do you have any idea why the second trigger provides the expected result
> >> while the first one do not ?
> >>
> >> Thanks a lot,
> >>
> >> Mathieu
> >>
> >>
> >>
> >> Fred Toussi-2 wrote:
> >> >
> >> > It shouldn't be a propblem. Use different names for the triggers. If
> >> there
> >> > is a problem, report on the bug tracker with details of how you create
> >> the
> >> > triggers.
> >> >
> >> > Fred
> >> >   ----- Original Message -----
> >> >   From: Mathieu Estratat
> >> >   To: hsqldb-user@lists.sourceforge.net
> >> >   Sent: 08 October 2009 10:23
> >> >   Subject: [Hsqldb-user] hsqldb v 1.9 and triggers
> >> >
> >> >
> >> >   Hi,
> >> >
> >> >   I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I got
> >> an
> >> > error.
> >> >
> >> >   These two triggers are define on two different tables, on the same
> >> db.
> >> >
> >> >   Is it a known issue ? Did anyone success to have two or more trigger
> >> in
> >> > this version ?
> >> >
> >> >   Thanks a lot,
> >> >
> >> >   Mathieu
> >> >
> >> >
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> >
> >> >
> >> >  
> >> >
> >> ------------------------------------------------------------------------------
> >> >   Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >> >   is the only developer event you need to attend this year. Jumpstart
> >> your
> >> >   developing skills, take BlackBerry mobile applications to market and
> >> > stay
> >> >   ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >> >   http://p.sf.net/sfu/devconference
> >> >
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> >
> >> >
> >> >   _______________________________________________
> >> >   Hsqldb-user mailing list
> >> >   Hsqldb-user@lists.sourceforge.net
> >> >   https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> > Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >> > is the only developer event you need to attend this year. Jumpstart
> >> your
> >> > developing skills, take BlackBerry mobile applications to market and
> >> stay
> >> > ahead of the curve. Join us from November 9 - 12, 2009. Register now!
> >> > http://p.sf.net/sfu/devconference
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > Hsqldb-user@lists.sourceforge.net
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26375659.html
> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> >
> > ------------------------------------------------------------------------------
> > Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> > 30-Day
> > trial. Simplify your report design, integration and deployment - and focus
> > on
> > what you do best, core application coding. Discover what's new with
> > Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26513228.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: hsqldb v 1.9 and triggers

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

No.

"Triggers that modify the trigger table" includes triggers that perform
insert,update or delete rows.

Fred

On Wed, 25 Nov 2009 09:26 -0800, "mathieu.estratat"
<mestratat@...> wrote:

>
> Ok thanks,
>
> and what about the update ?
> Is it possible to update rows in the TABLE_A in a trigger like "AFTER
> UPDATE
> ON TABLE_A" ?
> Thanks
>
>
>
> Fred Toussi-2 wrote:
> >
> > That's correct. Such a trigger can insert data into other tables only.
> >
> > Fred
> >
> > On Wed, 25 Nov 2009 06:06 -0800, "mathieu.estratat"
> > <mestratat@...> wrote:
> >>
> >> Thanks a lot for your answer,
> >>
> >> 2. Does that mean that a trigger like "AFTER UPDATE ON TABLE_A" can not
> >> insert data on TABLE_A ?
> >>
> >> Thanks again
> >> Mathieu
> >>
> >>
> >> Fred Toussi-2 wrote:
> >> >
> >> > 1. Triggers should work the same way in file mode and network mode.
> >> > Please report the problem with an example of the triggers that work in
> >> > one mode and not in the other mode.
> >> > 2. Triggers that modify the trigger table are not currently supported.
> >> > They should not be used even if they works in some situations. We will
> >> > probably support them in the next minor version.
> >> >
> >> > Fred
> >> >
> >> > On Mon, 16 Nov 2009 09:10 -0800, "mathieu.estratat"
> >> > <mestratat@...> wrote:
> >> >>
> >> >> Thanks a lot Fred,
> >> >> I have the problem with the two triggers only in FILE mode
> >> >>
> >> >> But I have an other problem with triggers even ON NETWORK mode :
> >> >>
> >> >> Assume the following table and trigger :
> >> >>
> >> >> CREATE MEMORY TABLE PUBLIC.TABLE_A(A_ID VARCHAR(255) NOT NULL PRIMARY
> >> >> KEY,A_ORIG_ID VARCHAR(255) NOT NULL,A_PARENT_ID
> >> VARCHAR(255),A_CHILD_ID
> >> >> VARCHAR(255),A_LAST_VERSION BOOLEAN)
> >> >>
> >> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> >> REFERENCING NEW ROW AS INSERTED FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> >> D1.A_LAST_VERSION = false where D1.A_ID = INSERTED.A_PARENT_ID
> >> >>
> >> >> Lets do a first insert, where the trigger has nothing to insert :
> >> >> insert into TABLE_A  VALUES('1','1',null,null,true);
> >> >>
> >> >> The second insert should lead to the update of the first row
> >> >> insert into TABLE_A  VALUES('2','1','1',null,true);
> >> >>
> >> >> But after this second insert the following SQL statement "select *
> >> from
> >> >> TABLE_A" returns :
> >> >> '1','1',null,null,true
> >> >> '2','1','1',null,true
> >> >>
> >> >> while we expect to have :
> >> >> '1','1',null,null,false
> >> >> '2','1','1',null,true
> >> >>
> >> >>
> >> >> What is really astonishing is that the following trigger :
> >> >> CREATE TRIGGER PUBLIC.CHECKACHILDREN AFTER INSERT ON PUBLIC.TABLE_A
> >> >> REFERENCING NEW ROW AS INSERTED2 FOR EACH ROW UPDATE TABLE_A AS D1 SET
> >> >> D1.A_CHILD_DID = (select D2.A_ID from TABLE_A AS D2 where D1.A_ID =
> >> >> D2.A_PARENT_DID AND D2.A_ORIG_DID = D1.A_ORIG_DID) where D1.A_ORIG_DID
> >> =
> >> >> INSERTED2.A_ORIG_DID  
> >> >>
> >> >> provides the expected result (for the child/parent relation):
> >> >> '1','1',null,2,true
> >> >> '2','1','1',null,true
> >> >>
> >> >>
> >> >> Do you have any idea why the second trigger provides the expected
> >> result
> >> >> while the first one do not ?
> >> >>
> >> >> Thanks a lot,
> >> >>
> >> >> Mathieu
> >> >>
> >> >>
> >> >>
> >> >> Fred Toussi-2 wrote:
> >> >> >
> >> >> > It shouldn't be a propblem. Use different names for the triggers. If
> >> >> there
> >> >> > is a problem, report on the bug tracker with details of how you
> >> create
> >> >> the
> >> >> > triggers.
> >> >> >
> >> >> > Fred
> >> >> >   ----- Original Message -----
> >> >> >   From: Mathieu Estratat
> >> >> >   To: hsqldb-user@...
> >> >> >   Sent: 08 October 2009 10:23
> >> >> >   Subject: [Hsqldb-user] hsqldb v 1.9 and triggers
> >> >> >
> >> >> >
> >> >> >   Hi,
> >> >> >
> >> >> >   I tried to use 2 triggers simultaneously on hsqldb v1.9 RC6 and I
> >> got
> >> >> an
> >> >> > error.
> >> >> >
> >> >> >   These two triggers are define on two different tables, on the same
> >> >> db.
> >> >> >
> >> >> >   Is it a known issue ? Did anyone success to have two or more
> >> trigger
> >> >> in
> >> >> > this version ?
> >> >> >
> >> >> >   Thanks a lot,
> >> >> >
> >> >> >   Mathieu
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> ------------------------------------------------------------------------------
> >> >> >
> >> >> >
> >> >> >  
> >> >> >
> >> >>
> >> ------------------------------------------------------------------------------
> >> >> >   Come build with us! The BlackBerry(R) Developer Conference in SF,
> >> CA
> >> >> >   is the only developer event you need to attend this year.
> >> Jumpstart
> >> >> your
> >> >> >   developing skills, take BlackBerry mobile applications to market
> >> and
> >> >> > stay
> >> >> >   ahead of the curve. Join us from November 9 - 12, 2009. Register
> >> now!
> >> >> >   http://p.sf.net/sfu/devconference
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> ------------------------------------------------------------------------------
> >> >> >
> >> >> >
> >> >> >   _______________________________________________
> >> >> >   Hsqldb-user mailing list
> >> >> >   Hsqldb-user@...
> >> >> >   https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> >
> >> >> >
> >> >>
> >> ------------------------------------------------------------------------------
> >> >> > Come build with us! The BlackBerry(R) Developer Conference in SF, CA
> >> >> > is the only developer event you need to attend this year. Jumpstart
> >> >> your
> >> >> > developing skills, take BlackBerry mobile applications to market and
> >> >> stay
> >> >> > ahead of the curve. Join us from November 9 - 12, 2009. Register
> >> now!
> >> >> > http://p.sf.net/sfu/devconference
> >> >> > _______________________________________________
> >> >> > Hsqldb-user mailing list
> >> >> > Hsqldb-user@...
> >> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >> >
> >> >> >
> >> >>
> >> >> --
> >> >> View this message in context:
> >> >>
> >> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26375659.html
> >> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> >> >
> >> >
> >> ------------------------------------------------------------------------------
> >> > Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> >> > 30-Day
> >> > trial. Simplify your report design, integration and deployment - and
> >> focus
> >> > on
> >> > what you do best, core application coding. Discover what's new with
> >> > Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> >> > _______________________________________________
> >> > Hsqldb-user mailing list
> >> > Hsqldb-user@...
> >> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26513228.html
> >> Sent from the HSQLDB - User mailing list archive at Nabble.com.
> >>
> >>
> >> ------------------------------------------------------------------------------
> >> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> >> 30-Day
> >> trial. Simplify your report design, integration and deployment - and
> >> focus on
> >> what you do best, core application coding. Discover what's new with
> >> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> >> _______________________________________________
> >> Hsqldb-user mailing list
> >> Hsqldb-user@...
> >> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> > ------------------------------------------------------------------------------
> > Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> > 30-Day
> > trial. Simplify your report design, integration and deployment - and focus
> > on
> > what you do best, core application coding. Discover what's new with
> > Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Hsqldb-user mailing list
> > Hsqldb-user@...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/hsqldb-v-1.9-and-triggers-tp25801613p26516866.html
> Sent from the HSQLDB - User mailing list archive at Nabble.com.
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Hsqldb-user mailing list
> Hsqldb-user@...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user