Results 1 to 2 of 2

Thread: error trigger

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: error trigger

    Hi,
    I have these tables:

    desc LEAD
    BL_ID VARCHAR2(32)
    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,D ATA_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
    (DECODE(:OLD.USE1,'21','EDIFICIO','22','EDIFICIO', '23','EDIFICIO','24',
    'EDIFICIO','25','EDIFICIO','26','EDIFICIO','31','E DIFICIO','41','EDIFICIO',
    '42','EDIFICIO','51','EDIFICIO','52','EDIFICIO','5 4','EDIFICIO','57','EDIFICIO',
    '58','EDIFICIO','64','EDIFICIO','60','EDIFICIO','6 3','CONTAINER','65','CONTAINER',
    '62','PALO','66','RIPETITORE','61','TRALICCIO','67 ','TRALICCIO','68','TRALICCIO',
    '1','TERRENO','2','TERRENO','3','TERRENO','4','TER RENO','5','TERRENO','6','TERRENO','N/A'),
    :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 );
    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    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
    Jul 2003
    Posts
    2,296
    What the heck are you trying to set it to??

    Right now you are setting the date to itself.
    Is that what you want to do? And if so, what the heck is the value?

    What if it is null?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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