Trigger problem: No data found error

NickJ · #1 (**permalink**) 11-15-04, 12:21

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!!

andrewst · #2 (**permalink**) 11-17-04, 08:11

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.)

Internet Services

Web Development

Digital Marketing

Consumer Tech