Re: declare variable in trigger

harun.bolat_at_netsim.net wrote:
> 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.

It looks like you have so much more to rewrite than just variable names, so why the fuss over those? i.e. "IF(NOT EXISTS(SELECT ..." is not valid, "DECLARE VARIABLE TARIH1..." is not valid, "EXECUTE PROCEDURE DEBUGTEXT... is not valid, "EXCEPTION "FLYEXCEPTION" _NONE 'Farklı..." is not valid. Even the select into needs to be reworked.

For variable names, use find and replace using ":" in the find and "v_" in the replace as someone suggested. That would be the easy part. The sytax issues are going to be alot more time consuming. Received on Fri Dec 29 2006 - 12:18:03 CST