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 Peter Bowman <pe...@hotmail.com> on 2006/04/09 06:37:12 UTC

Derby Trigger Query

Hi All,

I have a trigger like:

DROP TRIGGER RULE_10A;

CREATE TRIGGER RULE_10A
	AFTER INSERT ON PLAN_NAME_TABLE
	REFERENCING NEW AS NEWROW
	FOR EACH ROW MODE DB2SQL
	INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE, UNITS) 
VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g');


which inserts a new row into a table whenever a new row is inserted into 
another table, but is it possible to use a single trigger to insert several 
rows into the same table or do I have to write a separate trigger for each 
insert.

Thanks in advance,

Peter.

Re: Derby Trigger Query

Posted by Michelle Caisse <Mi...@Sun.COM>.
Make that a StackOverflow, as per DERBY-759.

-- Michelle

Michelle Caisse wrote:

> Well, Peter said the trigger was "like" the one he showed, so the 
> actual one may be more complex.  In any case, if these bugs are 
> relevant, there should be an OutOfMemoryException in the log.  If not, 
> they are not relevant.
>
> -- Michelle
>
> Daniel John Debrunner wrote:
>
>>Michelle Caisse wrote:
>>
>>  
>>
>>>See http://issues.apache.org/jira/browse/DERBY-443 and
>>>http://issues.apache.org/jira/browse/DERBY-759.  There is an issue
>>>handling OutOfMemoryExceptions that can result in this error.
>>>    
>>>
>>
>>
>>Not sure I see the connection with those bugs. I don't see how this
>>trigger would lead to an out of memory situation. Or is it because there
>>is a BLOB/CLOB in the base table of the trigger?
>>
>>Dan.
>>
>>
>>
>>  
>>
>

Re: Derby Trigger Query

Posted by Michelle Caisse <Mi...@Sun.COM>.
Well, Peter said the trigger was "like" the one he showed, so the actual 
one may be more complex.  In any case, if these bugs are relevant, there 
should be an OutOfMemoryException in the log.  If not, they are not 
relevant.

-- Michelle

Daniel John Debrunner wrote:

>Michelle Caisse wrote:
>
>  
>
>>See http://issues.apache.org/jira/browse/DERBY-443 and
>>http://issues.apache.org/jira/browse/DERBY-759.  There is an issue
>>handling OutOfMemoryExceptions that can result in this error.
>>    
>>
>
>
>Not sure I see the connection with those bugs. I don't see how this
>trigger would lead to an out of memory situation. Or is it because there
>is a BLOB/CLOB in the base table of the trigger?
>
>Dan.
>
>
>
>  
>

Re: Derby Trigger Query

Posted by Daniel John Debrunner <dj...@apache.org>.
Michelle Caisse wrote:

> See http://issues.apache.org/jira/browse/DERBY-443 and
> http://issues.apache.org/jira/browse/DERBY-759.  There is an issue
> handling OutOfMemoryExceptions that can result in this error.


Not sure I see the connection with those bugs. I don't see how this
trigger would lead to an out of memory situation. Or is it because there
is a BLOB/CLOB in the base table of the trigger?

Dan.

Re: Derby Trigger Query

Posted by Michelle Caisse <Mi...@Sun.COM>.
See http://issues.apache.org/jira/browse/DERBY-443 and 
http://issues.apache.org/jira/browse/DERBY-759.  There is an issue 
handling OutOfMemoryExceptions that can result in this error.

-- Michelle

Peter Bowman wrote:

> Hi Dan,
>
> Thanks for your input. Since I first posted I tried the solution you 
> suggested but I always got:
>
> "ERROR 40XT0: An internal error was identified by RawStore module."
>
> I've checked everything ove many times to ensure that my statement is 
> correct and I believe it is, so is there any other reason for this error.
>
> Thanks,
>
> Peter.
>
>> From: Daniel John Debrunner <dj...@apache.org>
>> Reply-To: "Derby Discussion" <de...@db.apache.org>
>> To: Derby Discussion <de...@db.apache.org>
>> Subject: Re: Derby Trigger Query
>> Date: Mon, 10 Apr 2006 10:40:21 -0700
>>
>> Peter Bowman wrote:
>>
>> > Hi All,
>> >
>> > I have a trigger like:
>> >
>> > DROP TRIGGER RULE_10A;
>> >
>> > CREATE TRIGGER RULE_10A
>> >     AFTER INSERT ON PLAN_NAME_TABLE
>> >     REFERENCING NEW AS NEWROW
>> >     FOR EACH ROW MODE DB2SQL
>> >     INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,
>> > UNITS) VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g');
>> >
>> >
>> > which inserts a new row into a table whenever a new row is inserted 
>> into
>> > another table, but is it possible to use a single trigger to insert
>> > several rows into the same table or do I have to write a separate
>> > trigger for each insert.
>>
>> No. The VALUES clause supports multiple rows.
>>
>> INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,UNITS)
>> VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g'),
>>        (NEWROW.PLAN, 'Marmite', 'Vitamin B', 'mg');
>>
>> See
>>
>> http://db.apache.org/derby/docs/10.1/ref/rrefsqlj11277.html
>>
>> Dan.
>>
>>
>>
>
>

Re: Derby Trigger Query

Posted by Peter Bowman <pe...@hotmail.com>.
Hi Dan,

Thanks for your input. Since I first posted I tried the solution you 
suggested but I always got:

"ERROR 40XT0: An internal error was identified by RawStore module."

I've checked everything ove many times to ensure that my statement is 
correct and I believe it is, so is there any other reason for this error.

Thanks,

Peter.

>From: Daniel John Debrunner <dj...@apache.org>
>Reply-To: "Derby Discussion" <de...@db.apache.org>
>To: Derby Discussion <de...@db.apache.org>
>Subject: Re: Derby Trigger Query
>Date: Mon, 10 Apr 2006 10:40:21 -0700
>
>Peter Bowman wrote:
>
> > Hi All,
> >
> > I have a trigger like:
> >
> > DROP TRIGGER RULE_10A;
> >
> > CREATE TRIGGER RULE_10A
> >     AFTER INSERT ON PLAN_NAME_TABLE
> >     REFERENCING NEW AS NEWROW
> >     FOR EACH ROW MODE DB2SQL
> >     INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,
> > UNITS) VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g');
> >
> >
> > which inserts a new row into a table whenever a new row is inserted into
> > another table, but is it possible to use a single trigger to insert
> > several rows into the same table or do I have to write a separate
> > trigger for each insert.
>
>No. The VALUES clause supports multiple rows.
>
>INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,UNITS)
>VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g'),
>        (NEWROW.PLAN, 'Marmite', 'Vitamin B', 'mg');
>
>See
>
>http://db.apache.org/derby/docs/10.1/ref/rrefsqlj11277.html
>
>Dan.
>
>
>

Re: Derby Trigger Query

Posted by Daniel John Debrunner <dj...@apache.org>.
Peter Bowman wrote:

> Hi All,
> 
> I have a trigger like:
> 
> DROP TRIGGER RULE_10A;
> 
> CREATE TRIGGER RULE_10A
>     AFTER INSERT ON PLAN_NAME_TABLE
>     REFERENCING NEW AS NEWROW
>     FOR EACH ROW MODE DB2SQL
>     INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,
> UNITS) VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g');
> 
> 
> which inserts a new row into a table whenever a new row is inserted into
> another table, but is it possible to use a single trigger to insert
> several rows into the same table or do I have to write a separate
> trigger for each insert.

No. The VALUES clause supports multiple rows.

INSERT INTO PLAN_NUTRIENT_TABLE (PLAN, NUTRIENT, NUTRIENT_TYPE,UNITS)
VALUES (NEWROW.PLAN, 'Total Fat', 'Fat', 'g'),
       (NEWROW.PLAN, 'Marmite', 'Vitamin B', 'mg');

See

http://db.apache.org/derby/docs/10.1/ref/rrefsqlj11277.html

Dan.