RE: TRIGGER AND STORED PROCEDURE HELP NEEDED
----------
Ramki Natesan (rnatesan@bactc.com) asked:
>I have the following trigger stmnt.
>create trigger get_wo_num>insert on workorder
>after (execute procedure create_wo_for_nt())
Q: Can I do this in isql mode?
JL: Yes. ISQL can handle any statement which does not have semi-colons in
the middle of it. The statements which have such semi-colons are CREATE
PROCEDURE statements. For those, you need to use DB-Access.
>then, p1.sql file has the following:-
>CREATE PROCEDURE create_wo_for_nt()
[...several statements, hence several semi-colons...]
>END PROCUDERE;
>Q: Can I run this (p1.sql) file in isql mode?
No, because there are semi-colons in the middle of the statement and ISQL
gets confused.
Use DB-Access. If that isn't available, go to the server machine where the
database is and use the copy of DB-Access which is on that. Or, if you
have ESQL/C, you can either collect SQLCMD from the IIUG software archives
at http://www.iiug.org or you can use the program MKPROC in the shell
archive below which will execute the CREATE PROCEDURE statements from a
source file (or any other statements which return no data to the caller).
If none of this is feasible, then you need to chat to your System
Administrator and/or DBA and get DB-Access on your machine.
Yours,
Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>
PS: Mr Software Manager at IIUG - I think this could go in the IIUG
archives quite usefully.
begin 600 mkproc.txt
M.@DB0"@C*3H@<VAA<BYS:"QV(#$N,3$@,3DY."\\P,R\\Q-B R,#HS-CHQ-B!J
M;&5F9FQE<B!%>' @)"(-"B,A("]B:6XO<V@-"B,-"B,)5&AI<R!I<R!A('-H
M96QL(&%R8VAI=F4N#0HC"5)E;6]V92!E=F5R>71H:6YG(&%B;W9E('1H:7,@
M;&EN92!A;F0@<G5N('-H(&]N('1H92!R97-U;'1I;F<@9FEL92X-"B,)268@
M=&AI<R!A<F-H:79E(&ES(&-O;7!L971E+"!Y;W4@=VEL;"!S964@=&AI<R!M
M97-S86=E(&%T('1H92!E;F0Z#0HC"2)!;&P@9FEL97,@97AT<F%C=&5D(@T*
M(PT*(PE#<F5A=&5D.B!4:'4@36%Y(#$T(#$S.C4Y.C,V(%!$5" Q.3DX(&)Y
M(&IL969F;&5R(&%T(%9I<V$@26YT97)N871I;VYA;"!);F,N#0HC"49I;&5S
M(&%R8VAI=F5D(&EN('1H:7,@87)C:&EV93H-"B,)9V5T;W!T+F@-"B,);6MP
M<F]C+C$-"B,);6MP<F]C+F5C#0HC"6UK<')O8RYM:PT*(PES=&1E<G(N8PT*
M(PES=&1E<G(N: T*(PT*(RTM+2TM+2TM+2TM+2TM+2TM+2TM#0II9B!;("UF
M(&=E=&]P="YH("UA("(D,2(@(3T@(BUC(B!=#0IT:&5N(&5C:&\\@<VAA<CH@
M9V5T;W!T+F@@86QR96%D>2!E>&ES=',-"F5L<V4-"F5C:&\\@)W@@+2!G971O
M<'0N:" H,3<W,B!C:&%R86-T97)S*2<-"G-E9" M92 G<R]>6"\\O)R ^9V5T
M;W!T+F@@/#PG4TA!4BU%3T8G#0I8+RH-"EA *",I1FEL93H@(" @(" @(" @
M(" D4D-39FEL93H@9V5T;W!T+F@L=B D#0I80"@C*59E<G-I;VXZ(" @(" @
M(" @)%)E=FES:6]N.B Q+C$P("0-"EA *",I3&%S="!C:&%N9V5D.B @(" D
M1&%T93H@,3DY."\\P-"\\P.2 Q.#HT.#HR," D#0I80"@C*5!U<G!O<V4Z(" @
M(" @(" @1&5C;&%R871I;VYS(&9O<B!'151/4%0H,RD@86YD($=%5%-50D]0
M5"@S*0T*6$ H(RE!=71H;W(Z(" @(" @(" @($H@3&5F9FQE<@T*6$ H(RE#
M;W!Y<FEG:'0Z(" @(" @($I,4U,@*$,I(#$Y.3(M.3,L,3DY-BTY-PT*6$ H
M(RE0<F]D=6-T.B @(" @(" @(#I04D]$54-4.@T*6"HO#0I8#0I8(VEF;F1E
M9B!'151/4%1?2 T*6"-D969I;F4@1T543U!47T@-"E@-"E@C:69D968@34%)
M3E]04D]'4D%-#0I8(VEF;F1E9B!L:6YT#0I8<W1A=&EC(&-O;G-T(&-H87(@
M9V5T;W!T7VA;72 ](") *",I)$ED.B!G971O<'0N:"QV(#$N,3 @,3DY."\\P
M-"\\P.2 Q.#HT.#HR,"!J;&5F9FQE<B!%>' @)"([#0I8(V5N9&EF"2\\J(&QI
M;G0@*B\\-"E@C96YD:68)+RH@34%)3E]04D]'4D%-("HO#0I8#0I8+RH-"E@J
M*B!'3E4@9V5T;W!T('!R;W9I9&5S(&9A8VEL:71I97,@;F]T(&%V86EL86)L
M92!I;B!S=&%N9&%R9"!G971O<'0N#0I8*BH@4W!E8VEF:6-A;&QY+"!I="!W
M:6QL(')E;W)D97(@86QL(&]P=&EO;B!A<F=U;65N=',@8F5F;W)E(&%L;"!N
M;VXM;W!T:6]N#0I8*BH@87)G=6UE;G1S('5N;&5S<R!T:&4@96YV:7)O;FUE
M;G0@=F%R:6%B;&4@7U!/4TE87T]05$E/3E]/4D1%4B!I<PT*6"HJ(&1E9FEN
M960N("!)="!C86X@86QS;R!H86YD;&4@;W!T:6]N86P@87)G=6UE;G1S+"!W
M:&EC:"!M=7-T(&)E(&%T=&%C:&5D#0I8*BH@=&\\@;W!T:6]N(&QE='1E<B!O
M;B!T:&4@8V]M;6%N9"!L:6YE+"!I;F1I8V%T960@8GD@='=O(&-O;&]N<R!A
M9G1E<B!T:&4-"E@J*B!O<'1I;VX@;&5T=&5R+B @270@8V%N(&)E('1O;&0@
M=&\\@<F5T=7)N(&%L;"!A<F=U;65N=',@:6X@;W)D97(L('=I=&@@80T*6"HJ
M('9A;'5E(&]F("=<,"<@:6YD:6-A=&EN9R!A(&9I;&4@;W!T:6]N(&)Y('-T
M87)T:6YG('1H92!O<'1I;VYS('-T<FEN9PT*6"HJ('=I=&@@82 G+2<N("!)
M="!A;'-O(&AA<R!A(&1I9F9E<F5N="!I;G1E<F9A8V4@9G)O;2!S=&%N9&%R
M9"!G971O<'0-"E@J*B!B96-A=7-E('1H92!S96-O;F0@*&%R9W8I(&%R9W5M
M96YT(&ES(&YO="!C;VYS="X-"E@J+PT*6 T*6"-I9F1E9B!54T5?1TY57T=%
M5$]05 T*6"-D969I;F4@1T543U!4*&%R9V,L(&%R9W8L(&]P=',I"6=N=5]G
M971O<'0H87)G8RP@87)G=BP@;W!T<RD-"E@C9&5F:6YE(&]P=&5R<@EG;G5?
M;W!T97)R#0I8(V1E9FEN92!O<'1I;F0)9VYU7V]P=&EN9 T*6"-D969I;F4@
M;W!T87)G"6=N=5]O<'1A<F<-"E@C9&5F:6YE(&]P=&]P= EG;G5?;W!T;W!T
M#0I8(V5L<V4-"E@C9&5F:6YE($=%5$]05"AA<F=C+"!A<F=V+"!O<'1S*0EG
M971O<'0H87)G8RP@87)G=BP@;W!T<RD-"E@C96YD:68@+RH@55-%7T=.55]'
M151/4%0@*B\\-"E@-"EAE>'1E<FX@:6YT(" @(" @;W!T;W!T.PT*6&5X=&5R
M;B!I;G0@(" @("!O<'1E<G([#0I897AT97)N(&EN=" @(" @(&]P=&EN9#L-
M"EAE>'1E<FX@8VAA<B @(" J;W!T87)G.PT*6 T*6&5X=&5R;B!I;G0@(" @
M("!G971O<'0H:6YT(&%R9V,L(&-H87(@*F-O;G-T*F%R9W8L(&-O;G-T(&-H
M87(@*F]P=',I.PT*6&5X=&5R;B!I;G0@(" @("!G971S=6)O<'0H8VAA<B J
M*F]P="P@8VAA<B J8V]N<W0J;F%M97,L(&-H87(@*BIV86QU92D[#0I897AT
M97)N(&EN=" @(" @(&=N=5]G971O<'0H:6YT(&%R9V,L(&-H87(@*BIA<F=V
M+"!C;VYS="!C:&%R("IO<'1S*3L-"E@-"E@C96YD:68)+RH@1T543U!47T@@
M*B\\-"E-(05(M14]�IC:&UO9" T-# @9V5T;W!T+F@-"FEF(%L@8'=C("UC
M(#QG971O<'0N:& @+6YE(#$W-S(@70T*=&AE;B!E8VAO('-H87(Z(&=E=&]P
M="YH('5N<&%C:V5D('=I=&@@=W)O;F<@<VEZ90T*9FD-"B,@96YD(&]F(&]V
M97)W<FET:6YG(&-H96-K#0IF:0T*(RTM+2TM+2TM+2TM+2TM+2TM+2TM#0II
M9B!;("UF(&UK<')O8RXQ("UA("(D,2(@(3T@(BUC(B!=#0IT:&5N(&5C:&\\@
M<VAA<CH@;6MP<F]C+C$@86QR96%D>2!E>&ES=',-"F5L<V4-"F5C:&\\@)W@@
M+2!M:W!R;V,N,2 H,3(R,2!C:&%R86-T97)S*2<-"G-E9" M92 G<R]>6"\\O
M)R ^;6MP<F]C+C$@/#PG4TA!4BU%3T8G#0I8+EPB($ H(RDD260Z(&UK<')O
M8RXQ+'8@,2XQ(#$Y.3@O,#4O,30@,C Z-38Z-34@:FQE9F9L97(@17AP("0-
M"E@N7"(@0"@C*4UA;G5A;"!P86=E.B!-2U!23T,@+2T@17AE8W5T92!344P@
M<W1A=&5M96YT<R!I;B!F:6QE<PT*6"YD<R!F0R B5F5R<VEO;CH@)%)E=FES
M:6]N.B Q+C$@)" H)$1A=&4Z(#$Y.3@O,#4O,30@,C Z-38Z-34@)"D-"E@N
M5$@@34M04D]#(#%3(")*3%-3(%5.25@@5&]O;',B#0I8+E-(($Y!344-"EAM
M:W!R;V,@7"AE;2!%>&5C=71E(%-13"!S=&%T96UE;G1S(&EN(&9I;&5S#0I8
M+E-((%-93D]04TE3#0I87&9";6MP<F]C7&90(%LM<W1672 M9"!D8F%S92!F
M:6QE(%LN+BY=#0I8+E-(($1%4T-225!424].#0I85&AE(&-O;6UA;F0@7&9"
M36MP<F]C7&90(&]P96YS('1H92!G:79E;B!D871A8F%S92!A;F0@=&AE;@T*
M6&5X96-U=&5S('1H92!C;VYT96YT<R!O9B!E86-H(&]F('1H92!F:6QE<R!S
M<&5C:69I960@;VX@=&AE#0I88V]M;6%N9"!L:6YE('5S:6YG('1H92!%4U%,
M+T,@0U)%051%(%!23T-%1%5212!&4D]-('-T871E;65N="X-"EA4:&4@<W1A
M=&5M96YT<R!S:&]U;&0L('1H97)E9F]R92P@;F]T(')E='5R;B!A;GD@9&%T
M82P@8G5T('=I=&AI;@T*6'1H870@8V]N<W1R86EN="P@;6]S="!S=&%T96UE
M;G1S(&-A;B!B92!U<V5D+@T*6"Y0#0I8268@=&AE(&1A=&%B87-E(&AA<R!T
M<F%N<V%C=&EO;G,L(&UK<')O8R!W:6QL(&5N8VQO<V4@96%C: