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].
midrange.com
