Start a new topic

Problem on creating trigger in DB2

[This topic is migrated from our old forums. The original author name has been removed]

I tried to create a trigger in DB2 (see DDL below), but DB2visualizer returned error. Same DDL works fine from WinSql. Any suggestion ? Thanks --- Create trigger SING.TR_MATI_UPD_MATIUP AFTER UPDATE OF MATIDTAZ, MATIDATZ ON SING.MATI REFERENCING NEW AS N OLD AS O FOR EACH ROW BEGIN ATOMIC declare $xanek integer; declare $xprom integer; declare $xmaup integer; set $xanek = (SELECT count(*) from SING.ANEK where ANEKAPLI = N.MATIAPLI and ANEKPART = N.MATIPART and ANEKKONT = N.MATIKONT and ANEKPOEN = N.MATIPOEN); set $xprom = (SELECT count(*) from SING.PROM where PROMAPLI = N.MATIAPLI and PROMPART = N.MATIPART and PROMKONT = N.MATIKONT and PROMPOEN = N.MATIPOEN); set $xmaup = (SELECT count(*) from SING.MATIUP where MATIAPLI = N.MATIAPLI and MATIPART = N.MATIPART and MATIKONT = N.MATIKONT and MATIPOEN = N.MATIPOEN and MATIUPZS = 0); if $xanek = 0 and $xprom = 0 and $xmaup > 0 then DELETE from SING.MATIUP where MATIAPLI = N.MATIAPLI and MATIPART = N.MATIPART and MATIKONT = N.MATIKONT and MATIPOEN = N.MATIPOEN and MATIUPZS = 0; end if; if $xanek = 0 and $xprom = 0 then INSERT INTO SING.MATIUP ( MATIAPLI,MATIPART,MATIKONT,MATIPOEN, MATIUPZS, MATIUPIZ, MATIUPTP, MATIUPPR, MATIUPKJ, MATIUPNS, MATIUPST, MATIUPDT, MATIUPRE, MATIUPZO, MATIUPR1, MATIUPD1, MATIUPR2, MATIUPD2, MATIUPOP, MATIROZD, MATIZSDK, MATIZRAC, MATIZNAM, MATIZPAR, MATIVEZA, MATIREFE, MATIORGE, MATIRACU, MATIZAPO, MATIANEK, MATITRAN, MATIDODO, MATIDPOG, MATIDZOR, MATIDZOZ, MATIDZOM, MATIDVRA, MATIDSYS, MATIDROK, MATIDPRE, MATIDZAC, MATIDZAP, MATIDZAM, MATIDOBA, MATISTEV, MATIPERI, MATISTIP, MATISNMN, MATISVZA, MATISVSR, MATISODO, MATINOTE, MATISTRO, MATIOKVI, MATIOBNA, MATIOBBR, MATIKLAS, MATIKLSZ, MATIOBAN, MATISTAT, MATIREAL, MATIREVA, MATIOMRL, MATIOMRV, MATIYREA, MATIYREV, MATIYOBR, MATISTOC, MATITECM, MATITPTE, MATIZNES, MATIZNE1, MATIPSTR, MATIKLBZ, MATIZPOR, MATIITPO, MATIANUI, MATIROZ2, MATIPOPV, MATIPOGO, MATISTME, MATISTMZ, MATINAME, MATIDVLO, MATIOSPE, MATIOBPA, MATIOBKO, MATIPRSM, MATIPRPR, MATIPRSP, MATIPRNO, MATISEOS, MATINSTO, MATIOSTO, MATIDZTR, MATIVISR, MATITKLD, MATIZAPA, MATIZAHT, MATIIVNO, MATIDVNO, MATIPERO, MATIPEOB, MATINAMS, MATIVIRR, MATIVIRZ, MATINADK, MATIDTKS, MATISTKS, MATIZAOB, MATINOTB, MATINAPR, MATIZAOK, MATIODRE, MATIIZOB, MATIJEOB, MATITRGI, MATIMOGI, MATISKGI, MATIOPGI, MATITROI, MATIMOOI, MATISKOI, MATIOPOI, MATITRGP, MATIMOGP, MATISKGP, MATIOPGP, MATIPOSP, MATIDZSP, MATIDSKLE,MATINEPO, MATIREPS, MATIREPK, MATISKLE, MATIODOB, MATIKLMS, MATIPOMS, MATIPEPR, MATISTAZ, MATIDATZ, MATIPRGM, MATIUSER, MATISUPA, MATISUKO, MATICPBR, MATINASR, MATINARO, MATINAST, MATINAMI, MATIDZPR, MATIDTGM, MATIVISZ, MATIROZD_F,MATIRACU_F,MATISEOS_F,MATIZAPLI,MATIZPART,MATIZKONT, MATIZPOEN,MATIVAPLI,MATIVPART,MATIVKONT,MATIVPOEN, MATIDTAZ, MATIZRRN, MATISPON, MATIZEVA, MATIZMRL, MATITXPO, MATITRAJ, MATITRGT, MATIDOOD, MATIDNAJ, MATIVISR_F,MATISIKR, MATIRAPR, MATIVRKR, MATIPOOL, MATISIFP, MATIAGNT, MATIAGUD, MATIDTST, MATIBCID, MATIBCIK, MATIBCDL ) VALUES ( O.MATIAPLI,O.MATIPART,O.MATIKONT,O.MATIPOEN, 0, 'K', 'TI', 'TR_MATI_UPD_MATIUP', 'P', 'N', 'A', current timestamp, '', 'N', '', current timestamp, '', current timestamp, '', O.MATIROZD, O.MATIZSDK, O.MATIZRAC, O.MATIZNAM, O.MATIZPAR, O.MATIVEZA, O.MATIREFE, O.MATIORGE, O.MATIRACU, O.MATIZAPO, O.MATIANEK, O.MATITRAN, O.MATIDODO, O.MATIDPOG, O.MATIDZOR, O.MATIDZOZ, O.MATIDZOM, O.MATIDVRA, O.MATIDSYS, O.MATIDROK, O.MATIDPRE, O.MATIDZAC, O.MATIDZAP, O.MATIDZAM, O.MATIDOBA, O.MATISTEV, O.MATIPERI, O.MATISTIP, O.MATISNMN, O.MATISVZA, O.MATISVSR, O.MATISODO, O.MATINOTE, O.MATISTRO, O.MATIOKVI, O.MATIOBNA, O.MATIOBBR, O.MATIKLAS, O.MATIKLSZ, O.MATIOBAN, O.MATISTAT, O.MATIREAL, O.MATIREVA, O.MATIOMRL, O.MATIOMRV, O.MATIYREA, O.MATIYREV, O.MATIYOBR, O.MATISTOC, O.MATITECM, O.MATITPTE, O.MATIZNES, O.MATIZNE1, O.MATIPSTR, O.MATIKLBZ, O.MATIZPOR, O.MATIITPO, O.MATIANUI, O.MATIROZ2, O.MATIPOPV, O.MATIPOGO, O.MATISTME, O.MATISTMZ, O.MATINAME, O.MATIDVLO, O.MATIOSPE, O.MATIOBPA, O.MATIOBKO, O.MATIPRSM, O.MATIPRPR, O.MATIPRSP, O.MATIPRNO, O.MATISEOS, O.MATINSTO, O.MATIOSTO, O.MATIDZTR, O.MATIVISR, O.MATITKLD, O.MATIZAPA, O.MATIZAHT, O.MATIIVNO, O.MATIDVNO, O.MATIPERO, O.MATIPEOB, O.MATINAMS, O.MATIVIRR, O.MATIVIRZ, O.MATINADK, O.MATIDTKS, O.MATISTKS, O.MATIZAOB, O.MATINOTB, O.MATINAPR, O.MATIZAOK, O.MATIODRE, O.MATIIZOB, O.MATIJEOB, O.MATITRGI, O.MATIMOGI, O.MATISKGI, O.MATIOPGI, O.MATITROI, O.MATIMOOI, O.MATISKOI, O.MATIOPOI, O.MATITRGP, O.MATIMOGP, O.MATISKGP, O.MATIOPGP, O.MATIPOSP, O.MATIDZSP, O.MATIDSKLE,O.MATINEPO, O.MATIREPS, O.MATIREPK, O.MATISKLE, O.MATIODOB, O.MATIKLMS, O.MATIPOMS, O.MATIPEPR, O.MATISTAZ, O.MATIDATZ, O.MATIPRGM, O.MATIUSER, O.MATISUPA, O.MATISUKO, O.MATICPBR, O.MATINASR, O.MATINARO, O.MATINAST, O.MATINAMI, O.MATIDZPR, O.MATIDTGM, O.MATIVISZ, O.MATIROZD_F,O.MATIRACU_F,O.MATISEOS_F,O.MATIZAPLI,O.MATIZPART,O.MATIZKONT, O.MATIZPOEN,O.MATIVAPLI,O.MATIVPART,O.MATIVKONT,O.MATIVPOEN, O.MATIDTAZ, O.MATIZRRN, O.MATISPON, O.MATIZEVA, O.MATIZMRL, O.MATITXPO, O.MATITRAJ, O.MATITRGT, O.MATIDOOD, O.MATIDNAJ, O.MATIVISR_F,O.MATISIKR, O.MATIRAPR, O.MATIVRKR, O.MATIPOOL, O.MATISIFP, O.MATIAGNT, O.MATIAGUD, O.MATIDTST, O.MATIBCID, O.MATIBCIK, O.MATIBCDL ); end if; END ---

