Re: Problem creating trigger

[Edson Carlos Ericksson Richter]

I've found an old mail saying that OLD and NEW works only in UPDTE 
triggers. So, should I not use OLD in DELETE trigger too?

Thanks,
Richter


Edson Carlos Ericksson Richter escreveu:

Thank you, you is right. I forgot the brackets...
But now, if I stay with "if old.CO_SITUACAO <> 2", I get "Table name 
must be in from list". If I remove the IF line, compiles ok.

Any hints?

Richter


Frank Schimmelpfennig escreveu:

Hi,

perhaps it helps to put the trigger logic between brackets... Pls. 
see the
code modifications below:

create trigger TRG_PARCELA_DEL for TB_PARCELA
 after delete
   execute (
     if old.CO_SITUACAO <> 2 then
       UPDATE MGR.TB_CC_ORCAMENTO
         SET VL_PROVISIONADO = VL_PROVISIONADO - :old.VL_NOMINAL_REAIS
         ,VL_PAGO = VL_PAGO - :old.VL_PAGO
         WHERE EXISTS
           (SELECT *
             FROM MGR.TB_TITULO T3
             WHERE T3.NU_TITULO = :old.NU_TITULO
             AND MGR.TB_CC_ORCAMENTO.CO_CONTABIL = T3.CO_CONTABIL_CR
             AND date(T3.DT_EMISSAO) BETWEEN TB_CC_ORCAMENTO.DT_INICIO
             AND TB_CC_ORCAMENTO.DT_FIM);
   )
//


kind regards
Frank


                                                                          
                                                                          
                                                                          
                                                                       
To                                       
maxdb@lists.mysql.com               
                                                                       
cc     Edson 
Carlos                                                              
Ericksson Richter                                             Subject 
                                      Problem creating 
trigger                09.06.2005 
09:31                                       Classification 
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          
                                                                          




I'm creating a very simple trigger:

create trigger TRG_PARCELA_DEL for TB_PARCELA after delete
execute

if old.CO_SITUACAO <> 2 then
 UPDATE MGR.TB_CC_ORCAMENTO
    SET VL_PROVISIONADO = VL_PROVISIONADO - :old.VL_NOMINAL_REAIS,
           VL_PAGO = VL_PAGO - :old.VL_PAGO
WHERE EXISTS (SELECT *
                FROM MGR.TB_TITULO T3
               WHERE T3.NU_TITULO = :old.NU_TITULO
                 AND MGR.TB_CC_ORCAMENTO.CO_CONTABIL = T3.CO_CONTABIL_CR
                 AND date(T3.DT_EMISSAO) BETWEEN 
TB_CC_ORCAMENTO.DT_INICIO
             AND TB_CC_ORCAMENTO.DT_FIM);

No matter what command I put in trigger code, I get

(-5015) at 72: Missing keyword IN

Someone knows what's wrong here?

TIA,

--
Edson Carlos Ericksson Richter
MGR Inform�tica Ltda.
Fones: 3347-0446 / 9259-2993


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


 





--
Edson Carlos Ericksson Richter
MGR Inform�tica Ltda.
Fones: 3347-0446 / 9259-2993


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