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