Postgresql Trigger To Compare The Third To Last Character With An Attribute
I have recently started studying PostgreSQL and am having trouble creating triggers. In the specific case I should check that a male athlete cannot participate in a competition for women and vice versa; in the match_code attribute an 'M' or an 'F' is inserted as the third to last character to identify that the race is for males or females (for example: 'Q100x4M06'); only one character, 'M' or 'F', is stored in the gender attribute. I would therefore need to understand how to compare them and activate the trigger when they are not correctly entered in the participation table. This is what i have assumed but i know it is wrong, it is just an idea, can someone help me?
CREATE FUNCTION check() RETURNS TRIGGER LANGUAGE PLPGSQL AS $BODY$ DECLARE x VARCHAR; y VARCHAR; BEGIN SELECT match_code INTO x FROM race; SELECT gender INTO y FROM athlete; IF $x LIKE '&$y__' == athlete.gender THEN RETURN new; ELSE RAISE EXCEPTION $$It is not possible to add an athlete of a gender that is not compatible with the competition$$; RETURN NULL; END IF; END; $BODY$
CREAT TRIGGER triggerCheck BEFORE INSERT OR UPDATE ON participation FOR EACH ROW EXECUTE PROCEDURE check();
below are the definitions of the tables:
CREATE TABLE race ( ID_g SERIAL NOT NULL, code_race VARCHAR (20) PRIMARY KEY, r_date DATE, discipline VARCHAR (20) NOT NULL ); CREATE TABLE athlete ( ID_a SERIAL NOT NULL, code_athlete INT CHECK (codice_atleta >= 0 AND codice_atleta <= 15000) PRIMARY KEY, name VARCHAR (30), surname VARCHAR (30) NOT NULL, nation VARCHAR (3) NOT NULL, gender CHAR CHECK (gender = 'M' OR gender = 'F'), b_date DATE, sponsor VARCHAR (20) ); CREATE TABLE participation ( ID_p SERIAL NOT NULL, codR VARCHAR (20) REFERENCES race (code_race) ON DELETE CASCADE ON UPDATE CASCADE, codA INT REFERENCES athlete (code_athlete) ON DELETE CASCADE ON UPDATE CASCADE, arrival_order INT CHECK (arrival_order > 0), r_time TIME DEFAULT '00:00:00.00', PRIMARY KEY (codG, codA) );
According to the tables definition, your trigger function should be someting like :
CREATE FUNCTION check() RETURNS TRIGGER LANGUAGE PLPGSQL AS $BODY$ BEGIN IF EXISTS ( SELECT 1 FROM race AS r INNER JOIN athlete AS a ON r.code_race ~ (a.gender || '..$') WHERE r.code_race = NEW.codR AND a.code_athlete = NEW.codA ) THEN RETURN NEW ; ELSE RAISE EXCEPTION 'It is not possible to add an athlete of a gender that is not compatible with the competition'; RETURN NULL; END IF; END; $BODY$
In a function called by a trigger, you can (have to) use the variable
OLD) so that to refer to the new row (resp. the old row) to be inserted or updated (resp. only to be updated) in the targeted table (
"participation" in your case) see the manual.
The proposed function doesn't need to declare any variable, as the test can be performed directly through the proposed
The proposed sql query :
- First search for the rows in table
code_raceequals the inserted/updated value of participation.codR =
- Then search for the rows in table
code_athleteequals the inserted/updated value of participation.codA =
- Finally compare the selected rows from both tables
"athlete"using a regular expression to compare the
gendervalues, see the manual
By the way,
(a) I don't see the added value of the columns ID of type
serial in the tables definition, especially as they are not used in any primary key nor foreign key.
(b) The significant codification defined for the
code_race attribute (for instance : 3rd last character defining the gender, an other character defining the competition level, ...) is a quite old-fashion practice that was used 30 years ago when the computer sciences had limited capacities and performances. In a more up-to-date approach, I would suggest you to manage these meaningful information in dedicated columns of the table
"race", and then, if you really need a significant composite
code_race, to implement it as a generated column :
CREATE TABLE race ( ID_g SERIAL NOT NULL PRIMARY KEY , gender_race CHAR(1) NOT NULL CHECK (gender_race IN ('M', 'F')) , competition_level VARCHAR(12) NOT NULL CHECK (competition_level IN ('Q-Qualifiers', 'H-Heats', 'S-Semifinals', 'F=Finals')) , code_race VARCHAR (20) GENERATED ALWAYS AS (ID_g :: text || '-' || discipline || '-' || gender_race || '-' || left(competition_level, 1)) STORED , r_date DATE NOT NULL , discipline VARCHAR (20) NOT NULL );
see the result in db<>fiddle.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key