Trigger syntax ? problem

Hi All,
I am not new to databases by to db2 indeed yes...and I am finding
significant procedural limitations in DB2 triggers when compared to
procedures...this becomes more limiting when you try tto migrate db
triggers from an existing db...anyway here is a trigger which I am
finding problems please help with synntax you can ...I am using db udb
8.1 on windows...

see the error message very down below
--------------------------------------------------------------

CREATE TRIGGER Trg_UpdFieldName AFTER UPDATE ON
ADE_RULE_PARAMETER_FIELDS
REFERENCING OLD AS DELETED NEW AS INSERTED FOR EACH STATEMENT MODE
DB2SQL

BEGIN ATOMIC

DECLARE v_Rule_Parameter_FieldName VARCHAR(120);

DECLARE v_Rule_Parameter_FieldID INTEGER;

DECLARE v_Rule_Parameter_FieldName_old VARCHAR(120);
DECLARE SQLSTATE varchar(5);

LOOP_LABEL: FOR curFOR AS cur CURSOR FOR SELECT
Rule_Parameter_FieldID,Rule_Parameter_FieldName FROM inserted
DO
FETCH FROM curFOR INTO
v_Rule_Parameter_FieldID,v_Rule_Parameter_FieldNam e;
IF SQLSTATE='00000' THEN
LEAVE LOOP_LABEL;
END IF;
SELECT Rule_Parameter_FieldName INTO
v_Rule_Parameter_FieldName_old FROM deleted WHERE
Rule_Parameter_FieldID = v_Rule_Parameter_FieldID FETCH FIRST 1 ROWS
ONLY ;
SET (v_Rule_Parameter_FieldName_old) = (SELECT
Rule_Parameter_FieldName FROM deleted WHERE Rule_Parameter_FieldID =
v_Rule_Parameter_FieldID FETCH FIRST 1 ROWS ONLY);
IF v_Rule_Parameter_FieldName_old <>
v_Rule_Parameter_FieldName THEN
UPDATE ADE_RULE_SEGMENT SET Rule_Parameter_FieldName =
v_Rule_Parameter_FieldName WHERE Rule_Parameter_FieldName =
v_Rule_Parameter_FieldName_old;
END IF;
END FOR
END;



problem--------------------



END FOR END

DB21034E The command was processed as an SQL statement because it was
not a

valid Command Line Processor command. During SQL processing it
returned:

SQL0104N An unexpected token "FOR" was found following "END ".
Expected

tokens may include: "JOIN <joined_table>". SQLSTATE=42601

re: Trigger syntax ? problem

After a couple of irerations.including the replacement of ; with ! in
Tools setting <g>..I simplified the above trigger to below...
now I get
[IBM][CLI Driver][DB2/NT] SQL0697N OLD or NEW correlation names
are not allowed in a trigger defined with the FOR EACH
STATEMENT clause. LINE NUMBER=1. SQLSTATE=42899

how can one interate the inserted table then if this is not supported ?


PS: see trigger below

-TIA

CREATE TRIGGER PAGARWAL.TRG_UPDFIELDNAME AFTER UPDATE ON
PAGARWAL.ADE_RULE_PARAMETER_FIELDS REFERENCING OLD AS deleted NEW AS
inserted FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC
FOR forRow AS SELECT
Rule_Parameter_FieldID,Rule_Parameter_FieldName FROM inserted
DO
Set (v_Rule_Parameter_FieldName_old) = (SELECT
Rule_Parameter_FieldName FROM deleted WHERE Rule_Parameter_FieldID =
forRow.Rule_Parameter_FieldID FETCH FIRST 1 ROWS ONLY );
IF v_Rule_Parameter_FieldName_old <>
forRow.Rule_Parameter_FieldName THEN
UPDATE ADE_RULE_SEGMENT SET Rule_Parameter_FieldName =
forRow.Rule_Parameter_FieldName WHERE Rule_Parameter_FieldName =
v_Rule_Parameter_FieldName_old;
END IF;
END FOR;
END!

re: Trigger syntax ? problem

Following worked

needed to replace OLD with OLD_TABLE, NEW with NEW_TABLE

CREATE TRIGGER PAGARWAL.TRG_UPDFIELDNAME AFTER UPDATE ON
PAGARWAL.ADE_RULE_PARAMETER_FIELDS REFERENCING OLD_TABLE AS deleted
NEW_TABLE AS inserted FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC
DECLARE v_Rule_Parameter_FieldName_old VARCHAR(120);
FOR forRow AS SELECT
Rule_Parameter_FieldID,Rule_Parameter_FieldName FROM inserted
DO
Set (v_Rule_Parameter_FieldName_old) = (SELECT
Rule_Parameter_FieldName FROM deleted WHERE Rule_Parameter_FieldID =
forRow.Rule_Parameter_FieldID FETCH FIRST 1 ROWS ONLY );
IF v_Rule_Parameter_FieldName_old <>
forRow.Rule_Parameter_FieldName THEN
UPDATE ADE_RULE_SEGMENT SET Rule_Parameter_FieldName =
forRow.Rule_Parameter_FieldName WHERE Rule_Parameter_FieldName =
v_Rule_Parameter_FieldName_old;
END IF;
END FOR;
END!

re: Trigger syntax ? problem

PA wrote:[color=blue]
> Following worked
>
> needed to replace OLD with OLD_TABLE, NEW with NEW_TABLE
>
> CREATE TRIGGER PAGARWAL.TRG_UPDFIELDNAME AFTER UPDATE ON
> PAGARWAL.ADE_RULE_PARAMETER_FIELDS REFERENCING OLD_TABLE AS deleted
> NEW_TABLE AS inserted FOR EACH STATEMENT MODE DB2SQL BEGIN ATOMIC
> DECLARE v_Rule_Parameter_FieldName_old VARCHAR(120);
> FOR forRow AS SELECT
> Rule_Parameter_FieldID,Rule_Parameter_FieldName FROM inserted
> DO
> Set (v_Rule_Parameter_FieldName_old) = (SELECT
> Rule_Parameter_FieldName FROM deleted WHERE Rule_Parameter_FieldID =
> forRow.Rule_Parameter_FieldID FETCH FIRST 1 ROWS ONLY );
> IF v_Rule_Parameter_FieldName_old <>
> forRow.Rule_Parameter_FieldName THEN
> UPDATE ADE_RULE_SEGMENT SET Rule_Parameter_FieldName =
> forRow.Rule_Parameter_FieldName WHERE Rule_Parameter_FieldName =
> v_Rule_Parameter_FieldName_old;
> END IF;
> END FOR;
> END!
>[/color]
It appears a row trigger owudl be more appropriate here:
CREATE TRIGGER PAGARWAL.TRG_UPDFIELDNAME AFTER UPDATE ON
PAGARWAL.ADE_RULE_PARAMETER_FIELDS REFERENCING OLD AS deleted
NEW AS inserted FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
WHEN (inserted.Rule_Parameter_FieldName <>
deleted.Rule_Parameter_FieldName)
UPDATE ADE_RULE_SEGMENT
SET Rule_Parameter_FieldName = inserted.Rule_Parameter_FieldName
WHERE Rule_Parameter_FieldName = deleted.Rule_Parameter_FieldName

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab