By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

Trigger syntax ? problem

P: n/a
PA
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
PA
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!

Nov 12 '05 #2

P: n/a
PA
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!

Nov 12 '05 #3

P: n/a
PA wrote:
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!

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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.