Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Triggers again |
Thu, Jun 3 2010 4:24 AM | Permanent Link |
Francisco Fernandez NewTRON Informatica | Hi Tim.
I have in my project a table with an after insert and an after update trigger. Normally works fine but sometimes and I don't know why it seems that twice the amounts accumulated field. No raise error, working normally. I can't reproduce it because only occurs sometimes and I don't know how. I can see that the amounts duplicated belong to a same sale document, namely, at a given time all records of a sale document twice amounts managed by triggers. A similar thing occurs in the incident report 2871 but I think that this is not the problem because it was solved. Any ideas? Delphi 2007 and EDB 2.03 B13 Thank's |
Sat, Jun 5 2010 12:43 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Francisco,
<< I have in my project a table with an after insert and an after update trigger. Normally works fine but sometimes and I don't know why it seems that twice the amounts accumulated field. No raise error, working normally. I can't reproduce it because only occurs sometimes and I don't know how. I can see that the amounts duplicated belong to a same sale document, namely, at a given time all records of a sale document twice amounts managed by triggers. >> That really isn't much to go on. I know that you're looping in your triggers to handle potential row lock conflicts - are you sure that the duplicate amounts aren't coming from that ? << A similar thing occurs in the incident report 2871 but I think that this is not the problem because it was solved. >> That was a completely different issue with a very specific error condition (No active row). -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Jun 6 2010 3:46 AM | Permanent Link |
Francisco Fernandez NewTRON Informatica | Tim.
>>That really isn't much to go on. I know but I have not yet been able to reproduce the problem at my pc.In my own tests all works fine. >> I know that you're looping in your >> triggers to handle potential row lock conflicts - are you sure that the >> duplicate amounts aren't coming from that ? I am as I can be sure of that. I only know but now that all amount of differents tables only managed by triggers twice. I'm changing triggers in order to create a new table with all operations (after insert, after update and after delete) with oldrow and newrow values each time trigger shots to confirm it. I'll tell to you when I know something else. Thank's and regards. |
Sun, Jun 6 2010 5:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Francisco,
<< I'll tell to you when I know something else. >> Thanks. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Jun 8 2010 3:40 PM | Permanent Link |
Francisco Fernandez NewTRON Informatica | Tim.
I'm changed my triggers and now insert a record to a new table called "MovimientosTablas" with some NEWROW and OLDROW values when insert or update records. When I update a record I insert one in the new table to see that executes the AFTER UPDATE trigger, one more when I add new values to accumulated table and one more when I rest the OLDROW values. Trying it, I insert a record on the table and later I update it. Then I can see that when I insert the first record one of the fields have null values, the secord record have values OK and the third record is not in the table, then, by not executing the third step values in the accumulated table is wrong. I insert one record and update it again and works fine. ¿? part of the trigger: WHILE SALTE=FALSE DO START TRANSACTION ON TABLES Existencias; BEGIN PREPARE stmt FROM 'INSERT INTO "MovimientosTablas" (TABLA,OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?,?)'; OPEN TempCursor USING 'ENTERING AFTERUPDATE TRIGGER','NEWROW.ARTICULO='+NEWROW.ARTICULO+'/NEWROW.CANTIDAD='+CAST(NEWROW.CANTIDAD AS VARCHAR)+' OLDROW.ARTICULO='+OLDROW.ARTICULO+'/OLDROW.CANTIDAD='+CAST(OLDROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE',NEWROW.ALBARAN,NEWROW.CODIGO; 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 Can1; FETCH FROM TempCursor (EXISTU) INTO Can2; SET Can1=Can1-NEWROW.CANTIDAD; SET Can2=Can2-NEWROW.CAJAS; EXECUTE IMMEDIATE 'UPDATE EXISTENCIAS SET EXISTENCIA=?,EXISTU=? WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?' USING Can1,Can2,NEWROW.ARTICULO,NEWROW.ALMACEN; PREPARE stmt FROM 'INSERT INTO "MovimientosTablas" (OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?)'; OPEN TempCursor USING 'ARTICULO='+NEWROW.ARTICULO+'/Cant='+CAST(NEWROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE+',NEWROW.ALBARAN,NEWROW.CODIGO; END IF; END IF; PREPARE stmt FROM 'SELECT * FROM Existencias WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?'; OPEN TempCursor USING OLDROW.ARTICULO,OLDROW.ALMACEN; SET ACTUALIZA=True; IF ROWCOUNT(TempCursor)=0 THEN INSERT INTO TempCursor (Articulo,Almacen,Existencia,Iniciales,ExistInv,InicialesU,ExistU) values (OLDROW.Articulo,OLDROW.Almacen,OLDROW.Cantidad,0,0,0,OLDROW.CAJAS); ELSE FETCH FROM TempCursor (FECHAINV) INTO dFecha; IF OLDROW.FECHA<dFecha THEN Set ACTUALIZA=False; ELSE FETCH FROM TempCursor (EXISTENCIA) INTO Can1; FETCH FROM TempCursor (EXISTU) INTO Can2; SET Can1=Can1+OLDROW.CANTIDAD; SET Can2=Can2+OLDROW.CAJAS; EXECUTE IMMEDIATE 'UPDATE EXISTENCIAS SET EXISTENCIA=?,EXISTU=? WHERE ARTICULO COLLATE ESP=? AND ALMACEN COLLATE ESP=?' USING Can1,Can2,OLDROW.ARTICULO,OLDROW.ALMACEN; PREPARE stmt FROM 'INSERT INTO "MovimientosTablas" (OBSERVA,FECHA,OPERACION,DOCUMENTO,CODIGO) VALUES (?,?,?,?,?)'; OPEN TempCursor USING 'ARTICULO='+OLDROW.ARTICULO+'/Cant='+CAST(OLDROW.CANTIDAD AS VARCHAR),SUBSTRING(CAST(CURRENT_TIMESTAMP AS VARCHAR) FROM 1 FOR 19),'AFTER UPDATE-',OLDROW.ALBARAN,OLDROW.CODIGO; END IF; END IF; COMMIT; SET SALTE=TRUE; EXCEPTION ROLLBACK; IF ERRORCODE()<>1005 THEN RAISE; END IF; END; END WHILE; When it fails the field 'OBSERVA' of the first insert on table "MovimientosTablas" takes NULL value and the third insert is not at the table. Thank's |
Wed, Jun 9 2010 1:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Francisco,
<< I'm changed my triggers and now insert a record to a new table called "MovimientosTablas" with some NEWROW and OLDROW values when insert or update records. When I update a record I insert one in the new table to see that executes the AFTER UPDATE trigger, one more when I add new values to accumulated table and one more when I rest the OLDROW values. Trying it, I insert a record on the table and later I update it. Then I can see that when I insert the first record one of the fields have null values, the secord record have values OK and the third record is not in the table, then, by not executing the third step values in the accumulated table is wrong. I insert one record and update it again and works fine. ¿? >> Can you send me a simple example of what you're doing (database catalog and table files also, please), along with what you expect to occur vs. what is actually occurring ? I can then see what is happening in real time and give you a complete explanation. Thanks, -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |