Very Computer

Hello,

  I have written a log process to catch errors messages, but if error
is "attempt to insert a null value". In this case, the procedure exit
without catch the error.

My procedure in good configuration : the field "dummy" is primary key.
In this configuration, i suppose that already exist a record with
identifiant value egal "TRC001" to do an error "duplication key".

  Create Proc Test_log
As
  Begin
/**********************************************************
*Procedure  Name: Test_log
*Database: ?
*Server:
*********************************************************/

begin transaction

    insert into dummy(identifiant,description) VALUES("TRC001","zeze")


 Begin

        Goto GTRAN
 End  

 GTRAN:

        Begin
        Rollback transaction


        Return
        End

    Commit transaction      

 END

Create Proc test_log2 (


                   )
As
  Begin                  

 begin tran
    /*rcupration du descriptif de l'erreur*/


      from master..sysmessages

  insert into TRCLOG(identifiant,description)

  commit tran
 END  
*********************************************************

Now configuration doesn't work:
Attempt to insert a null value to dummy table

...
insert into dummy(identifiant,description) VALUES(NULL,"zeze")


...
In this case, the procedure stop and exit without catch error.

Please Help me!!

Fabrice

Hello, FG!
You wrote  on 18 Apr 2002 02:24:31 -0700:

 F> Hello,

 F>   I have written a log process to catch errors messages, but if error
 F> is "attempt to insert a null value". In this case, the procedure exit
 F> without catch the error.

 F> My procedure in good configuration : the field "dummy" is primary
 F> key.
 F> In this configuration, i suppose that already exist a record with
 F> identifiant value egal "TRC001" to do an error "duplication key".

 F> VALUES("TRC001","zeze")

Did you try ?
--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port

ICQ UID: 29427861(MasterZIV)

I had already done this :
    .....


    .....
But for me, is not the probleme because the execution don't go until the test.

Have you got anothers ideas?

thanks
Fabrice

Hello, Ilya!
You wrote to FG on Thu, 18 Apr 2002 13:51:06 +0300:

Oh, I see what you want. Put you INSERT statement in a separate procedure
and return 0 at the end. Then when you need to insert a record
call this procedure

and then check return code of it.
For good insert it should be 0, then there is an error it will be nonzero.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port

ICQ UID: 29427861(MasterZIV)

Hello,

Really thank for your help,

But I have multi tables and lot of procedures. So if I have to
implement a procedure for all insert, delete or update .... I dead.

I know, I'm not cool!!

So this a procedure example:

Declare @Error int
Declare @Error_Table varchar(40)    
declare @date datetime
select @date = getdate()
declare @test int
declare @ref int
declare @ref2 int
declare @vers int

begin transaction

select @test = count(*) from TRC001 where (CUPC_CODSRE = @P1 and
CUPC_REF = @P2 and CUPC_NUMVRS = @P3)

/****************Modification du produit***************/

if @test <> 0
    begin

    Select @vers = EVOL_NUMVRSENR  from TRC001 where (CUPC_CODSRE =
@P1 and CUPC_REF = @P2 and CUPC_NUMVRS = @P3)
    Select @vers = @vers+1
    select @Error=0
    Update TRC001
        set CUPC_CODSRE = @P1,CUPC_REF = @P2,CUPC_NUMVRS =
@P3,CUPC_NUMCLIUTR = @P4,CUPC_LIBCLIUTR = @P4Bis,CUPC_CODSEC = @P5,
                CUPC_CODMRC = @P6,CUPC_CODSEG = @P7,CUPC_CODTYPPCE =
@P8,CUPC_CODAPTDFM = @P9,CUPC_CODAPTREVULT = @P10,
                        CUPC_INDPCESRT = @P11,CUPC_CODEVN = @P12,CUPC_QTEANN =
@P13,CUPC_FRQCSM = @P14,CUPC_REFCLI = @P15,
                        CUPC_REFCDC = @P16,CUPC_CODGRPPRD = @P17,CUPC_CODCATGRPPRD =
@P18,CUPC_CODCATSGPPRD = @P19,
                        CUPC_CODFORPRD = @P20,CUPC_CODSCNPRD = @P21,CUPC_CODFCEREL =
@P22,CUPC_ORIFCEREL = @P23,CUPC_CODTYPREL = @P24,
                        CUPC_ORITYPREL = @P25,CUPC_NOMMRQ = @P26,CUPC_NOMQUAMRQ =
@P27,CUPC_NOMNRM = @P28,CUPC_CODPAYNRM = @P29,
                        CUPC_NUMVRSNRM = @P30,CUPC_NOMQUA = @P31,CUPC_NUMQUA =
@P32,CUPC_TPR = @P33,CUPC_CODTYPACI = @P34,
                        CUPC_POSEPRLARANI = @P35,CUPC_ORISEPRLARANI =
@P36,CUPC_POSEPRLONANI = @P37,CUPC_ORIEPRLONANI = @P38,
                        CUPC_CODMTHMESANI = @P39,CUPC_ORIMTHMESANI = @P40,CUPC_CODSNSTCTANI
= @P45,CUPC_ORISNSTCTANI = @P46,
                        CUPC_CODCDNACIANI = @P47,CUPC_ORICDNACIANI = @P48,CUPC_COFMOYMINECG
= @P49,CUPC_ORIMOYMINECG = @P50,
                        CUPC_COFANIMOYMIN = @P51,CUPC_ORIANIMOYMIN = @P52,CUPC_COFANIPLNMAX
= @P53,CUPC_ORIANIPLNMAX = @P54,
                        CUPC_POSEPRSNSLAR = @P55,CUPC_ORIEPRSNSLAR = @P56,CUPC_POSEPRSNSLON
= @P57,CUPC_ORIEPRSNSLON = @P58,
                        CUPC_POSEPRSNSEPS = @P59,CUPC_ORIEPRSNSEPS = @P60,CUPC_CODTYPESSDUT
= @P61,CUPC_ORITYPESSDUT = @P62,
                        CUPC_ETAACITSTDUT = @P63,CUPC_ORIACITSTDUT = @P64,CUPC_VALMINDUT =
@P65,CUPC_ORIVALMINDUT = @P66,
                        CUPC_VALMAXDUT = @P67,CUPC_ORIVALMAXDUT = @P68,CUPC_POSEPRLARPLI =
@P69,CUPC_ORIEPRLARPLI = @P70,
                        CUPC_POSEPRLONPLI = @P71,CUPC_ORIEPRLONPLI = @P72,CUPC_ETAACIPLI =
@P73,CUPC_ORIETAACIPLI = @P74,
                        CUPC_CODSNSPLI = @P75,CUPC_ORISNSPLI = @P76,CUPC_RAYPLIMIN =
@P77,CUPC_ORIRAYPLIMIN = @P78,
                        CUPC_CODTYPTST_BH = @P79,CUPC_ORITYPTST_BH = @P80,CUPC_CODSNSTCT_BH
= @P81,CUPC_ORISNSTCT_BH = @P82,
                        CUPC_VALMIN_BH0 = @P83,CUPC_ORIVALMIN_BH0 = @P84,CUPC_VALMIN_BH2 =
@P85,CUPC_ORIVALMIN_BH2 = @P86,
                        CUPC_MIN_WH = @P87,CUPC_ORIMIN_WH = @P88,CUPC_CODFRQELQ =
@P89,CUPC_ORIFRQELQ = @P90,CUPC_VISEPSMINTLR = @P91,
                        CUPC_ORIEPSMINTLR = @P92,CUPC_CODOVR = @P93,CUPC_ORIOVR =
@P94,CUPC_CODNRMRCA = @P95,CUPC_ORINRMRCA = @P96,
                        CUPC_CODSVTRCA = @P97,CUPC_ORISVTRCA = @P98,CUPC_UNTFLEMAXRCA =
@P99,CUPC_FLEMAXRECCHT = @P100,
                        CUPC_ORIMAXRECCHT = @P101,CUPC_UNTLONREFRCA =
@P102,CUPC_LONREFRECCHT = @P103,CUPC_ORIREFRECCHT = @P104,
                        CUPC_CODNRMBMB = @P105,CUPC_ORINRMBMB = @P106,CUPC_CODSVTBMB =
@P107,CUPC_ORISVTBMB = @P108,
                        CUPC_UNTPOSRPPRIV = @P109,CUPC_CODPOSRPPRIV =
@P110,CUPC_ORIPOSRPPRIV = @P111,CUPC_UNTTLRBMB = @P112,
                        CUPC_TLRMAXBMB = @P113,CUPC_ORITLRMAXBMB = @P114,CUPC_TLRMINBMB =
@P115,CUPC_ORITLRMINBMB = @P116,
                        CUPC_CODNRMPLA = @P117,CUPC_ORINRMPLA = @P118,CUPC_CODSVTTLRPLA =
@P119,CUPC_ORISVTTLRPLA = @P120,
                        CUPC_UNTTLRPLA = @P121,CUPC_VALTLRPLA = @P122,CUPC_ORIVALTLRPLA =
@P123,CUPC_LONREFTLRPLA = @P124,
                        CUPC_ORIREFTLRPLA = @P125,CUPC_RPPFLECRDPLA =
