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

    Question Unanswered: Problem with triggers

    Hi All

    With only a passing knowledge of triggers, I recently updated an old trigger for a legacy application I'm working on. Previously, the trigger allocates a "company identifier" to all new companies from an allocation table (please don't ask why, its a legacy system). The new trigger now runs a second set of commands to allocate a "company number" or "ceres identifier" (again, I know it's stupid) to the company, in addition. The trigger is as follows:

    Code:
    CREATE OR REPLACE TRIGGER COMPANY_IDENTIFIER_TRIGGER
    BEFORE INSERT ON COY_COMPANY
      FOR EACH ROW
    begin
    	select SEQ_NO
    	  into :new.COMPANY_IDENTIFIER
    	  from COY_COMPANY_CODE_ALLOCATE
    	  where ID in (select min (id) from COY_COMPANY_CODE_ALLOCATE where ALLOCATED = 'N');
    
    	update coy_company_code_allocate
    	  set allocated = 'Y'
    	  where seq_no = :new.COMPANY_IDENTIFIER;
    
    	SELECT SEQ_NO
    	  INTO :new.CERES_IDENTIFIER
    	  FROM COY_COMPANY_NUMBER_ALLOCATE
    	  WHERE ID IN (SELECT MIN (id) FROM COY_COMPANY_NUMBER_ALLOCATE WHERE ALLOCATED = 'N');
    
    	UPDATE COY_COMPANY_NUMBER_ALLOCATE
    	  SET ALLOCATED = 'Y'
    	  WHERE SEQ_NO = :new.CERES_IDENTIFIER;
      end;
    /
    The problem is that the third statement in the BEGIN block (SELECT SEQ_NO INTO :new.CERES_IDENTIFIER...) doesn't seem to be working. When I examine the company table contents, COY_COMPANY.COMPANY_IDENTIFIER has been correctly inserted for new companies, but COY_COMPANY.CERES_IDENTIFIER is blank! In other words, the identical first SELECT INTO statement seems to be working, but not my new one!

    Even more strange, the fourth and final UPDATE statement does seem to correctly work as company numbers/ceres identifiers seem to have been correctly allocated (ALLOCATED == "Y"). This would indicate that that third, problematic statement has worked, but for some reason not updated the COY_COMPANY table.

    Any help or insight anyone can give would be greatly appreciated. TIA...

    -- Ricardo

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh boy, where to start laying into this one! But of course you realise it is cr*p already. Are you aware that you can end up with 2 rows having the same COMPANY_IDENTIFIER or CERES_IDENTIFIER with this trigger?

    If :new.CERES_IDENTIFIER is used correctly at step 4 then it will surely be used to update the value in the trigger table also - unless there is another trigger that then sets it back to NULL?

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    Quote Originally Posted by andrewst
    Oh boy, where to start laying into this one! But of course you realise it is cr*p already. Are you aware that you can end up with 2 rows having the same COMPANY_IDENTIFIER or CERES_IDENTIFIER with this trigger?
    Unfortunately, the whole set-up is reliant on even more creeky legacy applications that need these values. I know, it's really bad.

    Quote Originally Posted by andrewst
    If :new.CERES_IDENTIFIER is used correctly at step 4 then it will surely be used to update the value in the trigger table also - unless there is another trigger that then sets it back to NULL?
    AFAICT there are no other triggers or table constraints deleting or otherwise affecting the :new.CERES_IDENTIFIER. Is there anyway to trace the SQL calls. I'm using TOAD 8.6.0.38 at the moment.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It sounds like your COY_COMPANY_NUMBER_ALLOCATE table has run out of unallocated id numbers
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2006
    Posts
    5
    Quote Originally Posted by beilstwh
    It sounds like your COY_COMPANY_NUMBER_ALLOCATE table has run out of unallocated id numbers
    Thanks for the suggestion, but unfortunately I don't think that can be it: the COY_COMPANY_NUMBER_ALLOCATE table seems to have plenty of unallocated values. Is it possible there is something wrong with the trigger itself?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, here are some suggestions which might (or might not) help: first, kick out ':new.ceres_identifier' as it seems that you do not realize its side-effects (read Tony's post). If you do it, there's no need for SELECT statements at all - put them into the WHERE clause directly. Besides, working with IN operator you might end with the TOO-MANY-ROWS error once you decide to remove the MIN aggregate function.

    As both UPDATES look the same, I'll use only one of them in the example. Here it goes:
    Code:
    BEGIN
       UPDATE coy_company_code_allocate
          SET allocated = 'Y'
        WHERE seq_no = (SELECT seq_no
                          FROM coy_company_code_allocate
                         WHERE ID = (SELECT MIN (ID)
                                       FROM coy_company_code_allocate
                                      WHERE allocated = 'N'));
    END;
    Futhermore, it seems that using the 'seq_no' column is unnecessary and it only makes the statement more complicated than it should be. Kick it also out and rely on 'ID' instead of 'seq_no' (if possible). Now we have this, relatively simple code:
    Code:
    BEGIN
       UPDATE coy_company_code_allocate
          SET allocated = 'Y'
        WHERE ID = (SELECT MIN (ID)
                      FROM coy_company_code_allocate
                     WHERE allocated = 'N');
    END;
    Does any of this make any sense to you?

  7. #7
    Join Date
    Jan 2003
    Location
    China
    Posts
    38
    The second block 'UPDATE...' might cause the problem, so just put your new 'select ... into ...' after the first block and try again.

    Make sense? :-)
    Fan Yi

  8. #8
    Join Date
    Mar 2006
    Posts
    5
    Quote Originally Posted by Littlefoot
    Does any of this make any sense to you?
    Yes, it certainly did and I really appreciate your thoughtful response. Unfortunately, I think I may have wasted everybodies time: it does appear there is a subsequent update statement in the code that was wiping the CERES_IDENTIFIER. I cannot apologise enough and I would like to thank everyone for their thoughtful remarks.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's no need to apologise; you had a problem and we tried to help you sort it out. If we succeeded, fine. If not, oh well ...

  10. #10
    Join Date
    Mar 2006
    Posts
    5
    Actually, you guys did help. When andrewst and others suggested something else might have modified the record it started me thinking... Again, thanks guys.

Posting Permissions

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