0

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?

1 Answer 1

2

I don't have an Oracle installed over here to test it - but could you please not put the "violation_text" within quotation marks at the 10th line, or to put it in upper case within the quotation marks?

Oracle by default changes every lower case that is not within quotation marks to uppercase (for table and column names), so practically your column is new_violations."violation_text" which is not equal to new_violations .violation_text - which is new_violations."VIOLATION_TEXT"...

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much. That was my problem. Once I capitalized the column alias the trigger installed just fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.