Hi.

I have a process that append/delete a lot of records in a table with triggers and it is
very slow. Are there any way to perform this process working with memory and write to disk
at last?

Thank you.

Francisco

>I have a process that append/delete a lot of records in a table with triggers and it is
>very slow. Are there any way to perform this process working with memory and write to disk
>at last?

1. You can wrap the process in a transaction. You may have to set COMMIT at intervals otherwise with a lot of records you may run out of memory.

2. If the triggers aren't needed as part of this process you can turn them off and then back on again.

I really recommend 2 if you can. For my import process it reduced the time c 70%

Roy Lambert [Team Elevate]

Roy.

>>1. You can wrap the process in a transaction. You may have to set COMMIT at intervals
otherwise with a lot of >>records you may run out of memory.

>>2. If the triggers aren't needed as part of this process you can turn them off and then
back on again.

>>I really recommend 2 if you can. For my import process it reduced the time c 70%

Triggers are essential in this process and I can't turn them off then I choose the second
option but don't works and I can't raise the error because this line don't works in delphi
2007 and EDB 2.02 b15.

DataModule1.EDBDatabase1.raise;   -----> don't compile it (E2029 Identifier expected but
'RAISE' found

Do you know why?

Thanks

Francisco


>DataModule1.EDBDatabase1.raise; -----> don't compile it (E2029 Identifier expected but
>'RAISE' found
>
>Do you know why?

Its not valid code If there's an error ElevateDB should raise it itself. If you handle it but want to pass it on just use raise by itself.

Can you post the code that you're trying to use and someone may be able to help

Roy Lambert [Team Elevate]

Francisco

If you could then set Exclusive to True and it will improve the performance.

Eduardo

Eduardo.

Thanks for your help but I can't set Exclusive to True by code because triggers don't works.

Roy.

My code is easy:

 DataModule1.EDBDatabase1.StartTransaction(EmptyEDBStringsArray);
 try
   Table.First;
   While not Table.Eof do begin
     {some lines of code that works fine}
     Table.delete;
   end;
   DataModule1.EDBDatabase1.commit;
 except
   DataModule1.EDBDatabase1.RollBack;
 end;

In line "Table.delete" goes to rollback with no error and exit, any ideas?

Thanks

Francisco

> DataModule1.EDBDatabase1.StartTransaction(EmptyEDBStringsArray);
> try
> Table.First;
> While not Table.Eof do begin
> {some lines of code that works fine}
> Table.delete;
> end;
> DataModule1.EDBDatabase1.commit;
> except
> DataModule1.EDBDatabase1.RollBack;
> end;
>
> In line "Table.delete" goes to rollback with no error and exit, any ideas?

Yup - its most probably a delete trigger 1) do you have one of those for the table and 2) can you show the code.

Roy Lambert [Team Elevate]

Roy.

Here are the before delete trigger of table that I'm delete records, if you think is short
I can add more lines :P

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE Debe DECIMAL(16,2);
 DECLARE Haber DECIMAL(16,2);
 DECLARE TotalDebe,TotalHaber DECIMAL(16,2);
 DECLARE
Debe01,Debe02,Debe03,Debe04,Debe05,Debe06,Debe07,Debe08,Debe09,Debe10,Debe11,Debe12
DECIMAL(16,2);
 DECLARE
Haber01,Haber02,Haber03,Haber04,Haber05,Haber06,Haber07,Haber08,Haber09,Haber10,Haber11,Haber12
DECIMAL(16,2);
 DECLARE sAux VARCHAR;
 DECLARE Cuenta VARCHAR;                      
 DECLARE Mes VARCHAR;         
 DECLARE Nivel1,Nivel2,Nivel3,Nivel4,Nivel5 SMALLINT;
 DECLARE SALTE Boolean DEFAULT FALSE;
 SET Mes=CAST(EXTRACT(MONTH FROM OLDROW.Fecha) AS VARCHAR(2));
 PREPARE stmt FROM 'SELECT * FROM ASIENTOS WHERE ASIENTO=?';
 OPEN TempCursor USING OLDROW.ASIENTO;
 IF ROWCOUNT(TempCursor)>0 THEN
   DELETE FROM TempCursor;
 END IF;
 IF LENGTH(MES)=1 THEN
   SET Mes='0'+Mes;
 END IF;
 SET SALTE=FALSE;
 WHILE SALTE=FALSE DO
   START TRANSACTION ON TABLES AcumuladosCanal, Cuentas, DatosEmpresa;
   BEGIN
   PREPARE stmt FROM 'SELECT * FROM DATOSEMPRESA';
   OPEN TempCursor;
   FETCH FROM TempCursor (TOTALDEBE,TOTALHABER,NIVEL1,NIVEL2,NIVEL3,NIVEL4,NIVEL5) INTO
TotalDebe,TotalHaber,Nivel1,Nivel2,Nivel3,Nivel4,Nivel5;
   UPDATE TempCursor Set TOTALDEBE=TotalDebe-OLDROW.DEBE,TotalHaber=TotalHaber-OLDROW.Haber;
   If Nivel1>0 THEN
     SET Cuenta=LEFT(OLDROW.CUENTA,Nivel1);
     PREPARE stmt FROM 'SELECT * FROM ACUMULADOSCANAL WHERE CUENTA COLLATE ESP=? AND
CANAL COLLATE ESP=? AND MES COLLATE ESP=?';
     OPEN TempCursor USING Cuenta,OLDROW.CANAL,Mes;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor (Cuenta,Canal,Mes,Debe,Haber) values
        (Cuenta,OLDROW.Canal,Mes,OLDROW.Debe,OLDROW.Haber);
     ELSE
       FETCH FROM TempCursor (DEBE,HABER) INTO Debe,Haber;
       SET Debe=Debe-OLDROW.Debe;
       SET Haber=Haber-OLDROW.Haber;
       UPDATE TempCursor SET DEBE=Debe,HABER=Haber;
     END IF;
     PREPARE stmt FROM 'SELECT * FROM CUENTAS WHERE CODIGO COLLATE ESP=?';
     OPEN TempCursor USING Cuenta;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor
(Codigo,Nombre,Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12)
values
        (Cuenta,'CTA.CREADA DE FORMA
AUTOMATICA',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
       SET
Debe=0,Debe01=0,Debe02=0,Debe03=0,Debe04=0,Debe05=0,Debe06=0,Debe07=0,Debe08=0,Debe09=0,Debe10=0,Debe11=0,Debe12=0;
       SET
Haber=0,Haber01=0,Haber02=0,Haber03=0,Haber04=0,Haber05=0,Haber06=0,Haber07=0,Haber08=0,Haber09=0,Haber10=0,Haber11=0,Haber12=0;
     ELSE
       FETCH FROM TempCursor
(DEBE,HABER,DEBE01,HABER01,DEBE02,HABER02,DEBE03,HABER03,DEBE04,HABER04,DEBE05,HABER05,DEBE06,HABER06,
        
DEBE07,HABER07,DEBE08,HABER08,DEBE09,HABER09,DEBE10,HABER10,DEBE11,HABER11,DEBE12,HABER12)
         INTO
Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,
           
Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12;
     END IF;
     If Mes='01' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe01=Debe01-OLDROW.DEBE,Haber01=Haber01-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE01=Debe01,HABER01=Haber01;
     END IF;
     If Mes='02' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe02=Debe02-OLDROW.DEBE,Haber02=Haber02-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE02=Debe02,HABER02=Haber02;
     END IF;
     If Mes='03' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe03=Debe03-OLDROW.DEBE,Haber03=Haber03-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE03=Debe03,HABER03=Haber03;
     END IF;
     If Mes='04' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe04=Debe04-OLDROW.DEBE,Haber04=Haber04-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE04=Debe04,HABER04=Haber04;
     END IF;
     If Mes='05' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe05=Debe05-OLDROW.DEBE,Haber05=Haber05-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE05=Debe05,HABER05=Haber05;
     END IF;
     If Mes='06' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe06=Debe06-OLDROW.DEBE,Haber06=Haber06-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE06=Debe06,HABER06=Haber06;
     END IF;
     If Mes='07' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe07=Debe07-OLDROW.DEBE,Haber07=Haber07-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE07=Debe07,HABER07=Haber07;
     END IF;
     If Mes='08' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe08=Debe08-OLDROW.DEBE,Haber08=Haber08-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE08=Debe08,HABER08=Haber08;
     END IF;
     If Mes='09' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe09=Debe09-OLDROW.DEBE,Haber09=Haber09-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE09=Debe09,HABER09=Haber09;
     END IF;
     If Mes='10' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe10=Debe10-OLDROW.DEBE,Haber10=Haber10-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE10=Debe10,HABER10=Haber10;
     END IF;
     If Mes='11' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe11=Debe11-OLDROW.DEBE,Haber11=Haber11-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE11=Debe11,HABER11=Haber11;
     END IF;
     If Mes='12' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe12=Debe12-OLDROW.DEBE,Haber12=Haber12-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE12=Debe12,HABER12=Haber12;
     END IF;
   END IF;
   If Nivel2>0 THEN
     SET Cuenta=LEFT(OLDROW.CUENTA,Nivel2);
     PREPARE stmt FROM 'SELECT * FROM ACUMULADOSCANAL WHERE CUENTA COLLATE ESP=? AND
CANAL COLLATE ESP=? AND MES COLLATE ESP=?';
     OPEN TempCursor USING Cuenta,OLDROW.CANAL,Mes;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor (Cuenta,Canal,Mes,Debe,Haber) values
        (Cuenta,OLDROW.Canal,Mes,OLDROW.Debe,OLDROW.Haber);
     ELSE
       FETCH FROM TempCursor (DEBE,HABER) INTO Debe,Haber;
       SET Debe=Debe-OLDROW.Debe;
       SET Haber=Haber-OLDROW.Haber;
       UPDATE TempCursor SET DEBE=Debe,HABER=Haber;
     END IF;
     PREPARE stmt FROM 'SELECT * FROM CUENTAS WHERE CODIGO COLLATE ESP=?';
     OPEN TempCursor USING Cuenta;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor
(Codigo,Nombre,Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12)
values
        (Cuenta,'CTA.CREADA DE FORMA
AUTOMATICA',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
       SET
Debe=0,Debe01=0,Debe02=0,Debe03=0,Debe04=0,Debe05=0,Debe06=0,Debe07=0,Debe08=0,Debe09=0,Debe10=0,Debe11=0,Debe12=0;
       SET
Haber=0,Haber01=0,Haber02=0,Haber03=0,Haber04=0,Haber05=0,Haber06=0,Haber07=0,Haber08=0,Haber09=0,Haber10=0,Haber11=0,Haber12=0;
     ELSE
       FETCH FROM TempCursor
(DEBE,HABER,DEBE01,HABER01,DEBE02,HABER02,DEBE03,HABER03,DEBE04,HABER04,DEBE05,HABER05,DEBE06,HABER06,
        
DEBE07,HABER07,DEBE08,HABER08,DEBE09,HABER09,DEBE10,HABER10,DEBE11,HABER11,DEBE12,HABER12)
         INTO
Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,
           
Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12;
     END IF;
     If Mes='01' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe01=Debe01-OLDROW.DEBE,Haber01=Haber01-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE01=Debe01,HABER01=Haber01;
     END IF;
     If Mes='02' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe02=Debe02-OLDROW.DEBE,Haber02=Haber02-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE02=Debe02,HABER02=Haber02;
     END IF;
     If Mes='03' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe03=Debe03-OLDROW.DEBE,Haber03=Haber03-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE03=Debe03,HABER03=Haber03;
     END IF;
     If Mes='04' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe04=Debe04-OLDROW.DEBE,Haber04=Haber04-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE04=Debe04,HABER04=Haber04;
     END IF;
     If Mes='05' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe05=Debe05-OLDROW.DEBE,Haber05=Haber05-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE05=Debe05,HABER05=Haber05;
     END IF;
     If Mes='06' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe06=Debe06-OLDROW.DEBE,Haber06=Haber06-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE06=Debe06,HABER06=Haber06;
     END IF;
     If Mes='07' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe07=Debe07-OLDROW.DEBE,Haber07=Haber07-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE07=Debe07,HABER07=Haber07;
     END IF;
     If Mes='08' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe08=Debe08-OLDROW.DEBE,Haber08=Haber08-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE08=Debe08,HABER08=Haber08;
     END IF;
     If Mes='09' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe09=Debe09-OLDROW.DEBE,Haber09=Haber09-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE09=Debe09,HABER09=Haber09;
     END IF;
     If Mes='10' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe10=Debe10-OLDROW.DEBE,Haber10=Haber10-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE10=Debe10,HABER10=Haber10;
     END IF;
     If Mes='11' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe11=Debe11-OLDROW.DEBE,Haber11=Haber11-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE11=Debe11,HABER11=Haber11;
     END IF;
     If Mes='12' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe12=Debe12-OLDROW.DEBE,Haber12=Haber12-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE12=Debe12,HABER12=Haber12;
     END IF;
   END IF;
   If Nivel3>0 THEN
     SET Cuenta=LEFT(OLDROW.CUENTA,Nivel3);
     PREPARE stmt FROM 'SELECT * FROM ACUMULADOSCANAL WHERE CUENTA COLLATE ESP=? AND
CANAL COLLATE ESP=? AND MES COLLATE ESP=?';
     OPEN TempCursor USING Cuenta,OLDROW.CANAL,Mes;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor (Cuenta,Canal,Mes,Debe,Haber) values
        (Cuenta,OLDROW.Canal,Mes,OLDROW.Debe,OLDROW.Haber);
     ELSE
       FETCH FROM TempCursor (DEBE,HABER) INTO Debe,Haber;
       SET Debe=Debe-OLDROW.Debe;
       SET Haber=Haber-OLDROW.Haber;
       UPDATE TempCursor SET DEBE=Debe,HABER=Haber;
     END IF;
     PREPARE stmt FROM 'SELECT * FROM CUENTAS WHERE CODIGO COLLATE ESP=?';
     OPEN TempCursor USING Cuenta;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor
(Codigo,Nombre,Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12)
values
        (Cuenta,'CTA.CREADA DE FORMA
AUTOMATICA',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
       SET
Debe=0,Debe01=0,Debe02=0,Debe03=0,Debe04=0,Debe05=0,Debe06=0,Debe07=0,Debe08=0,Debe09=0,Debe10=0,Debe11=0,Debe12=0;
       SET
Haber=0,Haber01=0,Haber02=0,Haber03=0,Haber04=0,Haber05=0,Haber06=0,Haber07=0,Haber08=0,Haber09=0,Haber10=0,Haber11=0,Haber12=0;
     ELSE
       FETCH FROM TempCursor
(DEBE,HABER,DEBE01,HABER01,DEBE02,HABER02,DEBE03,HABER03,DEBE04,HABER04,DEBE05,HABER05,DEBE06,HABER06,
        
DEBE07,HABER07,DEBE08,HABER08,DEBE09,HABER09,DEBE10,HABER10,DEBE11,HABER11,DEBE12,HABER12)
         INTO
Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,
           
Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12;
     END IF;
     If Mes='01' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe01=Debe01-OLDROW.DEBE,Haber01=Haber01-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE01=Debe01,HABER01=Haber01;
     END IF;
     If Mes='02' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe02=Debe02-OLDROW.DEBE,Haber02=Haber02-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE02=Debe02,HABER02=Haber02;
     END IF;
     If Mes='03' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe03=Debe03-OLDROW.DEBE,Haber03=Haber03-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE03=Debe03,HABER03=Haber03;
     END IF;
     If Mes='04' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe04=Debe04-OLDROW.DEBE,Haber04=Haber04-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE04=Debe04,HABER04=Haber04;
     END IF;
     If Mes='05' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe05=Debe05-OLDROW.DEBE,Haber05=Haber05-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE05=Debe05,HABER05=Haber05;
     END IF;
     If Mes='06' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe06=Debe06-OLDROW.DEBE,Haber06=Haber06-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE06=Debe06,HABER06=Haber06;
     END IF;
     If Mes='07' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe07=Debe07-OLDROW.DEBE,Haber07=Haber07-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE07=Debe07,HABER07=Haber07;
     END IF;
     If Mes='08' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe08=Debe08-OLDROW.DEBE,Haber08=Haber08-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE08=Debe08,HABER08=Haber08;
     END IF;
     If Mes='09' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe09=Debe09-OLDROW.DEBE,Haber09=Haber09-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE09=Debe09,HABER09=Haber09;
     END IF;
     If Mes='10' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe10=Debe10-OLDROW.DEBE,Haber10=Haber10-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE10=Debe10,HABER10=Haber10;
     END IF;
     If Mes='11' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe11=Debe11-OLDROW.DEBE,Haber11=Haber11-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE11=Debe11,HABER11=Haber11;
     END IF;
     If Mes='12' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe12=Debe12-OLDROW.DEBE,Haber12=Haber12-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE12=Debe12,HABER12=Haber12;
     END IF;
   END IF;
   If Nivel4>0 THEN
     SET Cuenta=LEFT(OLDROW.CUENTA,Nivel4);
     PREPARE stmt FROM 'SELECT * FROM ACUMULADOSCANAL WHERE CUENTA COLLATE ESP=? AND
CANAL COLLATE ESP=? AND MES COLLATE ESP=?';
     OPEN TempCursor USING Cuenta,OLDROW.CANAL,Mes;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor (Cuenta,Canal,Mes,Debe,Haber) values
        (Cuenta,OLDROW.Canal,Mes,OLDROW.Debe,OLDROW.Haber);
     ELSE
       FETCH FROM TempCursor (DEBE,HABER) INTO Debe,Haber;
       SET Debe=Debe-OLDROW.Debe;
       SET Haber=Haber-OLDROW.Haber;
       UPDATE TempCursor SET DEBE=Debe,HABER=Haber;
     END IF;
     PREPARE stmt FROM 'SELECT * FROM CUENTAS WHERE CODIGO COLLATE ESP=?';
     OPEN TempCursor USING Cuenta;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor
(Codigo,Nombre,Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12)
values
        (Cuenta,'CTA.CREADA DE FORMA
AUTOMATICA',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
       SET
Debe=0,Debe01=0,Debe02=0,Debe03=0,Debe04=0,Debe05=0,Debe06=0,Debe07=0,Debe08=0,Debe09=0,Debe10=0,Debe11=0,Debe12=0;
       SET
Haber=0,Haber01=0,Haber02=0,Haber03=0,Haber04=0,Haber05=0,Haber06=0,Haber07=0,Haber08=0,Haber09=0,Haber10=0,Haber11=0,Haber12=0;
     ELSE
       FETCH FROM TempCursor
(DEBE,HABER,DEBE01,HABER01,DEBE02,HABER02,DEBE03,HABER03,DEBE04,HABER04,DEBE05,HABER05,DEBE06,HABER06,
        
DEBE07,HABER07,DEBE08,HABER08,DEBE09,HABER09,DEBE10,HABER10,DEBE11,HABER11,DEBE12,HABER12)
         INTO
Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,
           
Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12;
     END IF;
     If Mes='01' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe01=Debe01-OLDROW.DEBE,Haber01=Haber01-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE01=Debe01,HABER01=Haber01;
     END IF;
     If Mes='02' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe02=Debe02-OLDROW.DEBE,Haber02=Haber02-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE02=Debe02,HABER02=Haber02;
     END IF;
     If Mes='03' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe03=Debe03-OLDROW.DEBE,Haber03=Haber03-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE03=Debe03,HABER03=Haber03;
     END IF;
     If Mes='04' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe04=Debe04-OLDROW.DEBE,Haber04=Haber04-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE04=Debe04,HABER04=Haber04;
     END IF;
     If Mes='05' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe05=Debe05-OLDROW.DEBE,Haber05=Haber05-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE05=Debe05,HABER05=Haber05;
     END IF;
     If Mes='06' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe06=Debe06-OLDROW.DEBE,Haber06=Haber06-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE06=Debe06,HABER06=Haber06;
     END IF;
     If Mes='07' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe07=Debe07-OLDROW.DEBE,Haber07=Haber07-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE07=Debe07,HABER07=Haber07;
     END IF;
     If Mes='08' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe08=Debe08-OLDROW.DEBE,Haber08=Haber08-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE08=Debe08,HABER08=Haber08;
     END IF;
     If Mes='09' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe09=Debe09-OLDROW.DEBE,Haber09=Haber09-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE09=Debe09,HABER09=Haber09;
     END IF;
     If Mes='10' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe10=Debe10-OLDROW.DEBE,Haber10=Haber10-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE10=Debe10,HABER10=Haber10;
     END IF;
     If Mes='11' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe11=Debe11-OLDROW.DEBE,Haber11=Haber11-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE11=Debe11,HABER11=Haber11;
     END IF;
     If Mes='12' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe12=Debe12-OLDROW.DEBE,Haber12=Haber12-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE12=Debe12,HABER12=Haber12;
     END IF;
   END IF;
   If Nivel5>0 THEN
     SET Cuenta=LEFT(OLDROW.CUENTA,Nivel5);
     PREPARE stmt FROM 'SELECT * FROM ACUMULADOSCANAL WHERE CUENTA COLLATE ESP=? AND
CANAL COLLATE ESP=? AND MES COLLATE ESP=?';
     OPEN TempCursor USING Cuenta,OLDROW.CANAL,Mes;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor (Cuenta,Canal,Mes,Debe,Haber) values
        (Cuenta,OLDROW.Canal,Mes,OLDROW.Debe,OLDROW.Haber);
     ELSE
       FETCH FROM TempCursor (DEBE,HABER) INTO Debe,Haber;
       SET Debe=Debe-OLDROW.Debe;
       SET Haber=Haber-OLDROW.Haber;
       UPDATE TempCursor SET DEBE=Debe,HABER=Haber;
     END IF;
     PREPARE stmt FROM 'SELECT * FROM CUENTAS WHERE CODIGO COLLATE ESP=?';
     OPEN TempCursor USING Cuenta;
     IF ROWCOUNT(TempCursor)=0 THEN
       INSERT INTO TempCursor
(Codigo,Nombre,Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12)
values
        (Cuenta,'CTA.CREADA DE FORMA
AUTOMATICA',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
       SET
Debe=0,Debe01=0,Debe02=0,Debe03=0,Debe04=0,Debe05=0,Debe06=0,Debe07=0,Debe08=0,Debe09=0,Debe10=0,Debe11=0,Debe12=0;
       SET
Haber=0,Haber01=0,Haber02=0,Haber03=0,Haber04=0,Haber05=0,Haber06=0,Haber07=0,Haber08=0,Haber09=0,Haber10=0,Haber11=0,Haber12=0;
     ELSE
       FETCH FROM TempCursor
(DEBE,HABER,DEBE01,HABER01,DEBE02,HABER02,DEBE03,HABER03,DEBE04,HABER04,DEBE05,HABER05,DEBE06,HABER06,
        
DEBE07,HABER07,DEBE08,HABER08,DEBE09,HABER09,DEBE10,HABER10,DEBE11,HABER11,DEBE12,HABER12)
         INTO
Debe,Haber,Debe01,Haber01,Debe02,Haber02,Debe03,Haber03,Debe04,Haber04,Debe05,Haber05,Debe06,Haber06,
           
Debe07,Haber07,Debe08,Haber08,Debe09,Haber09,Debe10,Haber10,Debe11,Haber11,Debe12,Haber12;
     END IF;
     If Mes='01' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe01=Debe01-OLDROW.DEBE,Haber01=Haber01-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE01=Debe01,HABER01=Haber01;
     END IF;
     If Mes='02' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe02=Debe02-OLDROW.DEBE,Haber02=Haber02-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE02=Debe02,HABER02=Haber02;
     END IF;
     If Mes='03' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe03=Debe03-OLDROW.DEBE,Haber03=Haber03-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE03=Debe03,HABER03=Haber03;
     END IF;
     If Mes='04' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe04=Debe04-OLDROW.DEBE,Haber04=Haber04-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE04=Debe04,HABER04=Haber04;
     END IF;
     If Mes='05' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe05=Debe05-OLDROW.DEBE,Haber05=Haber05-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE05=Debe05,HABER05=Haber05;
     END IF;
     If Mes='06' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe06=Debe06-OLDROW.DEBE,Haber06=Haber06-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE06=Debe06,HABER06=Haber06;
     END IF;
     If Mes='07' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe07=Debe07-OLDROW.DEBE,Haber07=Haber07-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE07=Debe07,HABER07=Haber07;
     END IF;
     If Mes='08' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe08=Debe08-OLDROW.DEBE,Haber08=Haber08-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE08=Debe08,HABER08=Haber08;
     END IF;
     If Mes='09' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe09=Debe09-OLDROW.DEBE,Haber09=Haber09-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE09=Debe09,HABER09=Haber09;
     END IF;
     If Mes='10' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe10=Debe10-OLDROW.DEBE,Haber10=Haber10-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE10=Debe10,HABER10=Haber10;
     END IF;
     If Mes='11' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe11=Debe11-OLDROW.DEBE,Haber11=Haber11-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE11=Debe11,HABER11=Haber11;
     END IF;
     If Mes='12' THEN
       SET
Debe=Debe-OLDROW.Debe,Haber=Haber-OLDROW.Haber,Debe12=Debe12-OLDROW.DEBE,Haber12=Haber12-OLDROW.HABER;
       UPDATE TempCursor SET
SALDO=DEBE-HABER,DEBE=Debe,HABER=Haber,DEBE12=Debe12,HABER12=Haber12;
     END IF;
   END IF;

   COMMIT;
   SET SALTE=TRUE;
   EXCEPTION
     ROLLBACK;
     IF ERRORCODE()<>1005 THEN
       RAISE;
     END IF;
   END;
 END WHILE;

END

Francisco,

<< In line "Table.delete" goes to rollback with no error and exit, any
ideas? >>

Why don't you just add this:

DataModule1.EDBDatabase1.StartTransaction(EmptyEDBStringsArray);
 try
   Table.First;
   While not Table.Eof do begin
     {some lines of code that works fine}
     Table.delete;
   end;
   DataModule1.EDBDatabase1.commit;
 except
   DataModule1.EDBDatabase1.RollBack;
   raise
 end;

So you can see what the error message is ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Francisco


Yes they are a bit short aren't they

It will take me some time to work through them (hopefully others in the community will have a look as well). In addition to Tim's suggestion what happens if you just run the triggers as a script in EDBManager - do they run OK?



Roy Lambert [Team Elevate]