Hi Tim.

I have in my project a table with an after insert and an after update trigger. Normally works fine but sometimes and I don't know why it seems that twice the amounts accumulated field. No raise error, working normally. I can't reproduce it because only occurs sometimes and I don't know how.

I can see that the amounts duplicated belong to a same sale document, namely, at a given time all records of a sale document twice amounts managed by triggers.

A similar thing occurs in the incident report 2871 but I think that this is not the problem because it was solved.

Any ideas?

Delphi 2007 and EDB 2.03 B13

Thank's

Francisco,

<< I have in my project a table with an after insert and an after update
trigger. Normally works fine but sometimes and I don't know why it seems
that twice the amounts accumulated field. No raise error, working normally.
I can't reproduce it because only occurs sometimes and I don't know how.

I can see that the amounts duplicated belong to a same sale document,
namely, at a given time all records of a sale document twice amounts managed
by triggers. >>

That really isn't much to go on.  I know that you're looping in your
triggers to handle potential row lock conflicts - are you sure that the
duplicate amounts aren't coming from that ?

<< A similar thing occurs in the incident report 2871 but I think that this
is not the problem because it was solved. >>

That was a completely different issue with a very specific error condition
(No active row).

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

>>That really isn't much to go on.

 I know but I have not yet been able to reproduce the problem at my pc.In my own tests all works fine.

>>  I know that you're looping in your
>> triggers to handle potential row lock conflicts - are you sure that the
>> duplicate amounts aren't coming from that ?

I am as I can be sure of that. I only know but now that all amount of differents tables only managed by triggers twice. I'm changing triggers in order to create a new table with all operations (after insert, after update and after delete) with oldrow and newrow values each time trigger shots to confirm it.

I'll tell to you when I know something else.

Thank's and regards.

Francisco,

<< I'll tell to you when I know something else. >>

Thanks.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

I'm changed my triggers and now insert a record to a new table called "MovimientosTablas" with some NEWROW and OLDROW values when insert or update records.

When I update a record I insert one in the new table to see that executes the AFTER UPDATE trigger, one more when I add new values to accumulated table and  one more when I rest the OLDROW values.

Trying it, I insert a record on the table and later I update it. Then I can see that when I insert the first record one of the fields have null values, the secord record have values OK and the third record is not in the table, then, by not executing the third step values in the accumulated table is wrong.

I insert one record and update it again and works fine. ¿?

part of the trigger:

   WHILE SALTE=FALSE DO
    START TRANSACTION ON TABLES Existencias;
    BEGIN

      PREPARE stmt FROM 'INSERT INTO "MovimientosTablas"  (TABLA,OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?,?)';
      OPEN TempCursor USING 'ENTERING AFTERUPDATE TRIGGER','NEWROW.ARTICULO='+NEWROW.ARTICULO+'/NEWROW.CANTIDAD='+CAST(NEWROW.CANTIDAD AS VARCHAR)+' OLDROW.ARTICULO='+OLDROW.ARTICULO+'/OLDROW.CANTIDAD='+CAST(OLDROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE',NEWROW.ALBARAN,NEWROW.CODIGO;

      PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?';
      OPEN TempCursor USING NEWROW.ARTICULO,NEWROW.ALMACEN;
      SET ACTUALIZA=True;
      IF ROWCOUNT(TempCursor)=0 THEN
        INSERT INTO TempCursor (Articulo,Almacen,Existencia,Iniciales,ExistInv,InicialesU,ExistU) values
        (NEWROW.Articulo,NEWROW.Almacen,-NEWROW.Cantidad,0,0,0,-NEWROW.CAJAS);
      ELSE
        FETCH FROM TempCursor (FECHAINV) INTO dFecha;
        IF NEWROW.FECHA<dFecha THEN
          Set ACTUALIZA=False;
        ELSE
          FETCH FROM TempCursor (EXISTENCIA) INTO Can1;
          FETCH FROM TempCursor (EXISTU) INTO Can2;
          SET Can1=Can1-NEWROW.CANTIDAD;
          SET Can2=Can2-NEWROW.CAJAS;
          EXECUTE IMMEDIATE 'UPDATE EXISTENCIAS SET EXISTENCIA=?,EXISTU=? WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?' USING Can1,Can2,NEWROW.ARTICULO,NEWROW.ALMACEN;

          PREPARE stmt FROM 'INSERT INTO "MovimientosTablas" (OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?)';
          OPEN TempCursor USING 'ARTICULO='+NEWROW.ARTICULO+'/Cant='+CAST(NEWROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE+',NEWROW.ALBARAN,NEWROW.CODIGO;

        END IF;
      END IF;
      PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?';
      OPEN TempCursor USING OLDROW.ARTICULO,OLDROW.ALMACEN;
      SET ACTUALIZA=True;
      IF ROWCOUNT(TempCursor)=0 THEN
        INSERT INTO TempCursor (Articulo,Almacen,Existencia,Iniciales,ExistInv,InicialesU,ExistU) values
        (OLDROW.Articulo,OLDROW.Almacen,OLDROW.Cantidad,0,0,0,OLDROW.CAJAS);
      ELSE
        FETCH FROM TempCursor (FECHAINV) INTO dFecha;
        IF OLDROW.FECHA<dFecha THEN
          Set ACTUALIZA=False;
        ELSE
          FETCH FROM TempCursor (EXISTENCIA) INTO Can1;
          FETCH FROM TempCursor (EXISTU) INTO Can2;
          SET Can1=Can1+OLDROW.CANTIDAD;
          SET Can2=Can2+OLDROW.CAJAS;
          EXECUTE IMMEDIATE 'UPDATE EXISTENCIAS SET EXISTENCIA=?,EXISTU=? WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?' USING Can1,Can2,OLDROW.ARTICULO,OLDROW.ALMACEN;

          PREPARE stmt FROM 'INSERT INTO "MovimientosTablas" (OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?)';
          OPEN TempCursor USING 'ARTICULO='+OLDROW.ARTICULO+'/Cant='+CAST(OLDROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE-',OLDROW.ALBARAN,OLDROW.CODIGO;

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


When it fails the field 'OBSERVA' of the first insert on table "MovimientosTablas" takes NULL value and the third insert is not at the table.

Thank's

Francisco,

<< I'm changed my triggers and now insert a record to a new table called
"MovimientosTablas" with some NEWROW and OLDROW values when insert or update
records.

When I update a record I insert one in the new table to see that executes
the AFTER UPDATE trigger, one more when I add new values to accumulated
table and  one more when I rest the OLDROW values.

Trying it, I insert a record on the table and later I update it. Then I can
see that when I insert the first record one of the fields have null values,
the secord record have values OK and the third record is not in the table,
then, by not executing the third step values in the accumulated table is
wrong.

I insert one record and update it again and works fine. ¿? >>

Can you send me a simple example of what you're doing (database catalog and
table files also, please), along with what you expect to occur vs. what is
actually occurring ?  I can then see what is happening in real time and give
you a complete explanation.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com