TRIGGER "InsertaSalidasLineas"
BEGIN
  DECLARE TempCursor CURSOR FOR stmt;
  DECLARE TempCantidad DECIMAL(16,4);
  DECLARE sAux VARCHAR;
  DECLARE Actualiza BOOLEAN;
  DECLARE dFecha DATE;
  DECLARE Descripcio VARCHAR;                 
  DECLARE ControlaU VARCHAR;  
  DECLARE SALTE BOOLEAN DEFAULT FALSE;
  PREPARE stmt FROM 'SELECT * FROM DATOSEMPRESA';
  OPEN TempCursor;
  FETCH FROM TempCursor (FECHAINI) INTO dFecha;
  If dFecha IS NULL OR dFecha<=NEWROW.FECHA THEN  
   WHILE SALTE=FALSE DO
     START TRANSACTION ON TABLES Existencias;
     BEGIN
       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 TempCantidad;
           UPDATE TempCursor SET EXISTENCIA=TempCantidad-NEWROW.CANTIDAD;
           FETCH FROM TempCursor (EXISTU) INTO TempCantidad;
           UPDATE TempCursor SET EXISTU=TempCantidad-NEWROW.CAJAS; -- ERROR HERE
         END IF;
       END IF;
       COMMIT;
       SET SALTE=TRUE;
     EXCEPTION
       ROLLBACK;      
       IF ERRORCODE()<>1005 THEN
         RAISE;
       END IF;
     END;    
    END WHILE;

---

 Comments and Workarounds

---

The problem was with this query:

PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?';

The problem occurred when the ALMACEN parameter was NULL. EDB was not checking for NULL parameters properly and would incorrectly return rows where ALMACEN was NULL, when it should have returned 0 rows because <Column>=NULL always equals FALSE. So, there would be one row in the result set for the first UPDATE statement. However, after the UPDATE statement, EDB re-evaluates the WHERE clause for the row being updated to make sure that the row still falls inside of the WHERE clause. This evaluation works differently than the initial WHERE clause index scans, and it would
correctly filter out the one row with the NULL ALMACEN column, thus leaving 0 rows in the result set. Therefore, when the second UPDATE statement was executed, there was no current row and an exception occurred.

The workaround is to add an IS NULL check to the query:

PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO COLLATE ESP=? AND
ALMACEN COLLATE ESP=? AND ALMACEN IS NOT NULL';

---

 Resolution

---

Fixed Problem on 4/29/2010 in version 2.03 build 13

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial