Re: AW: AW: AW: AW: Cascade Triggering

Thu, 02 Sep 2004 02:36:45 -0700 

Anhaus, Thomas a �crit :

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



Hi,

Just a short mail to tell you... it works ! You were true. Now I use TRY CATCH to make sure existing cursors are closed and to ignore errors when they don't. Thank you very much for your help.

VERY best regards, Xavier.

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to