Subject extremely slow ( delete trigger procedure ) Metin GĂ¶nen 2004-08-09T21:22:10Z
Hi , this is the second time I post this message to the group. If someone
can answer me I will be very happy.

I have a master stocks table and a detail stocks table. Below is the trigger
which starts after the detail table delete. If I delete let's say 100
records at one time, the computer almost stops and it lasts about five
minutes to end this trigger process. The total records in master table is
about 2000 and detail table is about 5000 records. I am testing this with
p4 1.8 / win XP. / FB 1.5 .

Can anyone suggest a better way to do this. I am really in trouble .

Metin Gonen

CREATE TRIGGER POSHAR_AFTER_DELETE FOR POSHAR AFTER DELETE POSITION 0
AS

DECLARE VARIABLE MTOPLAM DOUBLE PRECISION;
DECLARE VARIABLE MORT DOUBLE PRECISION;
DECLARE VARIABLE MORTAG DOUBLE PRECISION;
DECLARE VARIABLE MHIGH DOUBLE PRECISION;
DECLARE VARIABLE MLOW DOUBLE PRECISION;

BEGIN
MORT=0;
MTOPLAM=0;
MORTAG=0;
MHIGH=0;
MLOW=0;

/* HERE UPDATING MASTER STOCKS TABLE AND WRITING THE SUMS & AVERAGES
TO IT */

IF ((OLD.GCTIP='C') AND (OLD.DEPOTRANS='0') ) THEN
BEGIN
SELECT SUM(MIKTAR*CARPAN) ,
AVG(FIYATNET/CARPAN),
SUM(MIKTAR*FIYATNET),
MAX(FIYATNET/CARPAN),
MIN(FIYATNET/CARPAN)
FROM POSHAR WHERE GCTIP='C' AND STOOTO=OLD.STOOTO INTO
:MTOPLAM,
:MORT,
:MORTAG,
:MHIGH,
:MLOW;
UPDATE STOK
SET TOPCIK=TOPCIK-(OLD.MIKTAR*OLD.CARPAN),
ORTSAT=:MORT,
ORTSATAG=:MORTAG/:MTOPLAM,
HIGHSATIS=:MHIGH,
LOWSATIS=:MLOW
WHERE OTONUM=OLD.STOOTO;
END
IF ((OLD.GCTIP='G') AND (OLD.DEPOTRANS='0') ) THEN
BEGIN
SELECT SUM(MIKTAR*CARPAN) ,
AVG(FIYATNET/CARPAN),
SUM(MIKTAR*FIYATNET),
MAX(FIYATNET/CARPAN),
MIN(FIYATNET/CARPAN)
FROM POSHAR WHERE GCTIP='G' AND STOOTO=OLD.STOOTO INTO
:MTOPLAM,
:MORT,
:MORTAG,
:MHIGH,
:MLOW;
UPDATE STOK
SET TOPGIR=TOPGIR-(OLD.MIKTAR*OLD.CARPAN),
ORTMAL=:MORT,
ORTMALAG=:MORTAG/:MTOPLAM,
HIGHALIS=:MHIGH,
LOWALIS=:MLOW
WHERE OTONUM=OLD.STOOTO;
END

END

[Non-text portions of this message have been removed]