You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Javier Fonseca V." <fo...@gmail.com> on 2007/08/20 08:36:32 UTC
DDL in Trigger Procedure
Hello. I'm trying to do a trigger that fires after an INSERT INTO a table.
This trigger will call a Java procedure that performs some operations in the
Database ... and then decide to CREATE TABLE according to a sequence for
later local replication purposes.
But I can't make it work. It's failing in the CREATE TABLE step. It looks
like DDL is not supported in Derby Triggers, even if they are in a stored
procedure. If I call the procedure manually with some test values, it
works. But I need it to work it triggered in Derby. I already did it in
PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
same.
Thanks for your replies,
Javier Fonseca
Barranquilla, Colombia
Re: DDL in Trigger Procedure
Posted by "Javier Fonseca V." <fo...@gmail.com>.
That looks reasonable...
On 8/20/07, Francois Orsini <fr...@gmail.com> wrote:
>
> Javier,
>
> I guess and I may be wrong that the main reason for not allowing DDL
> operation in a trigger is due to the fact that a DDL operation will get
> implicitly committed, and as a trigger is always called in the context of a
> transaction, it cannot allow an implicit (in this case) commit of a DDL
> statement, neither an explicit commit...
>
> --francois
>
> On 8/20/07, Javier Fonseca V. <fo...@gmail.com> wrote:
> >
> > Yes. I agree with the importance of the footprint. I don't know if DDL
> > in Triggers would be too hard/big/convenient to implement, but the Derby
> > Team must have their reasons.
> >
> > Anyway, it looks like I'll have to call the procedure directly in the
> > application that I'm designing... that doesn't look so hard...
> >
> > Thanks for your replies.
> >
> > Javier
> >
> >
> >
> > On 8/20/07, derby@segel.com < derby@segel.com> wrote:
> > >
> > >
> > >
> > > Just a comment…
> > >
> > >
> > >
> > > Just because another database can do something doesn't mean that it's
> > > necessarily a good idea.
> > >
> > >
> > >
> > > The other issue with Derby is that until someone determines a way to
> > > create a "plug n play" architecture of features, it becomes more important
> > > to decide if Derby is going to be a "full fledged" database, or a
> > > lightweight embedded database.
> > >
> > >
> > >
> > > As you increase the size of the footprint, you make it harder to
> > > embed. And as you add features, you are increasing the size of the
> > > footprint.
> > >
> > >
> > >
> > >
> > > ------------------------------
> > >
> > > *From:* Francois Orsini [mailto: francois.orsini@gmail.com]
> > > *Sent:* Monday, August 20, 2007 5:26 AM
> > > *To:* Derby Discussion
> > > *Subject:* Re: DDL in Trigger Procedure
> > >
> > >
> > >
> > > Javier,
> > >
> > > DDL statements are not allowed in triggers, directly or via a
> > > procedure called from a trigger. I checked the codeline.
> > >
> > > --francois
> > >
> > > On 8/19/07, *Javier Fonseca V.* < fonsecajavier@gmail.com> wrote:
> > >
> > > Hello. I'm trying to do a trigger that fires after an INSERT INTO a
> > > table.
> > >
> > >
> > >
> > > This trigger will call a Java procedure that performs some operations
> > > in the Database ... and then decide to CREATE TABLE according to a
> > > sequence for later local replication purposes.
> > >
> > >
> > >
> > > But I can't make it work. It's failing in the CREATE TABLE step. It
> > > looks like DDL is not supported in Derby Triggers, even if they are in a
> > > stored procedure. If I call the procedure manually with some test values,
> > > it works. But I need it to work it triggered in Derby. I already did it in
> > > PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
> > > same.
> > >
> > >
> > >
> > > Thanks for your replies,
> > >
> > >
> > >
> > > Javier Fonseca
> > >
> > > Barranquilla, Colombia
> > >
> > >
> > >
> >
> >
>
Re: DDL in Trigger Procedure
Posted by "Javier Fonseca V." <fo...@gmail.com>.
My use case is:
There's a table in the DB. Let's call it MAINTABLE. It receives a lot of
insertions each day (about 3000 per day). And I just want to keep records
of the current day on MAINTABLE. So I'll have to clean the table daily.
But I need to keep all the other records for at least a month
(90.000records in a month). But only one table per day because I need
quick access
if I need the report of a specific day. So there's going to be one
MAINTABLE per day. Something like MAINTABLE_20070821.
It's a little bit more complicated than that, but that's the general idea.
That's why I need a trigger that call a procedure with DDL support. I need
to create MAINTABLE_20070821 if it doesn't exist.
But right now it looks that a Derby Trigger isn't going to be the way...
Like Francois pointed... there's some weird restriction in the code...
Maybe something for the development team?
Thanks for your time people ...
Javier
On 8/21/07, derby@segel.com <de...@segel.com> wrote:
>
>
> In general its not really a good idea.
>
> Use case(s)?
>
> I can think of one... if you had to have rolling partitions and you want
> to
> automate the process.
>
> Or if your database offers a system db, (Like IDS11 aka Cheetah) you may
> want to have a process that monitors the system and automate the fine
> tuning or maintenance.
>
> But this could be done by having the trigger call a procedure that calls
> an
> outside process... (in theory at least if your DB supports it.
>
> I would have to say, being paranoid, you'd have to make sure that the
> person
> who is triggering the trigger has the permissions to execute the DDL. So
> it
> gets a bit tricky and I'm not sure its worth the effort.
>
>
> > -----Original Message-----
> > From: Daniel John Debrunner [mailto:djd@apache.org]
> > Sent: Monday, August 20, 2007 11:43 AM
> > To: Derby Discussion
> > Subject: Re: DDL in Trigger Procedure
> >
> > Francois Orsini wrote:
> > > Javier,
> > >
> > > I guess and I may be wrong that the main reason for not allowing DDL
> > > operation in a trigger is due to the fact that a DDL operation will
> get
> > > implicitly committed,
> >
> > DDL is not implicitly committed in Derby.
> >
> > Dan.
>
>
>
RE: DDL in Trigger Procedure
Posted by de...@segel.com.
In general its not really a good idea.
Use case(s)?
I can think of one... if you had to have rolling partitions and you want to
automate the process.
Or if your database offers a system db, (Like IDS11 aka Cheetah) you may
want to have a process that monitors the system and automate the fine
tuning or maintenance.
But this could be done by having the trigger call a procedure that calls an
outside process... (in theory at least if your DB supports it.
I would have to say, being paranoid, you'd have to make sure that the person
who is triggering the trigger has the permissions to execute the DDL. So it
gets a bit tricky and I'm not sure its worth the effort.
> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Monday, August 20, 2007 11:43 AM
> To: Derby Discussion
> Subject: Re: DDL in Trigger Procedure
>
> Francois Orsini wrote:
> > Javier,
> >
> > I guess and I may be wrong that the main reason for not allowing DDL
> > operation in a trigger is due to the fact that a DDL operation will get
> > implicitly committed,
>
> DDL is not implicitly committed in Derby.
>
> Dan.
Re: DDL in Trigger Procedure
Posted by Francois Orsini <fr...@gmail.com>.
Sure. You can also add some context as well.
On 8/21/07, Javier Fonseca V. <fo...@gmail.com> wrote:
>
> Francois, do you think that I should ask to the Derby-Dev mail list?
>
> On 8/21/07, Francois Orsini <fr...@gmail.com> wrote:
> >
> > Thanks for catching this Dan.
> >
> > DDL in Transaction is supported as per the manual:
> > A schema manipulation statement (DDL) is not automatically committed
> > when it is performed, but participates in the transaction within which it is
> > issued.
> > http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#cdevconcepts19173
> >
> >
> > Then, I don't know why the restriction is there in the code to prevent
> > DDL in a trigger context. I checked the ANSI SQL-2003 specs and I haven't
> > seen (at least obvious) restrictions of schema manipulation/change statement
> > within some trigger action context. If anyone knows...
> >
> > The comments in the code are not saying much about the reason for the
> > restriction:
> >
> > java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
> >
> > /**
> > * Make sure that whatever statement is about to be executed
> > * is ok from the context of this trigger.
> > * <p>
> > * Note that we are sub classed in replication for checks
> > * for replication specific language.
> > *
> > * @param constantAction the constant action of the action
> > * that we are to validate
> > *
> > * @exception StandardException on error
> > */
> > public void validateStatement(ConstantAction constantAction) throws
> > StandardException
> > {
> >
> > // DDL statements are not allowed in triggers. Direct use of DDL
> >
> > // statements in a trigger's action statement is disallowed by
> > the
> > // parser. However, this runtime check is needed to prevent
> > execution
> > // of DDL statements by procedures within a trigger context.
> > if (constantAction instanceof DDLConstantAction) {
> > throw StandardException.newException(
> > SQLState.LANG_NO_DDL_IN_TRIGGER, triggerd.getName(),
> > constantAction.toString());
> > }
> >
> > // No INSERT/UPDATE/DELETE for a before trigger. There is no
> > need to
> > // check this here because parser does not allow these DML
> > statements
> > // in a trigger's action statement in a before trigger. Parser
> > also
> > // disallows creation of before triggers calling procedures
> > that modify
> > // SQL data.
> >
> > }
> >
> > --francois
> >
> > On 8/20/07, Daniel John Debrunner <djd@apache.org > wrote:
> > >
> > > Francois Orsini wrote:
> > > > Javier,
> > > >
> > > > I guess and I may be wrong that the main reason for not allowing DDL
> > >
> > > > operation in a trigger is due to the fact that a DDL operation will
> > > get
> > > > implicitly committed,
> > >
> > > DDL is not implicitly committed in Derby.
> > >
> > > Dan.
> > >
> >
> >
>
Re: DDL in Trigger Procedure
Posted by "Javier Fonseca V." <fo...@gmail.com>.
Francois, do you think that I should ask to the Derby-Dev mail list?
On 8/21/07, Francois Orsini <fr...@gmail.com> wrote:
>
> Thanks for catching this Dan.
>
> DDL in Transaction is supported as per the manual:
> A schema manipulation statement (DDL) is not automatically committed when
> it is performed, but participates in the transaction within which it is
> issued.
> http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#cdevconcepts19173
>
>
> Then, I don't know why the restriction is there in the code to prevent DDL
> in a trigger context. I checked the ANSI SQL-2003 specs and I haven't seen
> (at least obvious) restrictions of schema manipulation/change statement
> within some trigger action context. If anyone knows...
>
> The comments in the code are not saying much about the reason for the
> restriction:
>
> java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
>
> /**
> * Make sure that whatever statement is about to be executed
> * is ok from the context of this trigger.
> * <p>
> * Note that we are sub classed in replication for checks
> * for replication specific language.
> *
> * @param constantAction the constant action of the action
> * that we are to validate
> *
> * @exception StandardException on error
> */
> public void validateStatement(ConstantAction constantAction) throws
> StandardException
> {
>
> // DDL statements are not allowed in triggers. Direct use of DDL
> // statements in a trigger's action statement is disallowed by the
> // parser. However, this runtime check is needed to prevent
> execution
> // of DDL statements by procedures within a trigger context.
> if (constantAction instanceof DDLConstantAction) {
> throw StandardException.newException(
> SQLState.LANG_NO_DDL_IN_TRIGGER, triggerd.getName(),
> constantAction.toString());
> }
>
> // No INSERT/UPDATE/DELETE for a before trigger. There is no need
> to
> // check this here because parser does not allow these DML
> statements
> // in a trigger's action statement in a before trigger. Parser
> also
> // disallows creation of before triggers calling procedures that
> modify
> // SQL data.
>
> }
>
> --francois
>
> On 8/20/07, Daniel John Debrunner <dj...@apache.org> wrote:
> >
> > Francois Orsini wrote:
> > > Javier,
> > >
> > > I guess and I may be wrong that the main reason for not allowing DDL
> > > operation in a trigger is due to the fact that a DDL operation will
> > get
> > > implicitly committed,
> >
> > DDL is not implicitly committed in Derby.
> >
> > Dan.
> >
>
>
Re: DDL in Trigger Procedure
Posted by Francois Orsini <fr...@gmail.com>.
Thanks for catching this Dan.
DDL in Transaction is supported as per the manual:
A schema manipulation statement (DDL) is not automatically committed when it
is performed, but participates in the transaction within which it is issued.
http://db.apache.org/derby/docs/dev/devguide/devguide-single.html#cdevconcepts19173
Then, I don't know why the restriction is there in the code to prevent DDL
in a trigger context. I checked the ANSI SQL-2003 specs and I haven't seen
(at least obvious) restrictions of schema manipulation/change statement
within some trigger action context. If anyone knows...
The comments in the code are not saying much about the reason for the
restriction:
java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
/**
* Make sure that whatever statement is about to be executed
* is ok from the context of this trigger.
* <p>
* Note that we are sub classed in replication for checks
* for replication specific language.
*
* @param constantAction the constant action of the action
* that we are to validate
*
* @exception StandardException on error
*/
public void validateStatement(ConstantAction constantAction) throws
StandardException
{
// DDL statements are not allowed in triggers. Direct use of DDL
// statements in a trigger's action statement is disallowed by the
// parser. However, this runtime check is needed to prevent
execution
// of DDL statements by procedures within a trigger context.
if (constantAction instanceof DDLConstantAction) {
throw StandardException.newException(
SQLState.LANG_NO_DDL_IN_TRIGGER, triggerd.getName(), constantAction.toString
());
}
// No INSERT/UPDATE/DELETE for a before trigger. There is no need to
// check this here because parser does not allow these DML
statements
// in a trigger's action statement in a before trigger. Parser also
// disallows creation of before triggers calling procedures that
modify
// SQL data.
}
--francois
On 8/20/07, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Francois Orsini wrote:
> > Javier,
> >
> > I guess and I may be wrong that the main reason for not allowing DDL
> > operation in a trigger is due to the fact that a DDL operation will get
> > implicitly committed,
>
> DDL is not implicitly committed in Derby.
>
> Dan.
>
Re: DDL in Trigger Procedure
Posted by Daniel John Debrunner <dj...@apache.org>.
Francois Orsini wrote:
> Javier,
>
> I guess and I may be wrong that the main reason for not allowing DDL
> operation in a trigger is due to the fact that a DDL operation will get
> implicitly committed,
DDL is not implicitly committed in Derby.
Dan.
Re: DDL in Trigger Procedure
Posted by Francois Orsini <fr...@gmail.com>.
Javier,
I guess and I may be wrong that the main reason for not allowing DDL
operation in a trigger is due to the fact that a DDL operation will get
implicitly committed, and as a trigger is always called in the context of a
transaction, it cannot allow an implicit (in this case) commit of a DDL
statement, neither an explicit commit...
--francois
On 8/20/07, Javier Fonseca V. <fo...@gmail.com> wrote:
>
> Yes. I agree with the importance of the footprint. I don't know if DDL
> in Triggers would be too hard/big/convenient to implement, but the Derby
> Team must have their reasons.
>
> Anyway, it looks like I'll have to call the procedure directly in the
> application that I'm designing... that doesn't look so hard...
>
> Thanks for your replies.
>
> Javier
>
>
>
> On 8/20/07, derby@segel.com <de...@segel.com> wrote:
> >
> >
> >
> > Just a comment…
> >
> >
> >
> > Just because another database can do something doesn't mean that it's
> > necessarily a good idea.
> >
> >
> >
> > The other issue with Derby is that until someone determines a way to
> > create a "plug n play" architecture of features, it becomes more important
> > to decide if Derby is going to be a "full fledged" database, or a
> > lightweight embedded database.
> >
> >
> >
> > As you increase the size of the footprint, you make it harder to embed.
> > And as you add features, you are increasing the size of the footprint.
> >
> >
> >
> >
> > ------------------------------
> >
> > *From:* Francois Orsini [mailto: francois.orsini@gmail.com]
> > *Sent:* Monday, August 20, 2007 5:26 AM
> > *To:* Derby Discussion
> > *Subject:* Re: DDL in Trigger Procedure
> >
> >
> >
> > Javier,
> >
> > DDL statements are not allowed in triggers, directly or via a procedure
> > called from a trigger. I checked the codeline.
> >
> > --francois
> >
> > On 8/19/07, *Javier Fonseca V.* < fonsecajavier@gmail.com> wrote:
> >
> > Hello. I'm trying to do a trigger that fires after an INSERT INTO a
> > table.
> >
> >
> >
> > This trigger will call a Java procedure that performs some operations in
> > the Database ... and then decide to CREATE TABLE according to a sequence for
> > later local replication purposes.
> >
> >
> >
> > But I can't make it work. It's failing in the CREATE TABLE step. It
> > looks like DDL is not supported in Derby Triggers, even if they are in a
> > stored procedure. If I call the procedure manually with some test values,
> > it works. But I need it to work it triggered in Derby. I already did it in
> > PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
> > same.
> >
> >
> >
> > Thanks for your replies,
> >
> >
> >
> > Javier Fonseca
> >
> > Barranquilla, Colombia
> >
> >
> >
>
>
Re: DDL in Trigger Procedure
Posted by "Javier Fonseca V." <fo...@gmail.com>.
Yes. I agree with the importance of the footprint. I don't know if DDL in
Triggers would be too hard/big/convenient to implement, but the Derby
Team must have their reasons.
Anyway, it looks like I'll have to call the procedure directly in the
application that I'm designing... that doesn't look so hard...
Thanks for your replies.
Javier
On 8/20/07, derby@segel.com <de...@segel.com> wrote:
>
>
>
> Just a comment…
>
>
>
> Just because another database can do something doesn't mean that it's
> necessarily a good idea.
>
>
>
> The other issue with Derby is that until someone determines a way to
> create a "plug n play" architecture of features, it becomes more important
> to decide if Derby is going to be a "full fledged" database, or a
> lightweight embedded database.
>
>
>
> As you increase the size of the footprint, you make it harder to embed.
> And as you add features, you are increasing the size of the footprint.
>
>
>
>
> ------------------------------
>
> *From:* Francois Orsini [mailto:francois.orsini@gmail.com]
> *Sent:* Monday, August 20, 2007 5:26 AM
> *To:* Derby Discussion
> *Subject:* Re: DDL in Trigger Procedure
>
>
>
> Javier,
>
> DDL statements are not allowed in triggers, directly or via a procedure
> called from a trigger. I checked the codeline.
>
> --francois
>
> On 8/19/07, *Javier Fonseca V.* <fo...@gmail.com> wrote:
>
> Hello. I'm trying to do a trigger that fires after an INSERT INTO a
> table.
>
>
>
> This trigger will call a Java procedure that performs some operations in
> the Database ... and then decide to CREATE TABLE according to a sequence for
> later local replication purposes.
>
>
>
> But I can't make it work. It's failing in the CREATE TABLE step. It
> looks like DDL is not supported in Derby Triggers, even if they are in a
> stored procedure. If I call the procedure manually with some test values,
> it works. But I need it to work it triggered in Derby. I already did it in
> PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
> same.
>
>
>
> Thanks for your replies,
>
>
>
> Javier Fonseca
>
> Barranquilla, Colombia
>
>
>
Re: DDL in Trigger Procedure
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> As you increase the size of the footprint, you make it harder to embed.
> And as you add features, you are increasing the size of the footprint.
True.
Although, I was extremely happy to see that after a year of development,
Derby had hardly changed in size at all:
- 10.2: derby.jar was 2,250,000 bytes
- 10.3: derby.jar was 2,330,000 bytes
That's 3.5% growth in jar file size in 1 year.
thanks,
bryan
RE: DDL in Trigger Procedure
Posted by de...@segel.com.
Just a comment.
Just because another database can do something doesn't mean that it's
necessarily a good idea.
The other issue with Derby is that until someone determines a way to create
a "plug n play" architecture of features, it becomes more important to
decide if Derby is going to be a "full fledged" database, or a lightweight
embedded database.
As you increase the size of the footprint, you make it harder to embed. And
as you add features, you are increasing the size of the footprint.
_____
From: Francois Orsini [mailto:francois.orsini@gmail.com]
Sent: Monday, August 20, 2007 5:26 AM
To: Derby Discussion
Subject: Re: DDL in Trigger Procedure
Javier,
DDL statements are not allowed in triggers, directly or via a procedure
called from a trigger. I checked the codeline.
--francois
On 8/19/07, Javier Fonseca V. <fo...@gmail.com> wrote:
Hello. I'm trying to do a trigger that fires after an INSERT INTO a table.
This trigger will call a Java procedure that performs some operations in the
Database ... and then decide to CREATE TABLE according to a sequence for
later local replication purposes.
But I can't make it work. It's failing in the CREATE TABLE step. It looks
like DDL is not supported in Derby Triggers, even if they are in a stored
procedure. If I call the procedure manually with some test values, it
works. But I need it to work it triggered in Derby. I already did it in
PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
same.
Thanks for your replies,
Javier Fonseca
Barranquilla, Colombia
Re: DDL in Trigger Procedure
Posted by Francois Orsini <fr...@gmail.com>.
Javier,
DDL statements are not allowed in triggers, directly or via a procedure
called from a trigger. I checked the codeline.
--francois
On 8/19/07, Javier Fonseca V. <fo...@gmail.com> wrote:
>
> Hello. I'm trying to do a trigger that fires after an INSERT INTO a
> table.
>
> This trigger will call a Java procedure that performs some operations in
> the Database ... and then decide to CREATE TABLE according to a sequence for
> later local replication purposes.
>
> But I can't make it work. It's failing in the CREATE TABLE step. It
> looks like DDL is not supported in Derby Triggers, even if they are in a
> stored procedure. If I call the procedure manually with some test values,
> it works. But I need it to work it triggered in Derby. I already did it in
> PostgreSQL (master DB) and I don't know why Derby doesn't allow me to do the
> same.
>
> Thanks for your replies,
>
> Javier Fonseca
> Barranquilla, Colombia
>