I have the following tables.
create table player (
player_id int,
firstname varchar(32),
lastname varchar(32),
birthdate date,
country varchar(3),
constraint pk_player primary key (player_id)
);
create table team (
team_id int,
name varchar(32),
city_id int,
manager_id int,
coach_id int,
constraint pk_team primary key (team_id),
constraint fk_team_city foreign key (city_id)
references city (city_id),
constraint fk_team_manager foreign key (manager_id)
references manager (manager_id),
constraint fk_team_coach foreign key (coach_id)
references coach (coach_id)
);
create table play_in_team (
player_id int,
team_id int,
from_date date,
to_date date,
position varchar(5),
squad_number int,
is_captain char(1),
constraint pk_play_in_team primary key (player_id, team_id, from_date),
constraint fk_play_player foreign key (player_id)
references player (player_id),
constraint fk_play_team foreign key (team_id)
references team (team_id)
);
create table violations (
team_id int,
violation_text varchar(200),
constraint fk_vio_team foreign key (team_id)
references team (team_id)
);
I've been assigned the following problem.
Create a trigger that will insert a violation message every time a team has more than one captains at any point of time. The violation message should include the phrase ”VIOLATION - Multiple Captains”, and the captains’ names. You may insert pairs of captain names in each violation message.
I keep getting syntax errors when trying to compile my trigger and am hoping someone can point me in the right direction. Right now, my trigger is defined as...
CREATE OR REPLACE TRIGGER one_captain_check
AFTER INSERT OR UPDATE OF player_id, team_id, from_date, to_date, is_captain ON play_in_team
FOR EACH ROW
WHEN(new.is_captain = '1')
BEGIN
MERGE INTO violations
USING (
SELECT
play_in_team.team_id,
('VIOLATION - Multiple Captains - ' || existing_captain.firstname || ' ' || existing_captain.lastname || ', ' || new_captain.firstname || ' ' || new_captain.lastname) "violation_text"
FROM play_in_team
JOIN player existing_captain
ON existing_captain.player_id = play_in_team.player_id
JOIN player new_captain
ON new_captain.player_id = :new.player_id
WHERE
play_in_team.team_id = :new.team_id AND
play_in_team.player_id != :new.player_id AND
play_in_team.is_captain = '1' AND
-- overlapping date ranges from http://tonyandrews.blogspot.com/2010/06/sql-overlap-test.html
NVL(:new.to_date, from_date) >= from_date AND
:new.from_date <= NVL(to_date, :new.from_date)
) new_violations
ON (violations.team_id = new_violations.team_id AND violations.violation_text = new_violations.violation_text)
WHEN NOT MATCHED THEN
INSERT VALUES(new_violations.team_id, new_violations.violation_text);
END;
/
And I get the following errors in sqlplus when I try to run it.
LINE/COL ERROR
2/3 PL/SQL: SQL Statement ignored
20/83 PL/SQL: ORA-00904: "NEW_VIOLATIONS"."VIOLATION_TEXT": invalid identifier
I'm new to Oracle and PL/SQL and have been trying variations of the above trigger but just can't get it to compile. I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0. Any ideas how to fix the syntax?