Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2010
    Posts
    5

    Unanswered: My trigger is inserting same record repeatedly up on one update

    Hi ,

    I wrote a trigger on MESSAGES table to fire on update whenever the status field is changing to POSTED or DELETED, the trigger on update has to insert the POSTED or DELETED records to new table SAP_MESSAGES.

    Trigger is firing properly up on update , but whenever status field is updated to DELETED in MESSAGES table, the records are keep on inserting into SAP_MESSAGES table for alomost every half or 1 minute, it is not happening when status is updated to "POSTED" in MESSAGES table, it just fires one time and inserts POSTED record to SAP_MESSAGES table.

    can somebody help where is the problem..

    thanks,
    SK

    here is my code

    Code:
    create or replace
    TRIGGER ECANDON.SAP_MESSAGES_TRIGGER
    AFTER UPDATE
        ON ECANDON.MESSAGES
        FOR EACH ROW
    when (new.status  in ( 'POSTED', 'DELETED'))
    DECLARE
     v_material varchar2(50);
     v_status_ind varchar2(1);
     
    BEGIN
     
        -- Fetch the value of part number from clob filed messagedata.
       v_material  := dbms_lob.substr(:new.messagedata,
       dbms_lob.instr(:new.messagedata,'</partnumber>') - dbms_lob.instr(:new.messagedata,'<partnumber>')-12,
       dbms_lob.instr(:new.messagedata,'<partnumber>' )+12);
       
      IF  ( :new.status = 'DELETED')
       THEN
       v_status_ind :='D';
      END IF; 
         
       IF ( :new.status = 'POSTED')
       THEN
       v_status_ind :='P';
      END IF;
     
        
        -- Insert record into sap_messages table
        INSERT INTO ecandon.sap_messages
         ( id,
           material,
           status_indicator,
           time_stamp,
           msg_id )
        VALUES
         ( ECANDON.SAP_MESSAGE_SEQ.NEXTVAL,
           v_material,
           v_status_ind,
           sysdate,
           :new.id);
     
    END;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    show us the update command you are using for the POSTED and DELETED statuses. Cut and paste from sql*plus
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2010
    Posts
    5
    they are like this

    update ecandon.messages set status='POSTED' where id=1341

    update ecandon.messages set status='DELETED' where id=1341

    where id is unique in MESSAGES table

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You are not running that in sql plus, you are simply typing the command into the forum? how do I know what really happened, how many rows did it say it updated?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2010
    Posts
    5
    I didn't posted the results, but I know 1 row is updated for each update statment as id is unique(pK)

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is there any other trigger on the same table? If so, what does it do?

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    No there are no other triggers on the table,

    what I observed is , for unit testing when I am executing update sqls for same id(which is unique)one time changing status to POSTED and one time changing status to DELETED, it is inserting same record multiple times for each update.

    when I do one update on table to change status to POSTED and then make the record to DELETED, trigger is working fine, firing those two times and inserting only two records as per logic.

    I am wondering why executing update satement of same id multiple times is making weired behaviour of trigger.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    your trigger can't do what you are saying. In a sql*plus session run the following commands and then PASTE the output into the forum so we can see the actual results

    select count(*) from ecandon.messages where id=1341;

    select count(*) from ecandon.sap_messages where msg_id = 1341;

    update ecandon.messages set status='DELETED' where id=1341;

    select count(*) from ecandon.messages where id=1341;

    select count(*) from ecandon.sap_messages where msg_id = 1341;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2010
    Posts
    5
    here they are , first I updated the messages table with status as POSTED and then updated the table with status as DELETED, actually now I dont see the problem now, I just tried adding additional condition in when clause showed in bold below, dont know how it fixed, but it got fixed, I am new to PL/SQL as basically I am java developer and thus learning these things.

    please give me your comments what you think went wrong.

    when ( new.status in ( 'POSTED', 'DELETED')
    AND new.status != old.status )

    SQL> select count(*) from ecandon.messages where id=1381
    2 /

    COUNT(*)
    ----------
    1

    SQL> select count(*) from ecandon.sap_messages where id=1381
    2 /

    COUNT(*)
    ----------
    0
    SQL> update ecandon.messages set status='POSTED' where id =1381
    2 /

    1 row updated.

    SQL> commit;

    Commit complete.

    SQL> select count(*) from ecandon.messages where id=1381
    2 /

    COUNT(*)
    ----------
    1
    SQL> select count(*) from ecandon.sap_messages where msg_id = 1381
    2 /

    COUNT(*)
    ----------
    1

    SQL> update ecandon.messages set status='DELETED' where id=1381
    2 /

    1 row updated.

    SQL> commit;

    Commit complete.

    SQL> select count(*) from ecandon.messages where id=1381
    2 /

    COUNT(*)
    ----------
    1

    SQL> select count(*) from ecandon.sap_messages where msg_id = 1381
    2 /

    COUNT(*)
    ----------
    2

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    so what is the problem? It seems as though this is working as you want.
    Dave

Posting Permissions

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