This should work
CREATE TRIGGER TRIG10 NO CASCADE BEFORE INSERT
ON emp_table REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.emp_number=0)
SET N.emp_number=NULL [Email address protected]
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 17:55
To: LazyDBA Discussion
Subject: RE: PROBLEMS IN A TRIGGER
Hi sathya,
Here it is:
CREATE TRIGGER trigger1 NO CASCADE BEFORE INSERT
ON emp_table REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.emp_number=0)
BEGIN ATOMIC
SET N.emp_number=NULL;
END
The END is there and same problem occurred, the error is:
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT
BE
LEGAL ARE: QUOTE
Why does he wants quotes if the field is numeric?
And where is this space "" is talking about?
Help me please...
Pedro Gonçalves
-----Mensagem original-----
De: Sathya [Email address protected]
Enviada: quinta-feira, 20 de Maio de 2004 17:40
Para: Pedro Miguel Gonçalves
Assunto: RE: PROBLEMS IN A TRIGGER
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret **from
this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Hi,
Have you missed the END for the Begin. I do not see in your text. Give
me full error text , I will try to fix it for you.
bye
sathya
"Pedro Miguel Gonçalves " [Email address protected] on 05/20/2004 11:39:59
AM
To: "LazyDBA Discussion" [Email address protected]
cc:
Subject: RE: PROBLEMS IN A TRIGGER
NOTHING,
DOES ANYONE HAS ANY MORE SUGGESTIONS?
RIGHT NOW I HAVE AND NOT WORKING ON DB2 V.7 FOR OS/390 THE FOLLOWING:
CREATE TRIGGER trigger_name NO CASCADE BEFORE INSERT
ON table_anme REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.field_name1=0)
BEGIN ATOMIC
SET field_name1=NULL;
All I want is that before inserting a record in a table that contains a 0 is
inserted in the table as NULL.
PLEASE HELP ME HERE.
-----Mensagem original-----
De: Alejandro Daniel Toffetti [Email address protected]
Enviada: quinta-feira, 20 de Maio de 2004 13:47
Para: Pedro Miguel Gonçalves
Assunto: Re: PROBLEMS IN A TRIGGER
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email, **and removed any
attachments, and kept your email address secret **from this person, and any
viruses/trojans. **If you reply to this email, the person will see your
email address as normal **Anything below this line is the original email
text
Two things that springs to mind:
Try fitting the whole text of the trigger into one single line and
submit again. It worked for me only this way sometimes (DB2 UDB 6.1 under
Windows 2000)
Try replacing
REC.field_name = NULL
with something like
REC.field_name = NULLIF('0', '0')
Nullif returns NULL if both arguments are equal.
Hope this helps,
Daniel
Pedro Miguel Gonçalves wrote:
>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]
>
---------------------------------------------------------------------
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