Can a Trigger do this ?

Sysdba AHE

Can a Trigger do this ?
I want to set a column on insert to a value retrieved from another table.
I want to do it without the overhead of either calling DB2 with a
preliminary SELECT to get the value, or the overhead of calling a stored
procedure, i.e. I want to use a Trigger pure and simple:-

CREATE TRIGGER WMTQRT01 NO CASCADE BEFORE INSERT
ON OS01.WMRQRT01
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
--
BEGIN ATOMIC
--
SET N.SMD_SEQ_NR =(SELECT SMD_SEQ_NR FROM OS01.WMRQHT01 H
WHERE H.SMD_MSG_ID = N.SMD_MSG_ID
AND H.SMH_IO_CD = 'I');

--
END#

DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
TRIGGERED
SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE
-104,
SQLSTATE 42601, AND MESSAGE TOKENS SMD_OBJ_ID,+ ) -

It seems I can't do this in a SET statement so apparently I can't do it in
a Trigger at all. Or is there a clever way of coding the trigger I haven't
thought of?


Trevor Davis
Database Designer
TNT Post Group




---------------------------------------------------------------------------------------------------------------
This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.
If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system.
If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
---------------------------------------------------------------------------------------------------------------



Peter Backlund

Re: Can a Trigger do this ?
(in response to Sysdba AHE)
This can be done in DB2 for Linux, UNIX, Windows

In DB2 for z/OS and OS/390 you have to use an after trigger as follows

CREATE TRIGGER WMTQRT01 AFTER INSERT
ON OS01.WMRQRT01
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
--
BEGIN ATOMIC
--
UPDATE OS01.WMRQRT01
SET SMD_SEQ_NR =(SELECT SMD_SEQ_NR FROM OS01.WMRQHT01 H
WHERE H.SMD_MSG_ID = N.SMD_MSG_ID
AND H.SMH_IO_CD = 'I')
WHERE keyvalue = N.keyvalue;
--
END#

You may have tu use a before trigger to temporarily set the SMD_SEQ_NR
to some valid number,
so the insert doesn't get rejected (duplicate, RI)

You could also set SMD_SEQ_NR tu NULL (if allowed) in the before trigger,
and the change the WHERE-clause in the after trigger to WHERE SMD_SEQ_NR
IS NULL

Best regards and a Happy New Year

Peter

Sysdba AHE wrote:

>I want to set a column on insert to a value retrieved from another table.
>I want to do it without the overhead of either calling DB2 with a
>preliminary SELECT to get the value, or the overhead of calling a stored
>procedure, i.e. I want to use a Trigger pure and simple:-
>
>CREATE TRIGGER WMTQRT01 NO CASCADE BEFORE INSERT
>ON OS01.WMRQRT01
>REFERENCING NEW AS N
>FOR EACH ROW MODE DB2SQL
>--
>BEGIN ATOMIC
>--
>SET N.SMD_SEQ_NR =(SELECT SMD_SEQ_NR FROM OS01.WMRQHT01 H
> WHERE H.SMD_MSG_ID = N.SMD_MSG_ID
> AND H.SMH_IO_CD = 'I');
>
>--
>END#
>
>DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A
>TRIGGERED
> SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE
>-104,
> SQLSTATE 42601, AND MESSAGE TOKENS SMD_OBJ_ID,+ ) -
>
>It seems I can't do this in a SET statement so apparently I can't do it in
>a Trigger at all. Or is there a clever way of coding the trigger I haven't
>thought of?
>
>
>Trevor Davis
>Database Designer
>TNT Post Group
>
>
>
>
>---------------------------------------------------------------------------------------------------------------
>This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.
>If you are not the intended recipient, please telephone or email the sender and delete this message and any attachment from your system.
>If you are not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
>---------------------------------------------------------------------------------------------------------------
>
>
>
>
>
>

--

=====> See you in October, 2003 at IDUG in Nice, France <======

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+



Steve Grimes

Re: Can a Trigger do this ?
(in response to Peter Backlund)
I think you are stuck too. A UDF is another option, but would incur (I
suppose) about the same overhead as an SP call. "Before" Triggers are the
ideal place to enforce some business logic, but the options are,
unfortunately, very limited.

Stg



Sibimon Philip

Re: Can a Trigger do this ?
(in response to Steve Grimes)
Do we pay only half the price for DB2 UDB on mainframe since only half the
feature available compared to UDB on other platform and other DBMS.

Thanks..sibi


-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]
Sent: Tuesday, December 31, 2002 11:51 AM
To: [login to unmask email]
Subject: Re: Can a Trigger do this ?


I think you are stuck too. A UDF is another option, but would incur (I
suppose) about the same overhead as an SP call. "Before" Triggers are the
ideal place to enforce some business logic, but the options are,
unfortunately, very limited.

Stg