Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: declare variable in trigger

Re: declare variable in trigger

From: <harun.bolat_at_netsim.net>
Date: 29 Dec 2006 03:34:32 -0800
Message-ID: <1167392072.707222.286050@79g2000cws.googlegroups.com> 





thanks,



I have posted trigger only to simplify an example.



as example firebird trigger like this.


variable of CARI_ISLEM_NO same as field name.




CREATE TRIGGER CARIISKA_BI FOR CARIISKA



BEFORE INSERT POSITION 100



AS


  DECLARE VARIABLE CARI_BAGLANTI_NO1 INTEGER;

  DECLARE VARIABLE CARI_BAGLANTI_NO2 INTEGER;

  DECLARE VARIABLE VADE1 DATE;



  DECLARE VARIABLE VADE2 DATE;



  DECLARE VARIABLE BAKIYE1 DOUBLE PRECISION;

  DECLARE VARIABLE BAKIYE2 DOUBLE PRECISION;

  DECLARE VARIABLE KAPANMA1 DOUBLE PRECISION;

  DECLARE VARIABLE KAPANMA2 DOUBLE PRECISION;

  DECLARE VARIABLE KAPANIR1 CHAR(1);



  DECLARE VARIABLE KAPANIR2 CHAR(1);



  DECLARE VARIABLE FIRMA_NO1 INTEGER;



  DECLARE VARIABLE FIRMA_NO2 INTEGER;



  DECLARE VARIABLE KUR_TIP_NO1 INTEGER;



  DECLARE VARIABLE KUR_TIP_NO2 INTEGER;



  DECLARE VARIABLE DOVIZ_BIRIMI1 VARCHAR(4);

  DECLARE VARIABLE DOVIZ_BIRIMI2 VARCHAR(4);

  DECLARE VARIABLE TARIH1 TIMESTAMP;



  DECLARE VARIABLE TARIH2 TIMESTAMP;



  DECLARE VARIABLE VIRMAN_CARI_ISLEM_NO INTEGER;

  DECLARE VARIABLE CARI_ISLEM_NO INTEGER; -- this variable name same as
field name


  DECLARE VARIABLE KUR1 DOUBLE PRECISION;

  DECLARE VARIABLE KUR2 DOUBLE PRECISION;

  DECLARE VARIABLE TARIH TIMESTAMP;



BEGIN



  IF(USER <> 'REPL') THEN


  BEGIN



    IF(NOT EXISTS(SELECT 1 FROM  CARIISLM WHERE CARI_ISLEM_NO =

NEW.CARI_ISLEM_NO)) THEN



      EXCEPTION "FLYEXCEPTION" _NONE 'Cari işlem kaydı veya kullanım
yetkisi yok. CariIslemNo: ' || COALESCE(NEW.CARI_ISLEM_NO, '<NULL>');



    IF(NEW.TUTAR < 0) THEN


      EXCEPTION "FLYEXCEPTION" _NONE 'Kapatma tutarı negatif olamaz.
CariIslemNo: ' || NEW.CARI_ISLEM_NO;


    SELECT I.CARI_BAGLANTI_NO, I.KAPANIR, I.VADE_TARIHI, I.BAKIYE,
I.KAPANMA, N.FIRMA_NO, I.DOVIZ_BIRIMI,
           I.TARIH, I.DOVIZ_KURU, I.KUR_TIP_NO
      FROM CARIISLM I
      LEFT JOIN ISLMNOKT N ON N.ISLEM_NOKTASI_NO = I.ISLEM_NOKTASI_NO
     WHERE I.CARI_ISLEM_NO = NEW.CARI_ISLEM_NO
      INTO :CARI_BAGLANTI_NO1, :KAPANIR1, :VADE1, :BAKIYE1, :KAPANMA1,
:FIRMA_NO1, :DOVIZ_BIRIMI1,
           :TARIH1, :KUR1, :KUR_TIP_NO1;

    SELECT I.CARI_BAGLANTI_NO, I.KAPANIR, I.VADE_TARIHI, I.BAKIYE,
I.KAPANMA, N.FIRMA_NO, I.DOVIZ_BIRIMI,
           I.TARIH, I.DOVIZ_KURU, I.KUR_TIP_NO
      FROM CARIISLM I
      LEFT JOIN ISLMNOKT N ON N.ISLEM_NOKTASI_NO = I.ISLEM_NOKTASI_NO
     WHERE I.CARI_ISLEM_NO = NEW.KAPANAN_CARI_ISLEM_NO
      INTO :CARI_BAGLANTI_NO2, :KAPANIR2, :VADE2, :BAKIYE2, :KAPANMA2,