[This reply is migrated from our old forums.]

Re: Problem on creating trigger in DB2
Sing, What error do you get and what edition of DB2 are you using (DB2 LUW, DB2 z/OS DB2 AS/400)? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Problem on creating trigger in DB2
I use DB2 LUW. Db (db2express,version 09.07.0001) is on windows 7 64bit , my client is xp 32bit. Errors from creating trigger: 13:27:11 [CREATE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=integer;MIC declare $xanek;END-OF-STATEMENT, DRIVER=4.13.127 13:27:11 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=integer;declare $xprom ;END-OF-STATEMENT, DRIVER=4.13.127 13:27:11 [DECLARE - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=integer;declare $xmaup ;END-OF-STATEMENT, DRIVER=4.13.127 13:27:11 [SET - 0 row(s), 0.000 secs] [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=$XANEK, DRIVER=4.13.127 13:27:11 [SET - 0 row(s), 0.000 secs] [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=$XPROM, DRIVER=4.13.127 13:27:11 [SET - 0 row(s), 0.000 secs] [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=$XMAUP, DRIVER=4.13.127 13:27:11 [IF - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=if;BEGIN-OF-STATEMENT;, DRIVER=4.13.127 13:27:11 [END - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;end if;JOIN , DRIVER=4.13.127 13:27:11 [IF - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=if;BEGIN-OF-STATEMENT;, DRIVER=4.13.127 13:27:11 [END - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;end if;JOIN , DRIVER=4.13.127 13:27:11 [END - 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;END;JOIN , DRIVER=4.13.127 ... 11 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 11 errors]
[This reply is migrated from our old forums.]

Re: Problem on creating trigger in DB2
Sing, DbVisualizer use by default the semicolon as the statement separator. The problem when running for example a "CREATE OR REPLACE TRIGGER" block is that this include semicolons in the body of the code. To workaround this in DbVisualizer there are several options: 1) Use of the @delimiter client side command. Example: @delimiter ##; @delimiter ;## This will re-define the statement separator to ## and at the end of the script you put @delimiter ;## to restore semicolon as separator. 2) Enclose each of the statements between the SQL block identifiers --/ and /: --/ CREATE OR REPLACE PROCEDURE ? / 3) Run each of the statements separately using the SQL->Execute Buffer command. This will not parse the buffer for the semicolon. The drawback is that you can only run one statement at a time. I hope this helps. Please let me know if I can be of further assistance with any questions you may have. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Problem on creating trigger in DB2
Thanks for your solutions. It is all ok now.