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

Thread: INSERT/UPDATE TRIGGER error

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Thank you for all your input. I tried using trunc function which allows me to insert and update cust table without any error but it does not insert or update cust_interface table.
    How do you use the trim function?
    here is the description of the tables as requested by the forum:

    SQL> desc cust;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    C_ID NOT NULL VARCHAR2(15)
    INST_ID VARCHAR2(15)
    PAYROLL_ID_NUM VARCHAR2(25)
    COMPANY_NAME VARCHAR2(50)
    SALUTATION VARCHAR2(7)
    F_NAME NOT NULL VARCHAR2(25)
    MI VARCHAR2(1)
    L_NAME NOT NULL VARCHAR2(25)
    SUFFIX VARCHAR2(7)
    EMAIL NOT NULL VARCHAR2(50)
    BIRTHDAY DATE
    REFERRAL_TYPE_CD VARCHAR2(25)
    FOLLOWUP_FLAG NOT NULL NUMBER(1)
    SSN VARCHAR2(11)
    USERNAME VARCHAR2(25)
    PIN VARCHAR2(25)
    PAYMENT_INFO_ID NUMBER(10)
    HTTP_REFERER VARCHAR2(128)
    SALARY NUMBER(8,2)
    DOWNLOAD_ID VARCHAR2(25)
    BACKEND_ID NUMBER(10)
    ACTIVE NUMBER(3)
    CHANGED_BY NOT NULL VARCHAR2(25)
    CHANGED_DATE NOT NULL DATE
    LOOKUP2 VARCHAR2(15)
    LOOKUP3 VARCHAR2(15)
    ACTION_CODE VARCHAR2(15)
    PAY_FREQ VARCHAR2(15)

    SQL> desc cust_interface;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    C_ID VARCHAR2(15)
    INST_ID VARCHAR2(15)
    F_NAME VARCHAR2(25)
    MI VARCHAR2(1)
    L_NAME VARCHAR2(25)
    LOOKUP2 VARCHAR2(15)
    LOOKUP3 VARCHAR2(15)
    ACTION_CODE VARCHAR2(15)
    PAY_FREQ VARCHAR2(15)
    ADDRESS_ID VARCHAR2(15)
    ADDRESS_TYPE_CD VARCHAR2(25)
    IS_DEFAULT NUMBER(5)
    STREET_1 VARCHAR2(50)
    STREET_2 VARCHAR2(50)
    CITY VARCHAR2(25)
    STATE_PROVINCE VARCHAR2(25)
    ZIP VARCHAR2(11)
    COUNTRY VARCHAR2(50)
    COUNTY VARCHAR2(25)
    INSERT_TYPE VARCHAR2(3)
    TIME_STAMP VARCHAR2(25)

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    I checked all fields and they seems good.
    But 1 more detail!!!

    We have message:
    ora-06512: at webuser, cust_tri_iu line 28 (!!! line = 28)

    in triggers source code :
    ------------------------------------------------
    1. CREATE OR REPLACE TRIGGER cust_trig_iu
    2. AFTER insert or update on CUST
    3. For each row
    4. BEGIN
    5. IF INSERTING then
    6. INSERT into CUST_INTERFACE (
    7. d,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
    9. 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'));
    9. ELSE
    10. INSERT into CUST_INTERFACE (
    c_id,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
    11. 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'));
    12. END IF;
    13. END;

    we have less lines.
    My question - are u check source code in database or not?


  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    What do you mean by less line? How do I check the source code in the database? Is it by quering user_triggers?

  4. #4
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    The columns were listed singly before in SQL*Plus. That's why you see line 28. But now, I joined the columns in a straight line.

  5. #5
    Join Date
    Mar 2001
    Posts
    635
    Hi

    There is a problem in this line

    6. INSERT into CUST_INTERFACE (
    7. d,f_name,mi,l_name,inst_id,pay_freq,lookup2,lookup3,action_code,insert_type,time_stamp)
    9. 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'));

    In 7th line dont you think

    d should be c_id

    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