Re: Trouble with CREATE TRIGGER and transition tables

On 05-Jan-2015 16:53 -0600, Glenn Gundermann wrote:

I've been able to create a trigger using mode DB2ROW but now I'm
trying to use transition tables.

Below is what I think is not a complex trigger but I'm getting an
error:
[SQL0104] Token OLD was not valid. Valid tokens: OLD.

If I change OLD to OLD_TABLE, I get:
[SQL0104] Token OLD_TABLE was not valid. Valid tokens: OLD.

My SWAG is that the parsing of the statement is getting confused due to the conditions\restrictions expressed in the msg SQL0696 of older releases [the KnowledgeCenter shows the message text is unchanged]; a possible deficiency in the multiple-event trigger support, for failing to enable specifications that since should no longer be restricted, when multiple events are being coded-for in the trigger.?

Perhaps a defect that was removed by a more recent DB fixpack or Cumulative than is currently applied [unstated release, TR, cum, and FP levels]. However, I could not find an APAR with the msg SQL0104 [-104] symptom for a CREATE TRIGGER statement [except an issue specific to use of the associated CCSID for the TRK language identifier].

CREATE OR REPLACE TRIGGER hrithdrtrg
AFTER INSERT
OR DELETE
OR UPDATE OF icls, iret
ON ipithdr
REFERENCING OLD TABLE AS oldtable
NEW TABLE AS newtable
FOR EACH STATEMENT MODE DB2SQL
BEGIN
IF INSERTING THEN -- use new_item
SIGNAL SQLSTATE '75000' ('Insert trigger');
ELSEIF DELETING THEN -- use old_item
SIGNAL SQLSTATE '75000' ('Delete trigger');
ELSEIF UPDATING THEN -- use new_item
SIGNAL SQLSTATE '75000' ('Update trigger');
END IF;
END;

Note: The final semicolon is not valid syntax for the CREATE TRIGGER. However that extraneous character may be deemed valid as a statement separator in a script processor that uses a semicolon as a statement separator.

Any help would be appreciated.

Perhaps the SQL code thinks, having parsed only up to that point, that something of the requested trigger event definitions implies and thus requires a row-trigger [FOR EACH ROW] vs a statement-trigger [FOR EACH STATEMENT], thus is enforcing a requirement that the REFERENCING clause should *not* be naming the correlation names for the transition tables and is expecting instead that the clause *must* name correlation names that identify the values in the row? That might explain the otherwise confusing suggested-as expected token of OLD when the actual token OLD was diagnosed as in-error; i.e. the OLD is expected as OLD [ROW], and either OLD TABLE or OLD_TABLE are deemed invalid. If so, then perhaps the error would not occur if either the "INSERT OR" were removed or the "OF icls, iret" for the UPDATE event were removed? And perhaps with just the former change, the issue might then simply migrate to the token NEW [instead of the token OLD].?

If either of those modifications cause the problem to disappear, my comments in no way intend to imply that the error is the expected effect or that the correlation for both row and transition tables are not allowed to be defined within the one trigger [because there seems to be no implication in the docs on any of those points]. Prior to multiple-event triggers, the use of AFTER UPDATE OF column-list allows specifying both correlations [row and transition table] with an UPDATE row-trigger. Prior to multi-event triggers, the SQL also enforced specification of correlation only for transition tables for a statement-trigger, and the NEW_TABLE was disallowed for a DELETE trigger event whereas the OLD_TABLE was disallowed for an INSERT trigger event [as diagnosed by the old -696].