Hello.

I have this two tables:

Table: VENTASLINEAS
Fields: ARTICULO, VARCHAR
         ALMACEN, VARCHAR
         CANTIDAD, DECIMAL
Table: EXISTENCIAS
Fields: ARTICULO, VARCHAR
         ALMACEN, VARCHAR
         CANTIDAD, DECIMAL

I'm triying to create a trigger than when i append a record in VENTASLINEAS table goes to
EXISTENCIAS table and insert or increment the field CANTIDAD but i can't do it.

this is the trigger

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO="'+NEWROW.ARTICULO+'" AND
ALMACEN="'+NEWROW.ALMACEN+'"';
 OPEN TempCursor;
 IF EOF(TempCursor) THEN
   INSERT INTO TempCursor (Articulo,Almacen,Cantidad) values     
(NEWROW.Articulo,NEWROW.Almacen,NEWROW.Cantidad);
 ELSE
   EXECUTE IMMEDIATE 'UPDATE EXISTENCIAS SET CANTIDAD=CANTIDAD+NEWROW.CANTIDAD WHERE
ARTICULO="'+NEWROW.ARTICULO+'" AND ALMACEN="'+NEWROW.ALMACEN+'"';
 END IF;
END

Can you tell to me where is the error/s?

Thank you very mutch

Francisco,

<< I'm triying to create a trigger than when i append a record in
VENTASLINEAS table goes to EXISTENCIAS table and insert or increment the
field CANTIDAD but i can't do it. >>

The UPDATE statement was referring to the NEWROW row when it shouldn't have
been.  You want to use an UPDATE on the actual cursor like this instead, and
also a parameterized SELECT statement for the cursor will be a bit cleaner:

(I'm assuming that the CANTIDAD column is an INTEGER column, so make changes
as necessary)

BEGIN
 DECLARE TempCursor CURSOR FOR stmt;
 DECLARE TempCantidad INTEGER;
 PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO=? AND
ALMACEN=?';
 OPEN TempCursor USING NEWROW.ARTICULO,NEWROW.ALMACEN;
 IF ROWCOUNT(TempCursor)=0 THEN
   INSERT INTO TempCursor (Articulo,Almacen,Cantidad) values
(NEWROW.Articulo,NEWROW.Almacen,NEWROW.Cantidad);
 ELSE
   FETCH FROM TempCursor (CANTIDAD) INTO TempCantidad;
   UPDATE TempCursor SETCANTIDAD=TempCantidad+NEWROW.CANTIDAD;
 END IF;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Perfect, allow me to be grateful for your attention to me and to say to you that your
service of attention is marvellous.

Best reggards