CREATE TRIGGER ON INSERT

[Andrea MF]

Hi all,
i have a problem when creating a trigger on DB2 ver 7.2 on WINDOWS.

I have two tables MOVIMAG and BARCODE, the trigge when INSERT INTO
MOVIMAG
will be UPDATE a MOVIMAG field BARCODE taken the value from the table
BARCODE.

I have write this but doesnt work
=============== =============== ============
CREATE TRIGGER DB2ADMIN.TRG_I_ BARCODE NO CASCADE BEFORE INSERT ON
DB2ADMIN.MOVIMA G REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE iBARCODE varchar(12);
DECLARE iCODICE varchar(12);

SET iCODICE = (SELECT CODICE FROM N);

SET iBARCODE = (SELECT BARCODE FROM DB2ADMIN.BARCOD E WHERE CODICE =
iCODICE);

UPDATE N SET BARCODE = iBARCODE;
END
=============== =============== ==============

ERROR:The db answer DB2ADMIN N undefined obj..

why?
tnx

[Serge Rielau]

Re: CREATE TRIGGER ON INSERT

Andrea MF wrote:

Hi all,
i have a problem when creating a trigger on DB2 ver 7.2 on WINDOWS.
>
I have two tables MOVIMAG and BARCODE, the trigge when INSERT INTO
MOVIMAG
will be UPDATE a MOVIMAG field BARCODE taken the value from the table
BARCODE.
>
I have write this but doesnt work
=============== =============== ============
CREATE TRIGGER DB2ADMIN.TRG_I_ BARCODE NO CASCADE BEFORE INSERT ON
DB2ADMIN.MOVIMA G REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE iBARCODE varchar(12);
DECLARE iCODICE varchar(12);
>
SET iCODICE = (SELECT CODICE FROM N);
>
SET iBARCODE = (SELECT BARCODE FROM DB2ADMIN.BARCOD E WHERE CODICE =
iCODICE);
>
UPDATE N SET BARCODE = iBARCODE;
END
=============== =============== ==============
>
ERROR:The db answer DB2ADMIN N undefined obj..

You need to SET the transition variable insetad of running an UPDATE.
Take a look at the info center. There should be plenty of before trigger
examples.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Andrea MF]

Re: CREATE TRIGGER ON INSERT

On 27 Giu, 13:50, Serge Rielau <srie...@ca.ibm .comwrote:

Andrea MF wrote:

Hi all,
i have a problem when creating a trigger on DB2 ver 7.2 on WINDOWS.

>

I have two tables MOVIMAG and BARCODE, the trigge when INSERT INTO
MOVIMAG
will be UPDATE a MOVIMAG field BARCODE taken the value from the table
BARCODE.

>

I have write this but doesnt work
=============== =============== ============
CREATE TRIGGER DB2ADMIN.TRG_I_ BARCODE NO CASCADE BEFORE  INSERT  ON
DB2ADMIN.MOVIMA G  REFERENCING  NEW AS N  FOR EACH ROW  MODE DB2SQL
BEGIN ATOMIC
DECLARE iBARCODE varchar(12);
DECLARE iCODICE varchar(12);

>

SET iCODICE = (SELECT CODICE FROM N);

>

SET iBARCODE = (SELECT BARCODE FROM DB2ADMIN.BARCOD E WHERE CODICE =
iCODICE);

>

UPDATE N SET BARCODE = iBARCODE;
END
=============== =============== ==============

>

ERROR:The db answer DB2ADMIN N undefined obj..

>
You need to SET the transition variable insetad of running an UPDATE.
Take a look at the info center. There should be plenty of before trigger
examples.
>
Cheers
Serge
>
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab- Nascondi testo citato
>
- Mostra testo citato

Tnk , Where is the Info Center ?!?

[Dave Hughes]

Re: CREATE TRIGGER ON INSERT

Andrea MF wrote:

On 27 Giu, 13:50, Serge Rielau <srie...@ca.ibm .comwrote:

Andrea MF wrote:

Hi all,
i have a problem when creating a trigger on DB2 ver 7.2 on
WINDOWS.

[snip]

>
Tnk , Where is the Info Center ?!?

The DB2 7 for LUW InfoCenter [1] contains a CREATE TRIGGER reference
[2] with several examples. Unfortunately none of them cover BEFORE
triggers, or setting transition variables. I'd recommend having a look
at Examples #5 and #6 in the CREATE TRIGGER reference [3] in the DB2
9.5 for LUW InfoCenter.

Basically, you want something like:

CREATE TRIGGER DB2ADMIN.TRG_I_ BARCODE
NO CASCADE BEFORE INSERT ON DB2ADMIN.MOVIMA G
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE iBARCODE varchar(12);
DECLARE iCODICE varchar(12);
SET iCODICE = N.CODICE;
SET iBARCODE = (SELECT BARCODE FROM DB2ADMIN.BARCOD E WHERE CODICE =
iCODICE);
SET N.BARCODE = iBARCODE;
END

Alternatively, this could be simplified to:

CREATE TRIGGER DB2ADMIN.TRG_I_ BARCODE
NO CASCADE BEFORE INSERT ON DB2ADMIN.MOVIMA G
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
SET N.BARCODE = (SELECT BARCODE FROM DB2ADMIN.BARCOD E WHERE CODICE =
N.CODICE);

[1] http://publib.boulder.ibm.com/infoce...7luw/index.jsp

[2]

http://publib.boulder.ibm.com/infocenter/db2v7luw/topic/com.ibm.db2v7.do

c/cpsqlrv1/ibmsqlr82.htm

[3]

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l

uw.sql.ref.doc/doc/r0000931.html


Cheers,

Dave.