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, Dec 17 '19 at 00:11
@Novice_Techie, idk why but it doesn't show me any errors, only one i described before — Александра Юреня, Dec 17 '19 at 00:17
Try using END; instead of END NoMoreThanOneHorse; in the last line. — Novice_Techie, Dec 17 '19 at 00:27

score 1 · Accepted Answer · answered Dec 17 '19 at 00:28

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

If this is a real question rather than a homework assignment, unless the table is really small and seldom inserted into, doing a full scan of the table and grouping the data every time you insert a new row is going to be rather slow (and expensive). If you want to go with a trigger, you'd want a row-level trigger that puts the changed rows in a collection and uses that collection to narrow down the query the statement-level trigger runs. — Justin Cave, Dec 17 '19 at 03:16
@JustinCave - although OP *could* be writing software for a race track I strongly suspect this is a homework assignment, which is why I suggested a statement trigger. [I agree that in the real world a compound trigger would be a better solution](https://stackoverflow.com/a/29491264/213136). — Bob Jarvis - Слава Україні, Dec 17 '19 at 03:28
@BobJarvisReinstateMonica - Agreed. Just pointing it out for any future readers that might have a real system. — Justin Cave, Dec 17 '19 at 03:31
@BobJarvisReinstateMonica - Let me enjoy my fantasy world, OK? — Justin Cave, Dec 17 '19 at 03:51

Cannot to create trigger properly

1 Answers1