@P126,CUPC_ORIFLECRDPLA = @P127,CUPC_FLEMAXTLRPLA = @P128,
                        CUPC_ORIMAXTLRPLA = @P129,CUPC_CODPCSRCU = @P130,CUPC_ORIPCSRCU =
@P131,CUPC_CODPPRSRF = @P132,
                        CUPC_ORIPPRSRF = @P133,CUPC_CODAPTROUDRL = @P134,CUPC_ORIAPTROUDRL
= @P135,CUPC_NBRBANLAR = @P136,
                        CUPC_ORINBRBANLAR = @P137,CUPC_CODSNSERL = @P138,CUPC_ORISNSERL =
@P139,CUPC_DISMAXRIV = @P140,
                        CUPC_ORIDISMAXRIV = @P141,CUPC_UNTDISRIV = @P142,CUPC_UNTDCGSPIFUL
= @P143,CUPC_DCGSPIFUL = @P144,
                        CUPC_ORIDCGSPIFUL = @P145,CUPC_DCGMAXSPIFUL =
@P146,CUPC_ORIMAXSPIFUL = @P147,CUPC_FSHMAX = @P148,
                        CUPC_ORIFSHMAX = @P149,CUPC_CODORTAXE = @P150,CUPC_ORIORTAXE =
@P151,CUPC_CODSKN = @P152,CUPC_ORISKN = @P153,
                        CUPC_CODHLGREQ = @P154,CUPC_ORIHLGREQ = @P155,CUPC_CODTYPHLG =
@P156,CUPC_ORITYPHLG = @P157,CUPC_REFHLG = @P158,
                        CUPC_ORIREFHLG = @P159,CUPC_CODQTEHLG = @P160,CUPC_ORIQTEHLG =
@P161,CUPC_CODFIN = @P162,CUPC_ORIFIN = @P163,
                        CUPC_INDRFS = @P164,CUPC_ORIINDRFS = @P165,CUPC_INDVRNIPR =
@P166,CUPC_ORIINDVRNIPR = @P167,
                        CUPC_NUMPLNDEC = @P168,CUPC_ORIPLNDEC = @P169,CUPC_INDFCECISIDN =
@P170,CUPC_ORIINDFCEIDN = @P171,
                        CUPC_CODUNTDIACIL = @P172,CUPC_DIAINT = @P173,CUPC_ORIDIAINT =
@P174,CUPC_DIAEXTMAX = @P175,
                        CUPC_ORIDIAEXTMAX = @P176,CUPC_DIAEXTMIN = @P177,CUPC_ORIDIAEXTMIN
= @P178,CUPC_CODTYPPAQ = @P179,
                        CUPC_ORITYPPAQ = @P180,CUPC_UNTMESHAU = @P181,CUPC_HAUMAX =
@P182,CUPC_ORIHAUMAX = @P183,CUPC_HAUMIN = @P184,
                        CUPC_ORIHAUMIN = @P185,CUPC_QTE = @P186,CUPC_ORIQTE =
@P187,CUPC_NBRMAXSDR = @P188,CUPC_ORINBRMAXSDR = @P189,
                        CUPC_CODFCEMRQ = @P190,CUPC_ORIFCEMRQ = @P191,CUPC_CODMDEMRQ =
@P192,CUPC_ORIMDEMRQ = @P193,
                        CUPC_CODCNTMRQ = @P194,CUPC_ORICNTMRQ = @P195,CUPC_TXTLBRCNTMRQ =
@P196,CUPC_ORITXTCNTMRQ = @P197,
                        CUPC_CODPOSMRQ = @P198,CUPC_ORIPOSMRQ = @P199,CUPC_CODPOSFCEPFT =
@P200,CUPC_ORIPOSFCEPFT = @P201,
                        CUPC_CODETARIV = @P202,CUPC_ORIETARIV = @P203,CUPC_CODTYPERL =
@P204,CUPC_ORITYPERL = @P205,CUPC_NOMNRMRVM = @P206,
                        CUPC_ORINOMNRMRVM = @P207,CUPC_CODRVM = @P208,CUPC_ORIRVM =
@P209,CUPC_CODFLG = @P210,CUPC_ORIFLG = @P211,
                        CUPC_TYPRVMDBFSYM = @P212,CUPC_ORIRVMDBFSYM =
@P213,CUPC_UNTRVMDBFSYM = @P214,CUPC_QTERVMDBFSYM = @P215,
                        CUPC_ORIQRMDBFSYM = @P233,CUPC_UNTCHIDBFSYM =
