Re: create trigger crashes online
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