Re: AW: General error;-6013 POS(1) Duplicate trigger definition

[Xavier CAMIER]

Anhaus, Thomas a �crit :
Xavier Camier wrote
 

Linux
MaxDB 7.5.0.12
Good afternoon,
I use triggers to automate some datas processes and everything worked 
fine... until today. The following trigger seems alright (it has been 
accepted by SQL Studio) but when I try to browse it from sql studio it 
does not appear in the trigger list. I'm sure it has been 
stored  by the 
engine because when I try to create it again the "General error;-6013 
POS(1) Duplicate trigger definition" message is returned. It 
is strange 
that I can't drop it with the "drop trigger TRIG_MAINOEUVRESMAJPV of 
COMPOSANTS" command. The "General error;-4019 POS(14) Unknown trigger 
name:TRIG_MAINOEUVRESMAJPV" message is then returned. When I 
browse the 
DOMAIN.TRIGGER table, the trigger does not appear in the trigger list.

The question are... What can I do ? How to destroy the 
"hidden" trigger 
? What's wrong with my trigger ?

Thanks for your help.
Trigger Code:
CREATE TRIGGER TRIG_MAINOEUVRESMAJPV FOR MAINOEUVRES AFTER 
UPDATE EXECUTE (
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.IDELEMENT=:NEW.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.IDELEMENT=:NEW.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.IDELEMENT=:NEW.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.IDELEMENT=:NEW.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 =
                      CASE WHEN  (SELECT  CURSCUMULFINAL.TOTALPV
                                                  FROM 
DBA.CURSCUMULFINAL
                                                  WHERE 
OUVRAGES.IDELT= CURSCUMULFINAL.IDOUVRAGE) IS NULL THEN 
OUVRAGES.PRIXVENTE
                      ELSE  (SELECT  CURSCUMULFINAL.TOTALPV
                                                  FROM 
DBA.CURSCUMULFINAL
                                                  WHERE 
OUVRAGES.IDELT= 
CURSCUMULFINAL.IDOUVRAGE)                                      
               

                      END;            
         
CLOSE CURSCUMULPARTIEL;
CLOSE CURSCUMULFINAL;

CATCH                                                          
                                                                    

IF $RC <> 100 THEN STOP ($RC, 'Erreur lors de l''ex�cution 
du trigger 
permettant de mettre � jour le prix de vente des ouvrages contenant 
l''�lement modifi�.');
)

   

The error -6013 means that you have already defined an update trigger
for table MAINOEUVRES. Please note that MaxDB supports only one update trigger
per table. If you want to have more than one update trigger for a table you
have to merge their code into one update trigger. 

Best Regards,
Thomas
 

Ok I found how to help maxdb to perform the tasks I want :
I created a stored proc with the code above and call it from the trigger 
with the call statement. It's about to work :-)

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