@P216,CUPC_QRMMINFC1_1 = @P217,CUPC_ORIMINFC1_1 = @P218,
                        CUPC_QRMMAXFC1_1 = @P219,CUPC_ORIMAXFC1_1 = @P220,CUPC_QRMMOYMINFC1
= @P221,CUPC_ORIMOYMINFC1 = @P222,
                        CUPC_QRMMOYMAXFC1 = @P223,CUPC_ORIMOYMAXFC1 =
@P224,CUPC_QRMMINDBF_1 = @P225,CUPC_ORIMINDBF_1 = @P226,
                        CUPC_QRMMAXDBF_1 = @P227,CUPC_ORIMAXDBF_1 = @P228,CUPC_QRMMOYMINDBF
= @P229,CUPC_ORIMOYMINDBF = @P230,
                        CUPC_QRMMOYMAXDBF = @P231,CUPC_ORIMOYMAXDBF = @P232,CUPC_CODTYPPRD
= @P235,        CUPC_CODFLR = @P236,
                        CUPC_DATCRE = @P237,CUPC_HORCRE = @P238,CUPC_DATMAJDON =
@P239,CUPC_HORMAJDON = @P240,CUPC_DATMAJETA = @P241,
                        CUPC_HORMAJETA = @P242,CUPC_CODETA = @P243,EVOL_DHEMAJENR =
@date,EVOL_IDTMAJENR = @P1,
                        EVOL_NUMVRSENR = @vers,CUPF_CODSRE = @P244,CUPF_REF =
@P245,CUPF_NUMVRS = @P246

        where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and CUPC_NUMVRS =
@P3)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Update TRC001",1,40)
        exec dbo.test_log @Error, @Error_Table
        End          

        delete TRC002 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
        Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC002",1,40)
        exec dbo.test_log @Error, @Error_Table
        End

        delete TRC006 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
        Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC006",1,40)
        exec dbo.test_log @Error, @Error_Table
        End
        delete TRC007 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC007",1,40)
        exec dbo.test_log @Error, @Error_Table
        End
        delete TRC008 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC008",1,40)
        exec dbo.test_log @Error, @Error_Table
        End
        delete TRC009 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC009",1,40)
        exec dbo.test_log @Error, @Error_Table
        End
        delete TRC010 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete TRC010",1,40)
        exec dbo.test_log @Error, @Error_Table
        End
        delete TRC014 where (CUPC_CODSRE = @P1 and CUPC_REF = @P2 and
CUPC_NUMVRS = @P3)

        select @ref = min(CUCH_IDT) from TRC003 where (CUPC_CODSRE = @P1 and
CUPC_REF = @P2 and CUPC_NUMVRS = @P3)
        select @ref2 = max(CUCH_IDT) from TRC003 where (CUPC_CODSRE = @P1 and
CUPC_REF = @P2 and CUPC_NUMVRS = @P3)

        delete TRC004 where (CUCH_IDT = @ref)
        Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete
TRC004_1",1,40)
        exec dbo.test_log @Error, @Error_Table
        End  
        delete TRC004 where (CUCH_IDT = @ref2)
        Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete
TRC004_2",1,40)
        exec dbo.test_log @Error, @Error_Table
        End  
    delete TRC005 where (CUCH_IDT = @ref)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete
TRC005_1",1,40)
        exec dbo.test_log @Error, @Error_Table
        End  
    delete TRC005 where (CUCH_IDT = @ref2)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete
TRC005_2",1,40)
        exec dbo.test_log @Error, @Error_Table
        End  
    delete TRC003 where (CUCH_IDT = @ref)
    Select @Error = @@error
    If (@Error !=0)
        Begin
                select @Error_Table = substring("Alim_trc001 : Delete
TRC003_1",1,40)
        exec
...

read more »

Hello, FG!
You wrote  on 19 Apr 2002 00:41:57 -0700:

 F> But I have multi tables and lot of procedures. So if I have to
 F> implement a procedure for all insert, delete or update .... I dead.

I suspect you have nothing else to do with it.
Or else you can avoid inserting null values or allow nulls for the column.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port

ICQ UID: 29427861(MasterZIV)

Hello Ilya,

 So, i think too, i hasn't choice.
I will rewrite all procedures :(.

Thank you very much for your help.

I put me a question! you are Russian? And where do you live? So you
needn't answer if you want.

Fabrice.

Hello, FG!
You wrote  on 22 Apr 2002 12:19:45 -0700:

 F> I put me a question! you are Russian?
Yes I am.

 F> And where do you live? So you needn't answer if you want.
Saint-Petersburg, Russia.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port

ICQ UID: 29427861(MasterZIV)