Hi Tim.

After our last conversation i'm reviewing and trying my triggers. I have this one:

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(12,4);
 WHILE TRUE DO
   START TRANSACTION ON TABLES Articulos;
   BEGIN
     PREPARE stmt FROM 'SELECT * FROM Articulos WHERE CODIGO COLLATE ESP_CI=?';
     OPEN TempCursor USING NEWROW.ARTICULO;
     FETCH FROM TempCursor (EXISTT) INTO TempCantidad;
     IF TempCantidad IS NULL THEN
       UPDATE TempCursor SET EXISTT=NEWROW.CANTIDAD;
     ELSE
       UPDATE TempCursor SET EXISTT=TempCantidad+(NEWROW.CANTIDAD);
     END IF;
   EXCEPTION
     ROLLBACK;
     RAISE ERROR CODE 10000 MESSAGE 'Error al grabar';
   END;
   LEAVE;
 END WHILE;
END

.... If I lock table "ARTICULOS" it gets me the message 'Error al grabar' but don't
continue trying, i think it comes out of trigger even with the "WHILE TRUE" because if i
wait it should gets me the same message until table "ARTICULOS" is free, isn't it?

Thank you

Sorry, this trigger is not correct, this is the new one:

BEGIN
DECLARE TempCursor CURSOR FOR stmt;
DECLARE TempCantidad DECIMAL(12,4);
WHILE TRUE DO
  START TRANSACTION ON TABLES Articulos;
  BEGIN
    PREPARE stmt FROM 'SELECT * FROM Articulos WHERE CODIGO COLLATE ESP_CI=?';
    OPEN TempCursor USING NEWROW.ARTICULO;
    FETCH FROM TempCursor (EXISTT) INTO TempCantidad;
    IF TempCantidad IS NULL THEN
      UPDATE TempCursor SET EXISTT=NEWROW.CANTIDAD;
    ELSE
      UPDATE TempCursor SET EXISTT=TempCantidad+(NEWROW.CANTIDAD);
    END IF;
    LEAVE;
  EXCEPTION
    ROLLBACK;
    RAISE ERROR CODE 10000 MESSAGE 'Error al grabar';
  END;
END WHILE;
END

Francisco,

<< ... If I lock table "ARTICULOS" it gets me the message 'Error al grabar'
but don't continue trying, i think it comes out of trigger even with the
"WHILE TRUE" because if i wait it should gets me the same message until
table "ARTICULOS" is free, isn't it? >>

No, a RAISE statement will cause an exception to be raised, which will cause
the current execution to stop at that point and unwind the call stack.

What you want is this:

BEGIN
DECLARE TempCursor CURSOR FOR stmt;
DECLARE TempCantidad DECIMAL(12,4);
WHILE TRUE DO
  START TRANSACTION ON TABLES Articulos;
  BEGIN
    PREPARE stmt FROM 'SELECT * FROM Articulos WHERE CODIGO COLLATE
ESP_CI=?';
    OPEN TempCursor USING NEWROW.ARTICULO;
    FETCH FROM TempCursor (EXISTT) INTO TempCantidad;
    IF TempCantidad IS NULL THEN
      UPDATE TempCursor SET EXISTT=NEWROW.CANTIDAD;
    ELSE
      UPDATE TempCursor SET EXISTT=TempCantidad+(NEWROW.CANTIDAD);
    END IF;
    COMMIT;
    LEAVE;
  EXCEPTION
    ROLLBACK;
  END;
END WHILE;
END

Of course, this results in the same issue as I mentioned before, namely that
you're ignoring any possible exceptions.  And that's not a good idea.   What
are you trying to avoid with the retries ?  Is is row locking exceptions ?
If so, then you should trap for them specifically in a BEGIN..EXCEPTION
block by using the ERRORCODE() function to determine the actual error code.

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=23&topic=504

It would go like this:

BEGIN
DECLARE TempCursor CURSOR FOR stmt;
DECLARE TempCantidad DECIMAL(12,4);
WHILE TRUE DO
  BEGIN
    PREPARE stmt FROM 'SELECT * FROM Articulos WHERE CODIGO COLLATE
ESP_CI=?';
    OPEN TempCursor USING NEWROW.ARTICULO;
    FETCH FROM TempCursor (EXISTT) INTO TempCantidad;
    IF TempCantidad IS NULL THEN
      UPDATE TempCursor SET EXISTT=NEWROW.CANTIDAD;
    ELSE
      UPDATE TempCursor SET EXISTT=TempCantidad+(NEWROW.CANTIDAD);
    END IF;
    LEAVE;
  EXCEPTION
      IF ERRORCODE() <> 1005 THEN
           RAISE;
      END IF;
  END;
END WHILE;
END

You'll notice two things here:

1) You don't need a transaction around a single row update.
2) The BEGIN..EXCEPTION block will ensure that you retry the update in case
of a row lock error.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim.

Great, this is what i wanted. I'll try it.

Best regards

Tim.

I have this trigger on table COMPRAS after insert:

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(12,4);
 DECLARE dFecha DATE;
 WHILE TRUE DO
   BEGIN
     PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE ESP_CI=?';
     OPEN TempCursor USING NEWROW.PROVEEDOR;
     FETCH FROM TempCursor (TOTALC) INTO TempCantidad;
     If TempCantidad IS NULL THEN
       UPDATE TempCursor SET TotalC=NEWROW.TOTALBASE;
     ELSE
       UPDATE TempCursor SET TotalC=TempCantidad+NEWROW.TOTALBASE;
     END IF;
     UPDATE TempCursor SET FECHAULTC=NEWROW.FECHA;
     LEAVE;
   EXCEPTION
     IF ERRORCODE() <> 1005 THEN
       RAISE;
     END IF;
   END;
 END WHILE;
END


.... I lock a record of table "PROVEEDORES" and append a record on table "COMPRAS",
trigger retry and retry (perfect), I unlock record of table "PROVEEDORES" but the trigger
continue retrying ¿?

Thank you.

mmmmmmmmm.....

I'm trying to append record in table "COMPRAS" without lock any record of table
"PROVEEDORES" and don't leave trigger, continue trying and trying. What's wrong?

Francisco,

<< ... I lock a record of table "PROVEEDORES" and append a record on table
"COMPRAS", trigger retry and retry (perfect), I unlock record of table
"PROVEEDORES" but the trigger continue retrying ¿? >>

Okay, I figured out what your problem is - you're not checking to see if the
query actually returns a row that can be updated.  You should use this
instead (notice that I combined the UPDATEs, also):

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(12,4);
 DECLARE dFecha DATE;
 WHILE TRUE DO
   BEGIN
     PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE
ESP_CI=?';
     OPEN TempCursor USING NEWROW.PROVEEDOR;
     IF ROWCOUNT(TempCursor)=1 THEN
         FETCH FROM TempCursor (TOTALC) INTO TempCantidad;
         If TempCantidad IS NULL THEN
           UPDATE TempCursor SET TotalC=NEWROW.TOTALBASE,
                                                       FECHAULTC=NEWROW.FECHA;
         ELSE
           UPDATE TempCursor SET TotalC=TempCantidad+NEWROW.TOTALBASE,
                                                       FECHAULTC=NEWROW.FECHA;
         END IF;
     END IF;
     LEAVE;
   EXCEPTION
     IF ERRORCODE() <> 1005 THEN
       RAISE;
     END IF;
   END;
 END WHILE;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Francisco,

Actually, you also need to change the trigger so that it looks like this
(the LEAVE statement is leaving the current BEGIN..EXCEPTION block, not the
WHILE loop):

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(12,4);
 DECLARE dFecha DATE;
 DECLARE UpdateComplete BOOLEAN DEFAULT FALSE;

 PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE
ESP_CI=?';
 OPEN TempCursor USING NEWROW.PROVEEDOR;
  IF ROWCOUNT(TempCursor)=1 THEN
      FETCH FROM TempCursor (TOTALC) INTO TempCantidad;
     WHILE (NOT UpdateComplete) DO
       BEGIN
             If TempCantidad IS NULL THEN
               UPDATE TempCursor SET TotalC=NEWROW.TOTALBASE,
                                                           FECHAULTC=NEWROW.FECHA;
             ELSE
               UPDATE TempCursor SET TotalC=TempCantidad+NEWROW.TOTALBASE,
                                                           FECHAULTC=NEWROW.FECHA;
             END IF;
          SET UpdateComplete = TRUE;
       EXCEPTION
         IF ERRORCODE() <> 1005 THEN
           RAISE;
         END IF;
       END;
     END WHILE;
  END IF;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes... i can see.

Thank you