Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Angry Unanswered: Trigger problem: No data found error

    Hi all,

    I'm trying to create a fairly simple trigger which fires after an insert on a table.

    The trigger is based on a function, defined within a package. The function is as follows...

    Code:
    FUNCTION check_contract_length (V_playerId IN NUMBER)
    RETURN BOOLEAN
    IS
    months NUMBER;
    months2 NUMBER;
    BEGIN
    SELECT months_between(contract_end, contract_start), contract_length 
    INTO months, months2
    FROM player
    WHERE playerId = v_playerId;
    
    IF months <> months2 THEN
     RETURN (true);
    END IF;
    RETURN (false);
    END check_contract_length;
    The trigger operates when an insert is made on to the player table, it as defined as follows...

    Code:
    BEGIN
    
    IF INSERTING THEN
    isover := player_constr_pkg.check_contract_length(:new.playerId);
    IF isover THEN
    RAISE_APPLICATION_ERROR(-30001, 'Contract length incorrect');
    END IF;
    END IF;
    
    END play_ins_con;
    My problem is, is that every time I try to insert a row into the player table, the trigger fires and produces a "ora-01403: no data found" error. I was under the impression that as the trigger fires after an insert, this shouldn't be a problem.

    I'm also aware that this constraint can probably be implemented using a CHECK constraint, however it would be a learning curve for me to work it out this way.

    Any help would be much appreciated!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since you are not getting ORA-04091 (table XXX.PLAYER is mutating, trigger/function may not see it), you must have used the AUTONOMOUS_TRANSACTION pragma. This is commonly, but quite wrongly, believed to be a "solution" to the mutating table issue. In this case, it causes the NO_DATA_FOUND precisely because the trigger is thus not party to your main transaction and so cannot see the row just inserted!

    You could solve this with the correct mutating table work-around involving statement-level triggers and collections (see Avoiding Mutating Tables by Tom Kyte). But really, that would be a sledgehammer to crack this peanut.

    As you say yourself, the correct way to do this is via a check constraint; but if you want to build a trigger-based solution for the experience, it can still be so much simpler. There is no need to SELECT the row at all, since the trigger already has it! The trigger can be:
    Code:
    CREATE OR REPLACE TRIGGER player_trg
    AFTER INSERT ON player
    FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        IF months_between(:new.contract_end, :new.contract_start) <> :new.contract_length THEN
          RAISE_APPLICATION_ERROR(-20001, 'Contract length incorrect');
        END IF;
      END IF;
    END play_ins_con;
    (BTW, I would have seen this question and replied sooner if you had posted it in the correct forum - i.e. Oracle.)

Posting Permissions

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