INSERT/UPDATE TRIGGER error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: INSERT/UPDATE TRIGGER error

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    Cool

    CREATE OR REPLACE TRIGGER cust_trig_iu
    AFTER insert or update on CUST
    For each row
    BEGIN
    IF INSERTING then
    INSERT into CUST_INTERFACE (
    c_id,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
    VALUES (:new.c_id,:new.f_name,:new.mi,:new.l_name,:new.inst_id,:new.pay_freq,:new.lookup2,:new.lookup3,:new .action_code,
    'I',to_char(sysdate,'DD-MON-YY HH24:MM:SS'));
    ELSE
    INSERT into CUST_INTERFACE (
    c_id,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
    VALUES (
    :new.c_id,:new.inst_id,:new.f_name,:new.mi,:new.l_name,:new.pay_freq,:new.lookup2,:new.lookup3,:new. action_code,
    'U',to_char(sysdate,'DD-MON-YY HH24:MM:SS'));
    END IF;
    END;

    The trigger created quite alright without errors but when
    I insert or update on the cust table, I get the following errors:
    ora-01401: insert value too large for column (which is not true)
    ora-06512: at webuser, cust_tri_iu line 28
    ora-04088: error during execution of trigger

    I appreciate your input please:

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    compare length all fields in declaration both tables:
    desc CUST ;
    desc CUST_INTERFACE ;

    one or more fields in CUST_INTERFACE have less length then in CUST table.

  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    I have checked, all the columns from both tables have equal length and datatype.
    Thank you.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Next :
    check field - time_stamp - it should be no less then VARCHAR2(18) or CHAR(18)

  5. #5
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    I used VARCHAR2(25) for time_stamp, still when I update cust table for instance, it gives the following errors:

    SQL> update cust
    2 set cust_id=6914
    3 where pin='8822';
    update cust
    *
    ERROR at line 1:
    ORA-01401: inserted value too large for column
    ORA-06512: at "WEBUSER.CUST_TRIG_IU", line 28
    ORA-04088: error during execution of trigger 'WEBUSER.CUST_TRIG_IU'


  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    If u may, post to forum describe statments of both tables.
    desc CUST ;
    desc CUST_INTERFACE ;

  7. #7
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Surely there is no problem with the time_stamp column but there is a mismatch with the datatype and width with one of the column.Try using the trim function in your insert statement because char embeds spaces whereas varchar does not.if there is still a problem desc the table in the forum as said

    Regards
    Santosh

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