Re: Accessing transitional tables from trigger procedures
On 2021-08-13T13:26:47 -0700 Richard Hillegas 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 pgpRegJSB2Xw9.pgp Description: OpenPGP digital signature
Re: Accessing transitional tables from trigger procedures
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 < [email protected]> 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 > >
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 pgpf08clENHM4.pgp Description: OpenPGP digital signature
