Can a Trigger do this ?

Mohammad Khan

Can a Trigger do this ?
Are you sure that the error is related to this code ? I don't see the error
token - SMD_OBJ_ID , anywhere in this code.


On Tue, 31 Dec 2002 08:37:12 +0000, Sysdba AHE <[login to unmask email]> 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.
>---------------------------------------------------------------------------
------------------------------------
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Suresh Sane

Re: Can a Trigger do this ?
(in response to Mohammad Khan)
Looks like SMD_OBJ_ID is an IDENTITY column?

In any case, how about changing to an AFTER trigger (instead of BEFORE)?
You can then insert the row and follow up with an update?

Thanks,
Suresh

>From: Mohammad Khan <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Can a Trigger do this ?
>Date: Thu, 2 Jan 2003 10:02:50 -0600
>
>Are you sure that the error is related to this code ? I don't see the error
>token - SMD_OBJ_ID , anywhere in this code.
>
>
>On Tue, 31 Dec 2002 08:37:12 +0000, Sysdba AHE <[login to unmask email]> 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.
> >---------------------------------------------------------------------------
>------------------------------------
> >
> >
> >
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can
>
>
>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can


_________________________________________________________________
MSN 8: advanced junk mail protection and 2 months FREE*.
http://join.msn.com/?page=features/junkmail



Roger Miller

Re: Can a Trigger do this ?
(in response to Suresh Sane)
Perhaps you are thinking about a specific implementation, and it's not
DB2 for z/OS & OS/390. Triggers do not provide a solution with lower
overhead than having the separate statement or a direct update with the
subselect.

Several of the options depend upon the version and service level you are
running.

Roger Miller

On Tue, 31 Dec 2002 08:37:12 +0000, Sysdba AHE <[login to unmask email]> 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.
>---------------------------------------------------------------------------