Re: Trouble with CREATE TRIGGER and transition tables

Conclusion:

It can't be done in 7.1. Here is what IBM wrote back last night.

Development confirmed this is a restriction in 7.1, lifted in 7.2. You
cannot have a multiple event trigger with OLD_TABLE or NEW_TABLE in 7.1.
That seems to have been missed in the 7.1 version of the SQL Reference
(since it was one of the few 7.1 differences). We have added the following
statement to the documentation for the next 7.1 refresh that will happen
this spring.

The complete set of rows affected by the triggering SQL operation is
available to the triggered-action by using a temporary table name specified
as follows. *These transition tables cannot be used for triggers that are
defined for more than one trigger-event.*
OLD TABLE AS table-identifier

Specifies the name of a temporary table that identifies the values in
the complete set of affected rows prior to the triggering SQL operation.
The OLD TABLE includes the rows that were affected by the trigger if
the current activation of the trigger was caused by statements in
the SQL-trigger-body of a trigger.
NEW TABLE AS table-identifier
Specifies the name of a temporary table that identifies the state of
the complete set of affected rows as modified by the triggering SQL
operation and by any SET statement in a before trigger that has already
been executed.

The type of message we can put out was limited when we add function in mid
release. Normally, a better message would have been issued.



Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 6 January 2015 at 14:14, Glenn Gundermann <glenn.gundermann@xxxxxxxxx>
wrote:

Hi Luis,

As I wrote, it works if I write them as separate triggers, which isn't
required when writing one with a correlation, such as this:

CREATE OR REPLACE TRIGGER hrithdrtrg

AFTER INSERT OR DELETE OR UPDATE OF icls, iret ON ipithdr

REFERENCING OLD ROW AS old_item

NEW ROW AS new_item

FOR EACH ROW MODE DB2ROW

BEGIN

IF INSERTING THEN

SIGNAL SQLSTATE '75000' ('Insert trigger'); -- use new_item

ELSEIF DELETING THEN

SIGNAL SQLSTATE '75000' ('Delete trigger'); -- use old_item

ELSEIF UPDATING THEN

SIGNAL SQLSTATE '75000' ('Update trigger'); -- use new_item

END IF;

END;


I've opened a PMR with IBM and we'll see what they say.

Thanks for your help!


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 6 January 2015 at 14:10, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

I wonder...

OLD_TABLE is defined as available only with DELETE/UPDATE triggers. What
happens if you define one trigger containing OLD_TABLE as DELETE OR UPDATE
and another trigger (INSERT) with only the NEW_TABLE statement?

Regards,

Luis


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--


On Tue, Jan 6, 2015 at 1:00 PM, Glenn Gundermann <
glenn.gundermann@xxxxxxxxx

wrote:

Hi Luis,

When specified as:

REFERENCING OLD AS oldtable
NEW AS newtable

it is implying:

REFERENCING OLD ROW AS oldtable
NEW ROW AS newtable

, which is used for defining a correlation.

I am trying to define transition tables.

What I have found through trial and error is that I can define an after
insert, update, or delete trigger using my syntax but not all three in

one,

which can be done using correlations. I wonder if this is a bug?

Example of code that completes successfully:

CREATE OR REPLACE TRIGGER hrithdrtrg_after_insert
AFTER INSERT ON ipithdr
REFERENCING NEW TABLE AS newtable
FOR EACH STATEMENT MODE DB2SQL
BEGIN
SIGNAL SQLSTATE '75000' ('Insert trigger');
END;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 6 January 2015 at 11:17, Luis Rodriguez <luisro58@xxxxxxxxx> wrote:

Glenn,

There is a slight syntax error (I think) in your code. The right

syntax

should be:

REFERENCING OLD AS oldtable
NEW AS newtable

Just drop the "TABLE" after OLD and/or NEW


HTH,

Luis

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--


On Tue, Jan 6, 2015 at 11:21 AM, Glenn Gundermann <
glenn.gundermann@xxxxxxxxx> wrote:

Hi Jim,

I have the same manual but I don't see your point. What am I

missing?

Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Cell: (416) 317-3144


On 5 January 2015 at 22:48, Jim It <jim_it@xxxxxxxxxxx> wrote:

Trying again.


The only options for Referencing in CREATE TRIGGER statement are

shown

below.

--+------------------------------------------------------------------+-->

|

.-------------------------------------------------. |

| V .-ROW-. .-AS-.

(1)

| |

'-REFERENCING----+-OLD--+-----+--+----+--correlation-name--+-----+-'

| .-ROW-. .-AS-. |
+-NEW--+-----+--+----+--correlation-name--+
| .-AS-. |
+-+-OLD TABLE-+--+----+--table-identifier-+
| '-OLD_TABLE-' |
| .-AS-. |
'-+-NEW TABLE-+--+----+--table-identifier-'
'-NEW_TABLE-'

Jim