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...
FUNCTION check_contract_length (V_playerId IN NUMBER)
SELECT months_between(contract_end, contract_start), contract_length
INTO months, months2
WHERE playerId = v_playerId;
IF months <> months2 THEN
The trigger operates when an insert is made on to the player table, it as defined as follows...
IF INSERTING THEN
isover := player_constr_pkg.check_contract_length(:new.playerId);
IF isover THEN
RAISE_APPLICATION_ERROR(-30001, 'Contract length incorrect');
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.
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:
CREATE OR REPLACE TRIGGER player_trg
AFTER INSERT ON player
FOR EACH ROW
IF INSERTING THEN
IF months_between(:new.contract_end, :new.contract_start) <> :new.contract_length THEN
RAISE_APPLICATION_ERROR(-20001, 'Contract length incorrect');
(BTW, I would have seen this question and replied sooner if you had posted it in the correct forum - i.e. Oracle.)