Cannot to create trigger properly

Question

Oracle apex, PL_Sql. This is my trigger:

CREATE OR REPLACE TRIGGER NoMoreThanOneHorse
    BEFORE INSERT OR UPDATE OF Jockey_ID
    ON Horses
    FOR EACH ROW
DECLARE
    NumOfHorsesForJockey NUMBER(4);
BEGIN
    SELECT COUNT(*) INTO NumOfHorsesForJockey FROM Horses
        WHERE Jockey_ID = :NEW.Jockey_ID AND Horse_ID <> :NEW.Horse_ID;
    IF NumOfHorsesForJockey > 0
        THEN RAISE_APPLICATION_ERROR (-20445, 'Нельзя закрепить за лошадью уже занятого жокея!');
        END IF;
END NoMoreThanOneHorse;

but every time i try to create it i have this error :ORA-24344: success with compilation error ORA-06512. I suppose it's because bad syntax

Add SHOW ERRORS; at the end of your pl/sql block. It will show the exact error on the result page. — Novice_Techie
@Novice_Techie, idk why but it doesn't show me any errors, only one i described before — Александра Юреня
Try using END; instead of END NoMoreThanOneHorse; in the last line. — Novice_Techie

Bob Jarvis - Слава Україні Bob Jarvis - Слава Україні · Accepted Answer · 2019-12-17T00:28:17

I don't know what the compile-time error might be - it compiles fine for me. But at run-time you'll probably get an `ORA-04091 Table HORSES is mutating, trigger cannot see it" error. You can't fetch from HORSES in a ROW trigger which is defined on the HORSES table.

What you need to do is to change this to an AFTER STATEMENT trigger, which you do by leaving off the FOR EACH ROW in the trigger definition, and changing the trigger point from BEFORE to AFTER. The problem is that in a statement trigger you don't have access to the :NEW or :OLD data, but you don't really need it. A statement trigger is invoked only once for each statement which is executed. So your trigger should look something like:

CREATE OR REPLACE TRIGGER NoMoreThanOneHorse
    AFTER INSERT OR UPDATE OF Jockey_ID
    ON Horses
DECLARE
    nMaxHorses  NUMBER;
BEGIN
    SELECT MAX(HORSE_COUNT)
      INTO nMaxHorses
      FROM (SELECT JOCKEY_ID, COUNT(*) AS HORSE_COUNT
              FROM Horses
              GROUP BY Jockey_ID);

    IF nMaxHorses > 1 THEN
      RAISE_APPLICATION_ERROR (-20445, 'Нельзя закрепить за лошадью уже занятого жокея!');
    END IF;
END NoMoreThanOneHorse;

You really don't care which jockey has been assigned too many horses, only that there is a jockey who's been put on more than one horse.

db<>fiddle here

Cannot to create trigger properly

1 Answers