Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Unanswered: problem with triggers

    Hello everyone,
    i am working with triggers in PL/SQL. The function is the check whether the new ID entered is the next increasing integer wrt. the previous entered integer.
    eg. the ID should be entered as 4,5,6,7..................

    CREATE TABLE ORDERS (ID INTEGER, STR CHAR(20));

    The basic layout of the trigger is as follows:

    CREATE OR REPLACE TRIGGER trig1
    BEFORE INSERT ON ORDERS
    FOR EACH ROW
    DECLARE
    prev_val NUMBER;
    BEGIN
    SELECT ID INTO prev_val from
    (SELECT * FROM ORDERS ORDER BY ID desc)
    WHERE ROWNUM<2;
    dbms_output.put_line('STEP 1: '||:NEW.ID||' '||prev_val);
    if (:NEW.ID-prev_val<>1)
    then
    raise_application_error(-20202,'WRONG ID');
    end if;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    prev_val:=0;
    END trig1;
    .
    run;

    Now I run the following commands in sqlplus:

    SQL> insert into ORDERS VALUES(4,'s1');

    1 row created.

    SQL> insert into ORDERS VALUES(5,'s2');
    STEP 1: 5 4

    1 row created.

    SQL> insert into ORDERS VALUES(2,'s0');
    insert into ORDERS VALUES(2,'s0')
    *
    ERROR at line 1:
    ORA-20202: WRONG ID
    ORA-06512: at "HVIRANI.TRIG1", line 12
    ORA-04088: error during execution of trigger 'HVIRANI.TRIG1'


    SQL> insert into ORDERS VALUES(6,'s3');
    STEP 1: 2 5
    STEP 1: 6 5

    1 row created.



    My question is why the "STEP 1" statement is being written twice . Does it mean that the trigger is getting fired twice and if yes why?

    Thankx

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    To start with, using sequence is the best solution for this scenario. You can be sure that the next value will always be greater then current value.
    Your search :
    >SELECT ID INTO prev_val from
    >(SELECT * FROM ORDERS ORDER BY ID desc)
    >WHERE ROWNUM<2;
    is an unnecessary exercise that should be avoided. Well, imagine the multi user scenario.

Posting Permissions

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