>
Xavier Camier wrote :
>Anhaus, Thomas a �crit :
>
>>Hi Xavier,
>>error -4000 gave me a hint, but no explanation. Error -4000
>means 'Unknown result table'
>>and I think that one of the statements
>>
>>CLOSE CURSCUMULPARTIEL;
>>or
>>CLOSE CURSCUMULFINAL;
>>
>>produces the error. But I have no idea, why these cursors
>should not exist
>>any more.
>>I fear only a vtrace give us more information (For vtrace
>please have a look to
>>http://sapdb.2scale.net/moin.cgi/VTrace).
>>
>>Alternatively could try to ignore error -4000 inside your
>db-procedure.
>>
>>Best Regards,
>>Thomas
>>
>>
>>
>>
>>>-----Urspr�ngliche Nachricht-----
>>>Von: Xavier CAMIER [mailto:[EMAIL PROTECTED]
>>>Gesendet: Mittwoch, 1. September 2004 09:08
>>>An: Anhaus, Thomas; [EMAIL PROTECTED]
>>>Betreff: Re: AW: AW: Cascade Triggering
>>>
>>>
>>>Anhaus, Thomas a �crit :
>>>
>>>
>>>
>>>>Hi Xavier,
>>>>I'm interested in the sql code of the failing update. Since your
>>>>trigger terminates with a STOP statement in case of an error,
>>>>the update statement should return with that trigger error code.
>>>>
>>>>Kind Regards,
>>>>Thomas
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>-----Urspr�ngliche Nachricht-----
>>>>>Von: Xavier CAMIER [mailto:[EMAIL PROTECTED]
>>>>>Gesendet: Dienstag, 31. August 2004 17:37
>>>>>An: Anhaus, Thomas; [EMAIL PROTECTED]
>>>>>Betreff: Re: AW: Cascade Triggering
>>>>>
>>>>>
>>>>>Anhaus, Thomas a �crit :
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>Xavier CAMIER wrote :
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Good morning,
>>>>>>>
>>>>>>>Here starts the second part of my trigger tale. I have to
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>manage datas
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>>and I want it to be done by MaxDB. Here is a scheme of what I
>>>>>>>want to do
>>>>>>>
>>>>>>>---->
>>>>>>>
>>>>>>> --------------- --------------
>>>>>>> | 'A' Storage | -- | 'C' Storage | ...
>>>>>>> / -------------- --------------
>>>>>>>-------------- /
>>>>>>>| Component | ---------------
>>>>>>>-------------- \ | 'D'
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>Storage | ...
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>> \ -------------- / --------------
>>>>>>> | 'B' Storage | /
>>>>>>> -------------- \ --------------
>>>>>>> \ |
>>>>>>>
>>>>>>>
>>>'E' Storage
>>>
>>>
>>>>>>>| ...
>>>>>>>
>>>>>>>
>>>>>>>
>>>--------------
>>>
>>>
>>>>>>>* There is a trigger on the update event of the Component table
>>>>>>>* There is a trigger on the update event of the Storage table
>>>>>>>* A component is updated ==> the Component_Trigger updates
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>'A' Storage
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>>and 'B' Storage. As there's a trigger on the Storage table,
>>>>>>>'A' Storage
>>>>>>>updates 'C' Storage and 'B' Storage updates 'D' and 'E' Storage.
>>>>>>>
>>>>>>>There is no infinite loop but MaxDB returns an generic
>error when
>>>>>>>updating Component (it works when I disable the
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>Storage_Trigger). The
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>>MaxDB doc says that one of the reasons of an update statement
>>>>>>>failure is
>>>>>>>the failure of the trigger. That's seems beeing what's happening.
>>>>>>>
>>>>>>>The question are : is cascade triggering forbidden ? Are
>triggers
>>>>>>>threaded or queued ? if cascade triggering is not forbidden,
>>>>>>>where could
>>>>>>>be my mistake ?
>>>>>>>
>>>>>>>Thanks for having read that long post.
>>>>>>>Best regards, Xavier.
>>>>>>>
>>>>>>>--
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>Cascade triggering is not forbidden. Internally these
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>cascading requests are
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>queued.
>>>>>>It therefore would be very interesting to know the sql error
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>code which has been
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>returned by the trigger.
>>>>>>
>>>>>>Best Regards,
>>>>>>Thomas
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>I'm sorry but I can't find that error code. The returned code is
>>>>>generated by the update statement but because of the failure
>>>>>of the trigger.
>>>>>
>>>>>-> As my trigger's behaviour was strange I put the code into
>>>>>
>>>>>
>>>a stored
>>>
>>>
>>>>>proc and call that stored proc from the trigger
>>>>>
>>>>>What does the code do ? :
>>>>>----------------------------
>>>>>
>>>>>- It looks for all the records corresponding to the :NEW.ID
>>>>>- It processes the datas
>>>>>- It update those records with the result of the last treatment.
>>>>>
>>>>>==> Each update launch the same trigger... and so on...
>>>>>
>>>>>
>>>>>I'm sure there's no infinite loop as :
>>>>>
>>>>>When a A component (mainoeuvre) is modified ==> B and C works
>>>>>(ouvrages)
>>>>>which contains A are modified ==>the D work which contains the
>>>>>C work is
>>>>>modified
>>>>>
>>>>>Component -> a table
>>>>>Work -> a table linked to a WorkRessources table. A
>>>>>WorkRessource can be
>>>>>a Component or an another Work (there can't be incest
>between Works)
>>>>>
>>>>>
>>>>>Code of the stored proc :
>>>>>--------------------------
>>>>>
>>>>>CREATE DBPROC MAINOEUVRESMAJPV (IN IDELT INTEGER) AS
>>>>>TRY
>>>>>
>>>>>DECLARE CURSCUMULPARTIEL CURSOR FOR
>>>>> SELECT SUM(CASE COMPOSANTS.PRIXFIXE
>>>>> WHEN NULL THEN COMPOSANTS.PRIXVENTE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE * COMPOSANTS.COEFTECH
>>>>> ELSE COMPOSANTS.PRIXFIXE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE * COMPOSANTS.COEFTECH
>>>>> END) AS TOTALGENE, OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>> FROM DBA.OUVRAGESCOMPOSITIONS, DBA.COMPOSANTS
>>>>> WHERE OUVRAGESCOMPOSITIONS.IDOUVRAGE IN
>>>>> (SELECT IDOUVRAGE FROM
>>>>>DBA.OUVRAGESCOMPOSITIONS WHERE IDELEMENT=:IDELT) AND
>>>>> OUVRAGESCOMPOSITIONS.IDELEMENT=COMPOSANTS.IDELT
>>>>> GROUP BY OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>>
>>>>> UNION
>>>>>
>>>>> SELECT SUM(CASE MAINOEUVRES.PRIXFIXE
>>>>> WHEN NULL THEN MAINOEUVRES.PRIXVENTE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> ELSE MAINOEUVRES.PRIXFIXE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> END) AS TOTALGENE, OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>> FROM DBA.OUVRAGESCOMPOSITIONS, DBA.MAINOEUVRES
>>>>> WHERE OUVRAGESCOMPOSITIONS.IDOUVRAGE IN
>>>>> (SELECT IDOUVRAGE FROM
>>>>>DBA.OUVRAGESCOMPOSITIONS WHERE IDELEMENT=:IDELT) AND
>>>>> OUVRAGESCOMPOSITIONS.IDELEMENT=MAINOEUVRES.IDELT
>>>>> GROUP BY OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>>
>>>>> UNION
>>>>>
>>>>> SELECT SUM(CASE OUVRAGES.PRIXFIXE
>>>>> WHEN NULL THEN OUVRAGES.PRIXVENTE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> ELSE OUVRAGES.PRIXFIXE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> END) AS TOTALGENE, OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>> FROM DBA.OUVRAGESCOMPOSITIONS, DBA.OUVRAGES
>>>>> WHERE OUVRAGESCOMPOSITIONS.IDOUVRAGE IN
>>>>> (SELECT IDOUVRAGE FROM
>>>>>DBA.OUVRAGESCOMPOSITIONS WHERE IDELEMENT=:IDELT) AND
>>>>> OUVRAGESCOMPOSITIONS.IDELEMENT=OUVRAGES.IDELT
>>>>> GROUP BY OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>>
>>>>> UNION
>>>>>
>>>>> SELECT SUM(CASE LOCMATERIELS.PRIXFIXE
>>>>> WHEN NULL THEN LOCMATERIELS.PRIXVENTE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> ELSE LOCMATERIELS.PRIXFIXE *
>>>>>OUVRAGESCOMPOSITIONS.QUANTITE
>>>>> END) AS TOTALGENE, OUVRAGESCOMPOSITIONS.IDOUVRAGE
>>>>> FROM DBA.OUVRAGESCOMPOSITIONS, DBA.LOCMATERIELS
>>>>> WHERE OUVRAGESCOMPOSITIONS.IDOUVRAGE IN
>>>>> (SELECT IDOUVRAGE FROM
>>>>>DBA.OUVRAGESCOMPOSITIONS WHERE IDELEMENT=:IDELT) AND
>>>>> OUVRAGESCOMPOSITIONS.IDELEMENT=LOCMATERIELS.IDELT
>>>>> GROUP BY OUVRAGESCOMPOSITIONS.IDOUVRAGE;
>>>>>
>>>>>DECLARE CURSCUMULFINAL CURSOR FOR
>>>>>SELECT SUM(TOTALGENE) AS TOTALPV, IDOUVRAGE
>>>>>
>>>>>
>>>>>FROM DBA.CURSCUMULPARTIEL
>>>>
>>>>
>>>>>GROUP BY IDOUVRAGE;
>>>>>
>>>>>UPDATE DBA.OUVRAGES SET PRIXVENTE =(SELECT TOTALPV
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>FROM DBA.CURSCUMULFINAL
>>>>
>>>>
>>>>
>>>>
>>>>>
>>>>>
>>>>>WHERE DBA.OUVRAGES.IDELT=DBA.CURSCUMULFINAL.IDOUVRAGE)
>>>>>WHERE DBA.OUVRAGES.IDELT IN (SELECT IDOUVRAGE FROM
>>>>>
>>>>>
>>>DBA.CURSCUMULFINAL);
>>>
>>>
>>>>>
>>>>>
>>>>>CLOSE CURSCUMULPARTIEL;
>>>>>CLOSE CURSCUMULFINAL;
>>>>>
>>>>>CATCH
>>>>>
>
>>>>>
>>>>>IF $RC <> 100 THEN STOP ($RC, 'Erreur d''ex�cution du
>>>>>
>>>>>
>>>trigger de maj
>>>
>>>
>>>>>du pv des ouvrages');
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>Code of the triggers :
>>>>>----------------------
>>>>>
>>>>>CREATE TRIGGER TRIG_MAINOEUVRESMAJPV FOR MAINOEUVRES AFTER
>>>>>UPDATE EXECUTE (
>>>>>TRY
>>>>>CALL DBA.MAINOEUVRESMAJPV(:NEW.IDELT);
>>>>>CATCH
>>>>>IF $RC <> 100 THEN STOP ($RC, 'Erreur lors de l''ex�cution
>>>>>du trigger
>>>>>de mise � jours des prix de vente');
>>>>>)
>>>>>
>>>>>
>>>>>
>>>>>CREATE TRIGGER TRIG_OUVRAGESMAJPV FOR OUVRAGES AFTER
>UPDATE EXECUTE (
>>>>>TRY
>>>>>CALL DBA.MAINOEUVRESMAJPV(:NEW.IDELT);
>>>>>CATCH
>>>>>IF $RC <> 100 THEN STOP ($RC, 'Erreur du trigger de mise �
>>>>>jour des pv
>>>>>des ouvrages');
>>>>>)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>Good morning,
>>>
>>>The error code / message says :
>>>
>>>Le projet Everest.exe a provoqu� une classe d'exception EOleExeption
>>>avec le message '[SAP AG][SQLOD32 DLL][MaxDB]General
>>>error;-4000 POS(7)
>>>Impossible de modifier ce type de main d'oeuvre'. Processus stopp�.
>>>Utilisez Pas-�-pas ou Ex�cuter pour continuer.
>>>
>>>Which means
>>>
>>>The Everest.exe project caused an EOleExeption class exception
>>>with the
>>>message '[SAP AG][SQLOD32 DLL][MaxDB]General error;-4000 POS(7)
>>>Impossible de modifier ce type de main d'oeuvre'. Process
>>>stopped. [...]
>>>
>>>
>>>It's a delphi 7 through ODBC error code (I use odbc as I don't think
>>>there is an other way to connect to a MaxDB database from Delphi).
>>>
>>>Best regards, Xavier
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>Hi,
>
>I used VTrace as explained in the link you've provided to me. It has
>been launched just before validating the update and has been stopped
>after the crash. Well I tried to read it and even if I'm able to trace
>what's happening in it, I'm unable to understand everything. May you
>help me ? Everything seems working fine until the error... strange...
>
>Thanks for your help.
>
>P.S. I had to shorten the trace log as mails bigger than 40000 bytes
>aren't allowed. The beginning of the log and the end of the log are
>separated by a
>-------------------------
>SHORTENED BY ME
>------------------------- string.
>
Hi Xavier,
I'm now able to explain what happens.
Since your trigger cascades an inner call of the trigger has already closed
the cursors you try to close in an outer call. This is because each call uses the same
cursor names. As the final result the trigger fails with error -4000, unknown
result name. More detailed the following happens :
update MAINOEUVRES
calls trigger TRIG_MAINOEUVRESMAJPV
calls dbproc DBA.MAINOEUVRESMAJPV
creates cursor CURSCUMULPARTIEL
creates cursor CURSCUMULFINAL
update DBA.OUVRAGES
calls trigger TRIG_OUVRAGESMAJPV
calls dbproc DBA.MAINOEUVRESMAJPV
creates cursor CURSCUMULPARTIEL
creates cursor CURSCUMULFINAL
update DBA.OUVRAGES (no result)
close CURSCUMULPARTIEL
close CURSCUMULFINAL
close CURSCUMULPARTIEL (<-- error -4000)
I recommend just to ignore errors returned by the close statements.
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
