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 Mark Raynsford <li...@io7m.com> on 2021/08/13 18:02:21 UTC
Accessing transitional tables from trigger procedures
Hello!
Is it supposed to be possible to access the transitional tables
produced in triggers? For example, I need to write the following:
--
create trigger cardant.item_locations_enforce_counts_update_trigger
after update on cardant.item_locations
referencing old_table as new_item_locations
for each statement
call cardant.item_locations_enforce_counts ()
--
.. Where cardant.item_locations_enforce_counts is a JDBC/Java method
that tries to check the rows of new_item_locations. Unfortunately,
doing so just results in:
java.sql.SQLSyntaxErrorException: Table/View 'NEW_ITEM_LOCATIONS' does
not exist.
--
Mark Raynsford | https://www.io7m.com
Re: Accessing transitional tables from trigger procedures
Posted by Mark Raynsford <li...@io7m.com>.
On 2021-08-13T13:26:47 -0700
Richard Hillegas <ri...@gmail.com> wrote:
> Trigger transition tables can only be used for row-scoped triggers (FOR
> EACH ROW triggers). They cannot be used for statement-scoped triggers (FOR
> EACH STATEMENT triggers). I suppose that you could create a row-scoped
> trigger which populates a scratch table from the values of the transition
> table. Then you could create a statement-scoped trigger which fires a
> database procedure to process the scratch table.
Are you sure? The documentation seems to suggest otherwise:
https://db.apache.org/derby/docs/10.15/ref/rrefsqlj43125.html
"Note: Only statement triggers (see Statement versus row triggers) can
use the transition tables. INSERT statement triggers cannot reference
an OLD table. DELETE statement triggers cannot reference a NEW table."
--
Mark Raynsford | https://www.io7m.com
Re: Accessing transitional tables from trigger procedures
Posted by Richard Hillegas <ri...@gmail.com>.
Trigger transition tables can only be used for row-scoped triggers (FOR
EACH ROW triggers). They cannot be used for statement-scoped triggers (FOR
EACH STATEMENT triggers). I suppose that you could create a row-scoped
trigger which populates a scratch table from the values of the transition
table. Then you could create a statement-scoped trigger which fires a
database procedure to process the scratch table.
On Fri, Aug 13, 2021 at 11:02 AM Mark Raynsford <
list+org.apache.db.derby-user@io7m.com> wrote:
> Hello!
>
> Is it supposed to be possible to access the transitional tables
> produced in triggers? For example, I need to write the following:
>
> --
> create trigger cardant.item_locations_enforce_counts_update_trigger
> after update on cardant.item_locations
> referencing old_table as new_item_locations
> for each statement
> call cardant.item_locations_enforce_counts ()
> --
>
> .. Where cardant.item_locations_enforce_counts is a JDBC/Java method
> that tries to check the rows of new_item_locations. Unfortunately,
> doing so just results in:
>
> java.sql.SQLSyntaxErrorException: Table/View 'NEW_ITEM_LOCATIONS' does
> not exist.
>
> --
> Mark Raynsford | https://www.io7m.com
>
>