db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Javier Fonseca V." <fonsecajav...@gmail.com>
Subject Re: DDL in Trigger Procedure
Date Tue, 21 Aug 2007 16:39:47 GMT
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 ...


On 8/21/07, derby@segel.com <derby@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.

View raw message