Re: create trigger crashes online
In article <34900766.7A25@dvs1.informatik.th-darmstadt.de>, Christoph
Liebig <chris@dvs1.informatik.th-darmstadt.de> writes
>David Williams wrote:
>
> In article <348E8C64.229A@dvs1.informatik.th-darmstadt.de>, Christoph
> Liebig <chris@dvs1.informatik.th-darmstadt.de> writes
> >Hi !
> >
> >We are using online 7.23.UC2 in a database course (lab).
> >Some students manage to crash nformix by issuing the following "create
> >trigger" statement:
> >
> >CREATE TRIGGER ins_Reserv_Tr INSERT ON RESERV_TEILVB> >REFERENCING NEW AS new_RT
> >FOR EACH ROW
> >WHEN
> >(( SELECT COUNT (*)
> > FROM STRECKENABSCHNITT
> > WHERE AbschnittNr IN
> > ( select TA.AbschnittNr
> > from TVB_AUF TA
> > where (TA.TVBNr = new_RT.TVBNr) and
> > (TA.AbschnittNr NOT IN
> > ( select FGF.StreckenAbs
> > from FK_GILT_FUER FGF, RESERVIERUNG R
> > where (R.ReservNr = new_RT.ReservNr) and
> > (FGF.FKartenNr = R.ZuFKarte))))) = 0)
> >(EXECUTE PROCEDURE errorproc('some err_msg')),
> >WHEN
> >(( SELECT COUNT (r1.ReservNr)
> > FROM RESERVIERUNG r1, RESERVIERUNG r2
> > WHERE (r1.GiltAm = r2.GiltAm) AND
> > (r1.AufWagen = r2.AufWagen) AND
> > (r1.AufPlatz = r2.AufPlatz) AND
> > (r1.ReservNr IN
> > ( SELECT DISTINCT ReservNr
> > FROM RESERV_TEILVB
> > WHERE (TVBNr = new_RT.TVBnr) AND
> > (ReservNr != new_RT.ReservNr)))) > 0)
> >(EXECUTE PROCEDURE errorproc('some err_msg')),
> >--WHEN
> >--(((SELECT COUNT (r2.AufPlatz)
> >-- FROM RESERVIERUNG r1, RESERVIERUNG r2, Wagen w1, Wagen w2
> >-- WHERE
> >-- ( r2.ReservNr = new_rt.ReservNr AND
> >-- r2.GiltAm = r1.GiltAm AND
> >-- w2.WagenNr = r2. AufWagen AND
> >-- w1.ZugNr = w2.ZugNr AND
> >-- r1.AufWagen = w1.WagenNr AND
> >-- r1.ReservNr IN
> >-- ( SELECT ReservNr
> >-- FROM RESERV_TEILVB
> >-- WHERE (TVBNr = new_rt.TVBNr)))))
> >-- -
> >-- (SELECT Plaetze * 0.85
> >-- FROM ZUG z, WAGEN w, RESERVIERUNG r
> >-- WHERE
> >-- ( r.ReservNr = new_RT.ReservNr AND
> >-- w.WagenNr = r.AufWagen AND
> >-- z.ZugNr = w.ZugNr))
> >-- > 0)
> >--(EXECUTE PROCEDURE errorproc('some err_msg'));
> >
> >If you do not quote the last 'when' section, the create trigger will
> >not succeed but crash the server.
> >However, executed as an SQL statement the when clauses do succeed.
> >
> >Can anyone help ? (If you need the create tables, feel free to ask)
> >
>
>All I can think of is...
>
> 1) That is quite a nested stack trace, have you tried increasing
> the ONCONFIG parameter STACKSIZE to 64K?
>
> 2) Talk to Informix Tech Support.
>
>---- end of D. Williams' reply -----------------------------
>
>
>I tried with higher stacksize, same non-result :(
>But there is a solution, however, it is still a bug !!
>I commuted the two selects in the suspected when clause...and
>the create trigger runs to completion (without crashing informix) !
>
>--WHEN
> ((SELECT Plaetze * 0.85
> FROM ZUG z, WAGEN w, RESERVIERUNG r
> WHERE
> ( r.ReservNr = new_RT.ReservNr AND
> w.WagenNr = r.AufWagen AND
> z.ZugNr = w.ZugNr))
> -
> (SELECT COUNT (r2.AufPlatz)
> FROM RESERVIERUNG r1, RESERVIERUNG r2, Wagen w1, Wagen w2
> WHERE
> ( r2.ReservNr = new_rt.ReservNr AND
> r2.GiltAm = r1.GiltAm AND
> w2.WagenNr = r2. AufWagen AND
> w1.ZugNr = w2.ZugNr AND
> r1.AufWagen = w1.WagenNr AND
> r1.ReservNr IN
> ( SELECT ReservNr
> FROM RESERV_TEILVB
> WHERE (TVBNr = new_rt.TVBNr)))
> >= 0)
> (EXECUTE PROCEDURE errorproc('some err_msg'));
>
>--Chris
Please log it with Informix as a bug.. the more stable we can make the
engine the better.
--
David Williams