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)
);

CodePudding user response：

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 NEW (resp. 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 sql query.

The proposed sql query :

• First search for the rows in table "race" whose code_race equals the inserted/updated value of participation.codR = NEW.codR
• Then search for the rows in table "athlete" whose code_athlete equals the inserted/updated value of participation.codA = NEW.codA
• Finally compare the selected rows from both tables "race" and "athlete" using a regular expression to compare the code_race with the gender values, see the manual

By the way, 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.