Oracle Trigger- Something missing?

Vote count:

0

I am new to using Oracle/SQLPlus. I am trying to write a trigger that checks the amount of "captains" that are currently assigned to a team after a row is updated, added, or deleted, and sends out a violation message if that number is greater than 1.

I would love to test if my logic works, but when I try to create this trigger, the query does not "close"(for lack of a better term, considering my lack of knowledge). In other words, when I load the sql file that contains this trigger it just gives me a new line prompt and shown below. (My code ends with 'END;')

CREATE OR REPLACE TRIGGER one_captain AFTER INSERT OR DELETE OR UPDATE ON play_in_team
REFERENCING NEW AS n
FOR EACH ROW
DECLARE 
    captcount number;
BEGIN 
    SELECT COUNT(*) into captcount
    FROM play_in_team
    WHERE is_captain = 1 AND team_id = :n.team_id;
    IF captcount > 1 THEN
            INSERT INTO violations (team_id, violation_text)
            SELECT team_id, 'VIOLATION- Multiple Captains: ' || LISTAGG(firstname || ' ' || lastname, ', ') WITHIN GROUP (ORDER BY lastname)
            FROM play_in_team NATURAL JOIN player
            WHERE is_captain = 1 AND team_id = :n.team_id
            GROUP BY team_id;
    END IF;
END;
19
20
21

I have stared at this for quite a while and I can't figure out what is wrong. Can anyone help?

asked 1 min ago

ReezaCoriza

38