:FIRMA_NO2, :DOVIZ_BIRIMI2,
           :TARIH2, :KUR2, :KUR_TIP_NO2;





    IF(FIRMA_NO1 <> FIRMA_NO2) THEN


    BEGIN



      IF(CURRENT_ROLE = 'DEVELOPER') THEN


      BEGIN


        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 318');



        EXECUTE PROCEDURE DEBUGTEXT('NEW.CARI_ISLEM_NO || ''-'' ||

NEW.KAPANAN_CARI_ISLEM_NO : ' || CASE WHEN NEW.CARI_ISLEM_NO || '-' ||

NEW.KAPANAN_CARI_ISLEM_NO IS NULL THEN 'NULL' ELSE NEW.CARI_ISLEM_NO ||

'-' || NEW.KAPANAN_CARI_ISLEM_NO END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      IF(CURRENT_ROLE = 'DEVELOPER') THEN

      BEGIN

        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 319');

        EXECUTE PROCEDURE DEBUGTEXT('FIRMA_NO1 || ''-'' || FIRMA_NO2 :


' || CASE WHEN FIRMA_NO1 || '-' || FIRMA_NO2 IS NULL THEN 'NULL' ELSE

FIRMA_NO1 || '-' || FIRMA_NO2 END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      EXCEPTION "FLYEXCEPTION" _NONE 'Farklı firma cari işlemleri


birbiri ile kapatılamaz';


    END


    IF(CARI_BAGLANTI_NO1 <> 0 AND CARI_BAGLANTI_NO2 <> 0 AND
CARI_BAGLANTI_NO1 <> CARI_BAGLANTI_NO2) THEN


    BEGIN



      IF(CURRENT_ROLE = 'DEVELOPER') THEN


      BEGIN


        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 325');



        EXECUTE PROCEDURE DEBUGTEXT('NEW.CARI_ISLEM_NO || ''-'' ||

NEW.KAPANAN_CARI_ISLEM_NO : ' || CASE WHEN NEW.CARI_ISLEM_NO || '-' ||

NEW.KAPANAN_CARI_ISLEM_NO IS NULL THEN 'NULL' ELSE NEW.CARI_ISLEM_NO ||

'-' || NEW.KAPANAN_CARI_ISLEM_NO END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      IF(CURRENT_ROLE = 'DEVELOPER') THEN

      BEGIN

        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 326');

        EXECUTE PROCEDURE DEBUGTEXT('CARI_BAGLANTI_NO1 || ''-'' ||
CARI_BAGLANTI_NO2 : ' || CASE WHEN CARI_BAGLANTI_NO1 || '-' ||


CARI_BAGLANTI_NO2 IS NULL THEN 'NULL' ELSE CARI_BAGLANTI_NO1 || '-' ||

CARI_BAGLANTI_NO2 END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      EXCEPTION "FLYEXCEPTION" _NONE 'Farklı Cari Bağlantılara ait


işlemler birbirini kapatamaz';


    END


    IF(BAKIYE1 * BAKIYE2 > 0) THEN



    BEGIN



      IF(CURRENT_ROLE = 'DEVELOPER') THEN


      BEGIN


        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 332');



        EXECUTE PROCEDURE DEBUGTEXT('NEW.CARI_ISLEM_NO || ''-'' ||

NEW.KAPANAN_CARI_ISLEM_NO : ' || CASE WHEN NEW.CARI_ISLEM_NO || '-' ||

NEW.KAPANAN_CARI_ISLEM_NO IS NULL THEN 'NULL' ELSE NEW.CARI_ISLEM_NO ||

'-' || NEW.KAPANAN_CARI_ISLEM_NO END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      IF(CURRENT_ROLE = 'DEVELOPER') THEN

      BEGIN

        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 333');

        EXECUTE PROCEDURE DEBUGTEXT('BAKIYE1 || ''-'' || BAKIYE2 : ' ||


CASE WHEN BAKIYE1 || '-' || BAKIYE2 IS NULL THEN 'NULL' ELSE BAKIYE1 ||

'-' || BAKIYE2 END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      EXCEPTION "FLYEXCEPTION" _NONE 'Bakiyeleri aynı yönlü


hareketler birbirini kapatamaz';


    END


    IF(KAPANIR1 <> 'E' OR KAPANIR2 <> 'E') THEN
    BEGIN



      IF(CURRENT_ROLE = 'DEVELOPER') THEN


      BEGIN


        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 339');



        EXECUTE PROCEDURE DEBUGTEXT('NEW.CARI_ISLEM_NO || ''-'' ||

NEW.KAPANAN_CARI_ISLEM_NO : ' || CASE WHEN NEW.CARI_ISLEM_NO || '-' ||

NEW.KAPANAN_CARI_ISLEM_NO IS NULL THEN 'NULL' ELSE NEW.CARI_ISLEM_NO ||

'-' || NEW.KAPANAN_CARI_ISLEM_NO END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      IF(CURRENT_ROLE = 'DEVELOPER') THEN

      BEGIN

        EXECUTE PROCEDURE DEBUGTEXT('File: trigcari.sql, Line: 340');

        EXECUTE PROCEDURE DEBUGTEXT('KAPANIR1 || ''-'' || KAPANIR2 : '


|| CASE WHEN KAPANIR1 || '-' || KAPANIR2 IS NULL THEN 'NULL' ELSE

KAPANIR1 || '-' || KAPANIR2 END);



        EXECUTE PROCEDURE DEBUGTEXT('StackTrace: ' || StackTrace());


      END
      EXCEPTION "FLYEXCEPTION" _NONE 'KAPANIR ''E'' olmayan hareketler


birbirini kapatamaz';


    END


    IF(DOVIZ_BIRIMI1 <> DOVIZ_BIRIMI2) THEN
    BEGIN



      SELECT BitOr(BitShl(DB_ID,24),GEN_ID(CARI_ISLEM_NO,1)) FROM
FLYSYSTEM INTO :CARI_ISLEM_NO;


      TARIH = CASE WHEN TARIH1 < TARIH2 THEN TARIH2 ELSE TARIH1 END;
      IF(NEW.TUTAR > Abs(BAKIYE1 - KAPANMA1)) THEN
        NEW.TUTAR = Abs(BAKIYE1 - KAPANMA1);

      IF (TARIH1 < TARIH2) THEN
        SELECT KUR FROM DOVIZ_KURUBUL(:TARIH, :DOVIZ_BIRIMI1,


:KUR_TIP_NO1) INTO :KUR1;



      IF (TARIH1 > TARIH2) THEN
        SELECT KUR FROM DOVIZ_KURUBUL(:TARIH, :DOVIZ_BIRIMI2,


:KUR_TIP_NO2) INTO :KUR2;



      IF(NEW.TUTAR > Abs(BAKIYE2 - KAPANMA2) * KUR2 / KUR1) THEN
        NEW.TUTAR = Abs(BAKIYE2 - KAPANMA2) * KUR2 / KUR1;

      INSERT INTO CARIISLM(CARI_ISLEM_NO, CARI_NO, CARI_ALT_NO,
ISLEM_KODU, ISLEM_ADI,
           TARIH, VADE_TARIHI, ACIKLAMA, ODEME_ARACI, KUR_TIP_NO,
           BORC, ALACAK,
           DOVIZ_BIRIMI, DOVIZ_KURU, KAPANIR, XMODUL, TICARI_DEGER,
           ISLEM_NOKTASI_NO, PERSONEL_NO, USER_ID, GROUP_ID,
ROGROUP_ID, SECURITY, SYSTEM)
      SELECT :CARI_ISLEM_NO, CARI_NO, CARI_ALT_NO, 'VİRMAN', 'Virman',
             :TARIH, VADE_TARIHI, 'Döviz Virman', 'Nakit', KUR_TIP_NO,
             CASE WHEN ALACAK > 0 THEN NEW.TUTAR ELSE 0 END,
             CASE WHEN BORC > 0 THEN NEW.TUTAR ELSE 0 END,
             DOVIZ_BIRIMI, :KUR1, 'E', 'C', 'H',
             ISLEM_NOKTASI_NO, PERSONEL_NO, USER_ID, GROUP_ID,
ROGROUP_ID, SECURITY, 'E'
        FROM CARIISLM
       WHERE CARI_ISLEM_NO = NEW.CARI_ISLEM_NO;

      SELECT INTERNAL
        FROM CARIISLM
       WHERE CARI_ISLEM_NO = :CARI_ISLEM_NO  /**** this is variable
*****/
        INTO :VIRMAN_CARI_ISLEM_NO;

      UPDATE CARIISLM
         SET DOVIZ_BIRIMI = :DOVIZ_BIRIMI2,
             KUR_TIP_NO = :KUR_TIP_NO2,
             DOVIZ_KURU = :KUR2,
             BORC = CASE WHEN BORC > 0 THEN NEW.TUTAR * :KUR1 / :KUR2
ELSE 0 END,
             ALACAK = CASE WHEN ALACAK > 0 THEN NEW.TUTAR * :KUR1 /
:KUR2  ELSE 0 END
       WHERE CARI_ISLEM_NO = :VIRMAN_CARI_ISLEM_NO;

      INSERT INTO "CARIISKA"(CARI_ISLEM_NO, KAPANAN_CARI_ISLEM_NO,
TUTAR, DOVIZ_VIRMAN)
                           VALUES(NEW.KAPANAN_CARI_ISLEM_NO,


:VIRMAN_CARI_ISLEM_NO, NEW.TUTAR * :KUR1 / :KUR2 , 'E');



      NEW.KAPANAN_CARI_ISLEM_NO = CARI_ISLEM_NO;
      NEW.DOVIZ_VIRMAN = 'E';




    END



    ELSE



    IF(NOT EXISTS(SELECT 1 FROM CARIISKA


                   WHERE KAPANAN_CARI_ISLEM_NO = NEW.CARI_ISLEM_NO AND
                         CARI_ISLEM_NO = NEW.KAPANAN_CARI_ISLEM_NO))


THEN



    BEGIN


      IF(NEW.TUTAR > Abs(BAKIYE1 - KAPANMA1)) THEN
        NEW.TUTAR = Abs(BAKIYE1 - KAPANMA1);

      IF(NEW.TUTAR > Abs(BAKIYE2 - KAPANMA2)) THEN
        NEW.TUTAR = Abs(BAKIYE2 - KAPANMA2);


    END


    NEW.YAS = VADE1 - VADE2;



    IF(NEW.CARI_KAPATMA_NO IS NULL) THEN

      SELECT BitOr(BitShl(DB_ID,24),GEN_ID(CARI_KAPATMA_NO,1)) FROM
FLYSYSTEM INTO NEW.CARI_KAPATMA_NO;



  END



END ^




Vince yazdı:


> harun.bolat_at_netsim.net wrote:

> > thanks,

> >

> > we already tested block label, but not worked.

> >

> > I think there is no solution. we have to change variable name.

> >

> > thanks a lot.

> >

> >

> > hasta_l3_at_hotmail.com yazdý:

> > > harun.bolat_at_netsim.net a écrit :

> > > > we use firebird database and try to migrate project to oracle. All our

> > > > database object( trigger, stored procedure) we use variable name same

> > > > as database field name and we have no problem. using variable same as

> > > > field name, makes code more readable.

> > > > to migrate oracle from firebird we try to write convert program that

> > > > takes firebird sql and produce oracle sql code.

> > > >

> > > > In oracle stored procedure we can use variable name same as field name

> > > > like procedure_name.variable_name syntax. This syntax why not exists it

> > > > trigger?

> > >

> > > Perhaps block labels will help you - something

> > > like this little crazie :

> > >

> > > CREATE OR REPLACE TRIGGER KULLANIC_BI BEFORE INSERT ON KULLANIC


> > > FOR EACH ROW


> > > BEGIN


> > >   <<KULLANIC_BI>>

> > >   DECLARE


> > >     NSUID INTEGER;


> > >   BEGIN


> > >     NSUID := 5;


> > >     SELECT MAX(K.NSUID) AS F_1


> > >          INTO NSUID


> > >       FROM KULLANIC K


> > >     WHERE K.NSUID = KULLANIC_BI.NSUID;


> > >   END;


> > > END;


> > >

> > > Best regards

> > >

> > > --- Raoul


>




> It may be helpful if you post the entire trigger. Simply selecting a

> max value and doing nothing with it doesn't seem likely. Are you trying

> to find the next available NSUID value? You may want to look into using

> sequences and avoid performing a lookup for every row inserted.

Received on Fri Dec 29 2006 - 05:34:32 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US