473,320 Members | 1,974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Trigger syntax ? problem

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
3 1676
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Fernand St-Georges | last post by:
Hi, can someone tell how to write a Trigger; I am familiar with Sybase Sql Anywhere trigger syntax. Actually I have three tables MEMBER, CONTRACT and PAYMENT I need to update the MEMBER.BALANCE...
4
by: nosbtr1 | last post by:
When a row gets modified and it invokes a trigger, we would like to be able to update the row that was modified inside the trigger. This is (basically) how we are doing it now: CREATE TRIGGER...
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
12
by: Bob Stearns | last post by:
I am trying to create a duplicate prevention trigger: CREATE TRIGGER is3.ard_u_unique BEFORE UPDATE OF act_recov_date ON is3.flushes REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN...
5
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper?...
2
by: Juan Jose Costello Levien | last post by:
Hello, I am trying to use a trigger function I wrote in C. Basically what I want to do is to audit a table when a row is inserted into another table by copying the row to the new table. It...
1
by: deepdata | last post by:
Hi, I am creating a trigger in DB2 express version. When i use the following syntax to create trigger CREATE TRIGGER USER_PK_TRIGGER BEFORE INSERT On users REFERENCING NEW As N FOR EACH...
5
by: shethsheetal | last post by:
I am having some problem with the below mentioned trigger. CREATE TRIGGER D.TBA AFTER UPDATE OF TBAA.CIC ON TBAA REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC DECLARE RS...
4
zachster17
by: zachster17 | last post by:
Hello all, I have 2 tables (tblAddProviderProfessional and tblAddProvider). There are other tables such as tblAddProviderFacility and so forth. tblAddProvider is a table that has similar data...
6
by: Oliver | last post by:
I'm fairly new to DB2. I have been assigned to build a delete trigger that finds the data type of each of the table's fields so that the trigger can then build a string consisting of OLD values...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.