Before insert trigger not functioning in 8.1.7.3
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Before insert trigger not functioning in 8.1.7.3

  1. #1
    Join Date
    Apr 2002
    Posts
    61

    Unhappy

    I have created the following small table and trigger to simulate my problem for you as follows:

    -- Table:

    CREATE TABLE qis.rk_test_num
    (
    seqn NUMBER(8),
    seqv VARCHAR2(8)
    )
    /
    -- Trigger

    CREATE OR REPLACE TRIGGER qis.t_rk_test_num
    BEFORE INSERT
    ON qis.rk_test_num
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    Begin
    if :new.seqn = ' ' then :new.seqn := null; end if;
    End;
    /

    My goal is to check the insert values to make sure that users are not inserting blanks, before I actually insert into the table.

    This used to work fine on Oracle 8.0.5. We migrated to 8.1.7.3 a couple days ago. Since migration, my insert statements are failing due to this trigger (Please see below). I am guessing this might be a bug in Oracle 8.1.7.3.

    SQL> insert into rk_test_num values(1,'1');
    insert into rk_test_num values(1,'1')
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "QIS.T_RK_TEST_NUM", line 2
    ORA-04088: error during execution of trigger 'QIS.T_RK_TEST_NUM'

    SQL> insert into rk_test_num values(' ','1');
    insert into rk_test_num values(' ','1')
    *
    ERROR at line 1:
    ORA-01722: invalid number

    Please help. Thanks in Advance.

    Ramesh




  2. #2
    Join Date
    Feb 2001
    Posts
    180
    It seems to me that:
    if :new.seqn = ' ' then :new.seqn := null; end if;
    is useless.
    In :new.seqn = ' ' it is comparing a numeric against a string, what can cause the problem.
    On the other hand the check is not needed here, as you can notice in:
    insert into rk_test_num values(' ','1');
    You'll get an error


    Regards
    Ben de Boer

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