Oracle PL/SQL Constraint Trigger

Question

I have tables: Teams and Matches.

'Teams' stores the information on football teams and the one I'm particularly interested in is the Country field.

'Matches' stores the information on football matches that happen between the teams listed in 'Teams'. The field I'm interested in from this table is the Competition field. Depending on the type of competition: "All-England" or "All-Spain", etc. the teams involved should be from the appropriate countries.

I'm currently trying to write a Constraint Trigger to handle this but am being thrown a really cryptic error by SQL Developer "invalid and failed re-validation" upon running even if it compiles alright. Anyone care to help? Suggestions on doing things a better way would be great too. PL/SQL block below and I apologise if the following code makes any of you cry. I know I'm terrible at this

CREATE OR REPLACE TRIGGER matchcountry_BIR
BEFORE INSERT ON matches
FOR EACH ROW

DECLARE
invalidEng EXCEPTION;
invalidSpa EXCEPTION;
team1 teams.Country%type;
team2 teams.Country%type;
comp matches.Competition%type;

BEGIN
SELECT Country INTO team1 FROM teams WHERE TeamID = :new.TeamID_A;
SELECT Country INTO team2 FROM teams WHERE TeamID = :new.TeamID_B;
comp:=:new.Competition;

IF (comp='All England') AND (team1!='England' AND team2!='England') THEN
RAISE invalidEng;
END IF;

IF (comp='All Spain') AND (team1!='Spain' AND team2!='Spain') THEN
RAISE invalidSpa;
END IF;

EXCEPTION
WHEN invalidEng THEN
RAISE_APPLICATION_ERROR(-20005, 'Countries are invalid for a English competition.');
END;

EXCEPTION
WHEN invalidSpa THEN
RAISE_APPLICATION_ERROR(-20006, 'Countries are invalid for a Spanish competition.');
END;

In case the problem lies with the tables, I've also included their Create statements. Again, any suggestions or criticisms on these are very welcome.

CREATE TABLE teams
(
  TeamID number(2) PRIMARY KEY,
  TeamName varchar2(50),
  Country varchar2(30),
  CHECK (Country='Spain' OR Country='England')
);

CREATE TABLE matches
(
  MatchID number(2) PRIMARY KEY,
  TeamID_A number(2),
  TeamID_B number(2),
  Goal_A number(2),
  Goal_B number(2),
  Competition varchar2(50),
  CONSTRAINT fk_TeamA FOREIGN KEY(TeamID_A) REFERENCES teams,
  CONSTRAINT fk_TeamB FOREIGN KEY(TeamID_B) REFERENCES teams,
  CHECK (Goal_A >= 0),
  CHECK (Goal_B >= 0),
  CHECK (Competition='Champions League' OR Competition='Europa League' OR Competition='All England' OR Competition='All Spain')
);

In Oracle you use the AND operator, not the &&, so you should change this in your code. Also, why are you selecting Competition from matches table where MatchID is the equal to the ID of the new record? You should just use :new.competition to check that value of the new record. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Oct 18, 2013 at 8:44
A compound key foreign key would enable you to solve your problem declaratively. — Colin 't Hart
– Colin 't Hart, Commented Oct 18, 2013 at 8:46
Ah thank you for the comments. I've put in the fixes you suggested, Przemyslaw. However, I'm still getting the same error. — ScoopsaHaagenDazs
– ScoopsaHaagenDazs, Commented Oct 18, 2013 at 8:53
Please update your code in your question so we can see how it looks like now. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Oct 18, 2013 at 9:02
you said the compilation went through but with exception handling twice ???,Kindly provide correct information — psaraj12
– psaraj12, Commented Oct 18, 2013 at 9:45

Przemyslaw Kruglej · Accepted Answer · 2013-10-18 09:09:55Z

1

You had 3 mistakes:

&& instead of AND.
Accessing matches table instead of :new.competition.
Double EXCEPTION keyword.

Try below code:

-- Trigger will not let an insert on the Match table with invalid countries for the competition
CREATE OR REPLACE TRIGGER matchcountry_BIR
BEFORE INSERT ON matches
FOR EACH ROW

DECLARE
  invalidEng EXCEPTION;
  invalidSpa EXCEPTION;
  team1 teams.Country%type;
  team2 teams.Country%type;
  comp matches.Competition%type;

BEGIN
  SELECT Country INTO team1 FROM teams WHERE TeamID = :new.TeamID_A;
  SELECT Country INTO team2 FROM teams WHERE TeamID = :new.TeamID_B;
  --SELECT Competition INTO comp FROM matches WHERE MatchID = :new.MatchID;
  comp := :new.competition;

  IF (comp='All England') AND (team1!='England' AND team2!='England') THEN
    RAISE invalidEng;
  END IF;

  IF (comp='All Spain') AND (team1!='Spain' AND team2!='Spain') THEN
    RAISE invalidSpa;
  END IF;

EXCEPTION
  WHEN invalidEng THEN
    RAISE_APPLICATION_ERROR(-20005, 'Countries are invalid for a English competition.');

  WHEN invalidSpa THEN
    RAISE_APPLICATION_ERROR(-20006, 'Countries are invalid for a Spanish competition.');
END;

answered Oct 18, 2013 at 9:09

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

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

1 Comment

ScoopsaHaagenDazs Over a year ago

Man I have a lot to learn. Constraint is now working as intended. I need to re-structure my tables but this helps give me a template to work off of. Thanks again.

Collectives™ on Stack Overflow

Oracle PL/SQL Constraint Trigger

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related