A few points :

1) For Procedural SQL Statement, the statement terminator cannot be ; . ; is
used as a statement terminator for statements within the SQL/PL .. I'm
using [Email address protected] as a statement terminator of the CREATE TRIGGER .. Eg.
CREATE TRIGGER SAL_ADJ
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING OLD AS OLD_EMP
NEW AS NEW_EMP
FOR EACH ROW MODE DB2SQL
WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY * 1.20))
BEGIN ATOMIC
SIGNAL SQLSTATE '75001' ('Invalid Salary Increase - Exceeds
20%');
END

[Email address protected]

2) You cannot use INSERT, UPDATE or DELETE in BEFORE TRIGGERS.

3) Let us know what your requirement, and someone may come up with an idea.

If my understanding is correct, you are trying to set the fielname in the
new record to null, if your table has a row with the fieldname of 0.
In this case, use
CREATE TRIGGER TRIGGER_NAME
NO CASCADE BEFORE INSERT ON SAMPTAB
REFERENCING NEW AS REC
FOR EACH ROW MODE DB2SQL
WHEN(EXISTS (SELECT 1 FROM SAMPTAB WHERE I=1))

SET REC.J= NULL
[Email address protected]


Thanks


Sathyaram Sannasi
www.db2click.com
http://www.idug.org/forums/UserForumHome.asp


-----Original Message-----
From: Pedro Miguel Gonçalves
[Email address protected]
Sent: 20 May 2004 12:33
To: LazyDBA Discussion
Subject: RE: PROBLEMS IN A TRIGGER


Thanks but it's not it...

Keeps getting me SQL = -199 even with REC.field_name = 0;

Any suggestions?

Pedro Gonçalves


-----Mensagem original-----
De: Wilson Allan [Email address protected]
Enviada: quinta-feira, 20 de Maio de 2004 12:24
Para: LazyDBA Discussion
Assunto: RE: PROBLEMS IN A TRIGGER


Do you need WHERE REC.field_name = 0;

-----Original Message-----
From: Pedro Miguel Gonçalves [Email address protected]
Sent: Thursday, May 20, 2004 6:18 AM
To: LazyDBA Discussion
Subject: PROBLEMS IN A TRIGGER

Hi guys,

I'm working with DB2 v.7 for OS/390.

What's the problem with this trigger I want to create?

CREATE TRIGGER trigger_name
NO CASCADE BEFORE INSERT ON table_name
REFERENCING NEW AS REC
FOR EACH ROW MODE DB2SQL
UPDATE table_name
SET REC.field_name = NULL
WHERE field_name = 0;

Keeps telling that I should switch some keyword or end-of-statment for some
tokken that DB2 doesn´t give me.


HELP.


Pedro Gonçalves. [Email address protected]


---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe, e-mail: [Email address protected]
For additional commands, e-mail: [Email address protected]



---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe, e-mail: [Email address protected]
For additional commands, e-mail: [Email address protected]



---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe, e-mail: [Email address protected]
For additional commands, e-mail: [Email address protected]

**********************************************************************
This email and any files transmitted with it are confidential and are
intended solely for the use of the individual or entity to whom they are
addressed. This communication represents the originator's personal views and
opinions, which do not necessarily reflect those of HPI Limited. If you are
not the original recipient or the person responsible for delivering the
email to the intended recipient, be advised that you have received this
email in error, and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited. If you received this email in
error, please immediately notify [Email address protected]

This message has been scanned by Panda Anti-Virus. Recipients are advised to
apply their own virus checks to this message on delivery.
**********************************************************************



DB2 & UDB email list listserv db2-l LazyDBA home page