OS390 and CREATE TRIGGER
Gianoele
CREATE TRIGGER KM.T1GENDEF NO CASCADE BEFORE INSERT ON KM.IDGENDEF
REFERENCING NEW AS ROWADDED
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF ROWADDED.SESSO_CDB = 'X'
THEN
SET ROWADDED.SESSO_CDB = 'M';
ELSEIF ROWADDED.SESSO_CDB = 'Y'
THEN
SET ROWADDED.SESSO_CDB = 'F';
ELSE
INSERT INTO KM.KMTECTOT (KMTT_COD_INTERNAL, KMTT_DAT_OPERATION,
KMTT_NUM_TOTALE, KMTT_DES_MSG)
VALUES (ROWADDED.ID_INDIVIDUO, CURRENT_TIMESTAMP,
ROWADDED.NUM_IDENTITY, 'INSERT');
END IF
END
;
The server returns this error:
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN
<END-OF-STATEMENT> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 1089 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF' X'00000441' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8
Can anybody help me finding the error? If I search on the IBM manuals
the command seems to be correct, there are also many examples like
this.
Are there differents mode of creating a trigger for different
platforms? (OS390 vs. X86)
Thanks
Serge Rielau
If no you may want to condier the CASE expresion instead of IF THEN ELSE
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto
Try the DB2 UDB V8.1 beta at
http://www-3.ibm.com/software/data/db2/
EDWARD LIPSON
semicolon. The parser does not know which semicolon is the end of the
trigger.
G > I'd like to create this trigger:
G > CREATE TRIGGER KM.T1GENDEF NO CASCADE BEFORE INSERT ON KM.IDGENDEF
G > REFERENCING NEW AS ROWADDED
G > FOR EACH ROW MODE DB2SQL
G > BEGIN ATOMIC
G > IF ROWADDED.SESSO_CDB = 'X'
G > THEN
G > SET ROWADDED.SESSO_CDB = 'M';
G > ELSEIF ROWADDED.SESSO_CDB = 'Y'
G > THEN
G > SET ROWADDED.SESSO_CDB = 'F';
G > ELSE
G > INSERT INTO KM.KMTECTOT (KMTT_COD_INTERNAL, KMTT_DAT_OPERATION,
G > KMTT_NUM_TOTALE, KMTT_DES_MSG)
G > VALUES (ROWADDED.ID_INDIVIDUO, CURRENT_TIMESTAMP,
G > ROWADDED.NUM_IDENTITY, 'INSERT');
G > END IF
G > END
G > ;
G > The server returns this error:
G > DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN
G > <END-OF-STATEMENT> WAS EXPECTED
G > DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
G > DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
G > DSNT416I SQLERRD = 0 0 0 -1 1089 0 SQL DIAGNOSTIC INFORMATION
G > DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
G > X'FFFFFFFF' X'00000441' X'00000000' SQL DIAGNOSTIC
G > INFORMATION
G > BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
G > RETCODE = 8
G > Can anybody help me finding the error? If I search on the IBM manuals
G > the command seems to be correct, there are also many examples like
G > this.
G > Are there differents mode of creating a trigger for different
G > platforms? (OS390 vs. X86)
G > Thanks
Edward Lipson via Relaynet.org Moondog
edward...@moondog.com eli...@bankofny.com
---
ş MM 1.1 #0361 ş Programmers are a BIT smarter than the average user.
-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
Gianoele
> In SPUFI, change the delimiter from semicolon and make it match the last
> semicolon. The parser does not know which semicolon is the end of the
> trigger.
>
Thank you all for help.
I've posted this solution to the system administrator who told me
that the delimiter used was the question mark (?).
May it be the cause for the error? Are there any character (other
than letters, numbers and semicolon) that cannot be used as delimiter?
Gianoele.
James Campbell
The best I can suggest is
CREATE TRIGGER KM.T1GENDEF_1 NO CASCADE BEFORE INSERT ON KM.IDGENDEF
REFERENCING NEW AS ROWADDED
FOR EACH ROW MODE DB2SQL
when (case ROWADDED.SESSO_CDB = 'X' then 1
else case ROWADDED.SESSO_CDB = 'Y' then 1
else 0 end
end = 1)
set ROWADDED.SESSO_CDB =
case ROWADDED.SESSO_CDB = 'X' then 'M'
else case ROWADDED.SESSO_CDB = 'Y' then 'F'
end end;
CREATE TRIGGER KM.T1GENDEF_2 NO CASCADE BEFORE INSERT ON KM.IDGENDEF
REFERENCING NEW AS ROWADDED
FOR EACH ROW MODE DB2SQL
when (case ROWADDED.SESSO_CDB = 'X' then 1
else case ROWADDED.SESSO_CDB = 'Y' then 1
else 0 end
end = 0)
INSERT INTO KM.KMTECTOT (KMTT_COD_INTERNAL, KMTT_DAT_OPERATION,
KMTT_NUM_TOTALE, KMTT_DES_MSG)
VALUES (ROWADDED.ID_INDIVIDUO, CURRENT_TIMESTAMP,
ROWADDED.NUM_IDENTITY, 'INSERT');
Because each trigger only has a single statement, there is no need for
BEGIN ATOMIC and a trailing END - and hence no problem (which others have
mentioned) with using a semi-colon as the statement terminator.
James Campbell
gian...@netscape.net (Gianoele) wrote in
news:69dad29a.02072...@posting.google.com:
EDWARD LIPSON
You would have to RTM to see what is permitted or prohibited. I don't
remember what we used.
G > edward...@relaynet.org (EDWARD LIPSON) wrote in message news:<03B942E.09B40
G > 10AF5...@relaynet.org>...
G > > In SPUFI, change the delimiter from semicolon and make it match the last
G > > semicolon. The parser does not know which semicolon is the end of the
G > > trigger.
G > >
G > Thank you all for help.
G > I've posted this solution to the system administrator who told me
G > that the delimiter used was the question mark (?).
G > May it be the cause for the error? Are there any character (other
G > than letters, numbers and semicolon) that cannot be used as delimiter?
G > Gianoele.
Edward Lipson via Relaynet.org Moondog
edward...@moondog.com eli...@bankofny.com
---
þ MM 1.1 #0361 þ LSD: Virtual reality without the expensive hardware!
James Campbell
realised that you should create trigger 2 before creating trigger 1.
James Campbell
James Campbell <jacam...@acslink.remove_nospam.net.au> wrote in
news:3d400...@news.iprimus.com.au:
Gianoele
Ok,
logically it works (very hard to understand!). The question now is: Is
it possible to perform INSERT on a BEFORE TRIGGER? The server answers
me "The trigger is defined with an unsupported triggered SQL
statement".....