error trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: error trigger

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    error trigger

    Hi,
    I have these tables:
    desc LEAD
    BL_ID VARCHAR2(32)
    DATE_COST_ANAL_END DATE
    DATE_COST_ANAL_START DATE
    DATE_COSTS_LAST_CALCD DATE
    DATE_END DATE
    DATE_MOVE DATE
    DATE_START DATE
    FLOORS VARCHAR2(16)
    HPATTERN VARCHAR2(16)
    HPATTERN_ACAD VARCHAR2(35)
    IMAGE_DOC1 VARCHAR2(64)
    IMAGE_DOC2 VARCHAR2(64)
    IMAGE_DOC3 VARCHAR2(64)
    LANDLORD_TENANT NOT NULL VARCHAR2(8)
    LD_CONTACT VARCHAR2(32)
    LD_NAME VARCHAR2(32)
    LEASE_SUBLEASE NOT NULL VARCHAR2(8)
    TN_NAME VARCHAR2(32)
    TN_CONTACT VARCHAR2(32)
    SPACE_USE VARCHAR2(16)
    SIGNED NOT NULL NUMBER(38)
    QTY_SUITE_OCCUPANCY NOT NULL NUMBER(38)
    QTY_OCCUPANCY NOT NULL NUMBER(38)
    OWNED NOT NULL NUMBER(38)
    OPTION2 VARCHAR2(16)
    OPTION1 VARCHAR2(16)
    LS_PARENT_ID VARCHAR2(16)
    LEASE_TYPE VARCHAR2(10)
    LS_ID NOT NULL VARCHAR2(32)
    USE1 NOT NULL VARCHAR2(32)
    NAME VARCHAR2(60)
    CODE_BL_PADRE VARCHAR2(32)
    ADDRESS1 VARCHAR2(50)
    ZIP VARCHAR2(10)
    COUNTY_ID VARCHAR2(16)
    CITY_ID VARCHAR2(48)
    CTRY_ID VARCHAR2(16)
    DATA_CESSAZ DATE
    DATA_AGGIOR DATE
    DATA_AGGIOR_AFM DATE
    CODE_CT_UBIC VARCHAR2(4)
    CODE_CT_COMP VARCHAR2(4)
    FLAG_CANC VARCHAR2(2)
    CT_SERVCO VARCHAR2(3)
    NOTE VARCHAR2(200)
    TEL_CENTR VARCHAR2(30)
    FAX_CENTR VARCHAR2(30)
    NOP VARCHAR2(4)
    STATE_ID VARCHAR2(32)
    REGN_ID VARCHAR2(16)
    REGN_ID2 VARCHAR2(16)
    FLAG_RSG CHAR(1)
    DATA_FINE_UTILIZZO DATE
    COD_IMM_PRECEDENTE VARCHAR2(20)
    COD_IMM_SUCCESSIVO VARCHAR2(20)
    PROPRIETA CHAR(1)
    PROPRIETARIO VARCHAR2(64)
    desc TEAR_PM
    TIPO_OGG VARCHAR2(10)
    COD_IMM NOT NULL VARCHAR2(33)
    PT_SERVCO VARCHAR2(2)
    CT_SERVCO VARCHAR2(3)
    DENOM_AZ VARCHAR2(60)
    NOTE VARCHAR2(200)
    COD_IMM_SUP VARCHAR2(33)
    NOP VARCHAR2(4)
    INDIRIZZO VARCHAR2(50)
    CAP VARCHAR2(10)
    CODE_PROV VARCHAR2(16)
    COMUNE VARCHAR2(48)
    COUNTRY VARCHAR2(16)
    TEL_CENTR VARCHAR2(30)
    FAX_CENTR VARCHAR2(30)
    DATA_CESSAZ VARCHAR2(8)
    DATA_AGGIOR_AFM VARCHAR2(8)
    CODE_CT_UBIC VARCHAR2(4)
    CODE_CT_COMP VARCHAR2(4)
    DATA_FINE_UTILIZZO VARCHAR2(32)
    COD_IMM_PRECEDENTE VARCHAR2(20)
    COD_IMM_SUCCESSIVO VARCHAR2(20)
    PROPRIETA CHAR(1)
    desc TEAR_IS
    SITO VARCHAR2(64)
    TIPO_OGG VARCHAR2(10)
    COD_IMM NOT NULL VARCHAR2(39)
    PT_SERVCO VARCHAR2(2)
    CT_SERVCO VARCHAR2(3)
    DENOM_AZ VARCHAR2(50)
    NOTE VARCHAR2(200)
    COD_IMM_SUP VARCHAR2(32)
    NOP CHAR(3)
    INDIRIZZO VARCHAR2(50)
    CAP VARCHAR2(10)
    CODE_PROV VARCHAR2(16)
    COMUNE VARCHAR2(48)
    COUNTRY VARCHAR2(16)
    TEL_CENTR VARCHAR2(30)
    FAX_CENTR VARCHAR2(30)
    DATA_CESSAZ VARCHAR2(8)
    DATA_AGGIOR_AFM VARCHAR2(8)
    CODE_CT_UBIC VARCHAR2(4)
    CODE_CT_COMP VARCHAR2(4)
    DATA_FINE_UTILIZZO DATE
    COD_IMM_PRECEDENTE VARCHAR2(20)
    COD_IMM_SUCCESSIVO VARCHAR2(20)
    PROPRIETA VARCHAR2(1)
    I created this trigger:
    CREATE OR REPLACE TRIGGER AFTER_UPD
    AFTER UPDATE OF DATA_FINE_UTILIZZO, COD_IMM_SUCCESSIVO
    ON LEAD
    FOR EACH ROW
    DECLARE
    tmpVar NUMBER;
    BEGIN
    tmpVar := 0;
    BEGIN
    SELECT COUNT(*) INTO TMPVAR FROM TEAR_PM WHERE COD_IMM=substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0');
    IF TMPVAR>0 THEN
    DELETE TEAR_PM WHERE COD_IMM=substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0');
    END IF;
    INSERT INTO TEAR_PM (TIPO_OGG, COD_IMM,DENOM_AZ,COD_IMM_SUP, NOP, INDIRIZZO, CAP,CODE_PROV,COMUNE,COUNTRY,TEL_CENTR,FAX_CENTR,DATA_CESSAZ,DATA_AGGIOR_AFM,CODE_CT_UBIC, CODE_CT_COMP,DATA_FINE_UTILIZZO,COD_IMM_PRECEDENTE,COD_IMM_SUCCESSIVO,
    PROPRIETA)
    VALUES (ld.use1,substr(:OLD.LS_ID,1,2)||'-'||REPLACE(LTRIM(REPLACE(SUBSTR(:OLD.LS_ID,3),'0',' ')),' ','0'), :OLD.NAME,
    :OLD.CODE_BL_PADRE, :OLD.NOP,:OLD.ADDRESS1, :OLD.ZIP, :OLD.COUNTY_ID, :OLD.CITY_ID, :OLD.CTRY_ID,
    :OLD.TEL_CENTR,:OLD.FAX_CENTR, TO_DATE(:OLD.DATA_CESSAZ), TO_DATE(:OLD.DATA_AGGIOR_AFM), :OLD.CODE_CT_UBIC,
    :OLD.CODE_CT_COMP, TO_DATE(:OLD.DATA_FINE_UTILIZZO,'DDMMYYYY'), :OLD.COD_IMM_PRECEDENTE, :OLD.COD_IMM_SUCCESSIVO,:OLD.PROPRIETA);
    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END;
    BEGIN
    tmpVar := 0;
    SELECT COUNT(*) INTO TMPVAR FROM TEAR_IS WHERE SUBSTR(COD_IMM,8)=:OLD.LS_ID;
    IF TMPVAR>0 THEN
    DELETE TEAR_IS WHERE SUBSTR(COD_IMM,8)=:OLD.LS_ID;
    END IF;

    INSERT INTO TEAR_IS (TIPO_OGG, COD_IMM, COD_IMM_SUP, NOP,
    TEL_CENTR, FAX_CENTR, DATA_CESSAZ, DATA_AGGIOR_AFM,
    CODE_CT_UBIC, CODE_CT_COMP, DATA_FINE_UTILIZZO, COD_IMM_PRECEDENTE,
    COD_IMM_SUCCESSIVO, PROPRIETA )
    VALUES
    (ld.use1, :OLD.LS_ID,:OLD.CODE_BL_PADRE, 'EMS',
    :OLD.TEL_CENTR, :OLD.FAX_CENTR, TO_DATE(:OLD.DATA_CESSAZ), TO_DATE(:OLD.DATA_AGGIOR_AFM),
    :OLD.CODE_CT_UBIC, :OLD.CODE_CT_COMP, TO_DATE(:OLD.DATA_FINE_UTILIZZO, 'DDMMYYYY'), :OLD.COD_IMM_PRECEDENTE,
    :OLD.COD_IMM_SUCCESSIVO, :OLD.PROPRIETA );
    END;
    END AFTER_UPD;
    but when I update LEAD table (update LEAD
    set data_fine_utilizzo=data_fine_utilizzo
    where ls_id='00001234')

    I get this error:
    ORA-01401: inserted value too large for column
    ORA-06512: at "AFTER_UPD", line 76
    ORA-04088: error during execution of trigger 'AFTER_UPD'
    What i wrong??
    Thanks
    Raf

  2. #2
    Join Date
    Oct 2002
    Location
    Ljubljana,Slovenia
    Posts
    28
    As Oracle says : ORA-01401: inserted value too large for column

    You try to insert into TIPO_OGG "VARCHAR2(10)" data from column
    USE1 "VARCHAR2(32)"
    Aleš Orehek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width