Hi Jonas,

Am Sonntag, 2. November 2003 14:43 schrieb Datatal AB - Gauffin, Jonas:

> Hello
>
> I got a trigger (before delete) on a table called bokning. I got a
> identical table called bokning_historik that all old entries should be
> moved to by the trigger. I also got a table called bokning_passagerare that
> contains all passagers from the booking. (and a table called
> bokning_passagerare_historik).
>
> bokning_passagerare.bokningsnr is a FK to bokning.bokningsnr

What did you have choosen on delete? If you have choosen cascade, then there
is no need to delete the row in bokning_passagerare. That is done by the FK.
Then you can also write a trigger before delete for bokning_passagerare.

> bokning_passagerare_historik.bokningsnr is a FK to
> bokning_historik.bokningsnr
>
> bokning before delete trigger looks like this:
> -------------
> insert into bokning_historik (bokningsnr, flight_nr, avg_datum,
> bokad_av, faktura_adress) values(old.bokningsnr, old.flight_nr,
> old.avg_datum, old.bokad_av, old.faktura_adress);
>
> insert into bokning_passagerare_historik (bokningsnr, platsnr,
> personnr, pris, prisnamn) select bokningsnr, platsnr, personnr, pris,
> prisnamn
> from bokning_passagerare where bokningsnr=old.bokningsnr ;
>
> delete from bokning_passagerare where bokningsnr = old.bokningsnr;
> ---------------
>
> it fails on bokning_passagerare_historik insert as if nothing have been
> inserted into bokning_historik.

How ist it possible? You delete a row in bokning. Befor deleting the trigger
insert this row into bokning_historik. So this row in bokning_historik MUST
exist. Of course, it isn't commited at that time, but it exists.

>
> well. I can move some of the statements to a bokning_passagerare trigger,
> but then I gotto check if the booking exist in bokning_historik and just
> discard the passagerrow if it doesnt.