Hi.

Can't I create this AFTER ALL trigger for a table?

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(16,4);
 IF OPERATION()='DELETE' OR OPERATION()='UPDATE' THEN
   PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE ESP=?';
   OPEN TempCursor USING OLDROW.PROVEEDOR;
   FETCH FROM TempCursor ('TOTALC') INTO TempCantidad;
   UPDATE TempCursor SET 'TotalC'=TempCantidad-OLDROW.TOTALBASE;
 END IF;
 IF OPERATION()='INSERT' OR OPERATION()='UPDATE' THEN
   PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE ESP=?';
   OPEN TempCursor USING NEWROW.PROVEEDOR;
   FETCH FROM TempCursor ('TOTALC') INTO TempCantidad;
   UPDATE TempCursor SET 'TotalC'=TempCantidad+NEWROW.TOTALBASE;
 END IF;
END

Thank's

Sorry, this trigger works.

This is my bad trigger:

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad DECIMAL(16,4);
 DECLARE dFecha DATE;
 DECLARE SALTE Boolean DEFAULT FALSE;
 IF OLDROW.PROVEEDOR=NEWROW.PROVEEDOR AND OLDROW.FECHA=NEWROW.FECHA
   AND OLDROW.TOTALBASE=NEWROW.TOTALBASE THEN LEAVE;
 END IF;
 PREPARE stmt FROM 'SELECT * FROM DATOSEMPRESA';
 OPEN TempCursor;
 FETCH FROM TempCursor ('FECHAINI') INTO dFecha;
 If dFecha IS NULL OR dFecha<=OLDROW.FECHA THEN
   WHILE SALTE=False DO
     START TRANSACTION ON TABLES 'Proveedores';
     BEGIN
       IF OPERATION()='Delete' OR OPERATION()='Update' THEN
         PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE ESP=?';
         OPEN TempCursor USING OLDROW.PROVEEDOR;
         FETCH FROM TempCursor ('TOTALC') INTO TempCantidad;
         UPDATE TempCursor SET 'TotalC'=TempCantidad-OLDROW.TOTALBASE;
       END IF;
       IF OPERATION()='Insert' OR OPERATION()='Update' THEN
         PREPARE stmt FROM 'SELECT * FROM Proveedores WHERE CODIGO COLLATE ESP=?';
         OPEN TempCursor USING NEWROW.PROVEEDOR;
         FETCH FROM TempCursor ('TOTALC') INTO TempCantidad;
         UPDATE TempCursor SET 'TotalC'=TempCantidad+NEWROW.TOTALBASE;
         UPDATE TempCursor SET 'FECHAULTC'=NEWROW.FECHA;
       END IF;
       COMMIT;
       SET SALTE=TRUE;
     EXCEPTION
       ROLLBACK;
       IF ERRORCODE()<>1005 THEN
         RAISE;
       END IF;
     END;
   END WHILE;
 END IF;                                       
END

Ok.

I reply myself. This is my problem:

 If dFecha IS NULL OR dFecha<=OLDROW.FECHA THEN

Sorry for the inconveniences.

Francisco,

There is a problem with your first trigger, though.
It's here:

  IF OPERATION()='DELETE' OR OPERATION()='UPDATE' THEN

This is always false because the function OPERATION returns 'Delete', 'Update' and 'Insert' and not 'DELETE',  'Update' or 'INSERT'.


--
Fernando Dias
[Team Elevate]

Fernando.

>>There is a problem with your first trigger, though.
>>It's here:

>>   IF OPERATION()='DELETE' OR OPERATION()='UPDATE' THEN

>>This is always false because the function OPERATION returns 'Delete', 'Update' and 'Insert' and not 'DELETE',  >>'Update' or 'INSERT'.

Yes, you are rigth, I saw it after this post.

Thank's.