Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
About trigger |
Thu, Nov 13 2008 5:28 AM | Permanent Link |
Francisco Fernandez | 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 |
Thu, Nov 13 2008 5:34 AM | Permanent Link |
Francisco Fernandez | 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 |
Thu, Nov 13 2008 3:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Nov 14 2008 1:20 PM | Permanent Link |
Francisco Fernandez | Tim.
Great, this is what i wanted. I'll try it. Best regards |
Sat, Nov 15 2008 4:02 AM | Permanent Link |
Francisco Fernandez | 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. |
Sat, Nov 15 2008 5:13 AM | Permanent Link |
Francisco Fernandez | 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? |
Sat, Nov 15 2008 4:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sat, Nov 15 2008 4:41 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Sun, Nov 16 2008 3:11 AM | Permanent Link |
Francisco Fernandez | Yes... i can see.
Thank you |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |