Help me write a Mysql trigger correctly

[kostastsirigos]

I have the following two tables:

TABLE 1:

    +-----------------------------+--------------------------------------+------+-----+---------+-------+
    | Field                       | Type                                 | Null | Key | Default | Extra |
    +-----------------------------+--------------------------------------+------+-----+---------+-------+
    | patient_id                  | bigint(20)                           | NO   | PRI | NULL    |       |
    | patient_wpid                | int(11)                              | NO   | PRI | NULL    |       |
    | age_at_visit                | int(11)                              | YES  |     | NULL    |       |
    | sex                         | enum('male','female')                | YES  |     | NULL    |

TABLE 2

    +-------------------------+----------------------------+------+-----+---------+----------------+
    | Field                   | Type                       | Null | Key | Default | Extra          |
    +-------------------------+----------------------------+------+-----+---------+----------------+
    | patient_id              | bigint(20)                 | NO   | PRI | NULL    |                |
    | patient_wpid            | int(11)                    | NO   | PRI | NULL    |                |
    | weight                  | text                       | YES  |     | NULL    |                |
    | creatinine              | text                       | YES  |     | NULL    |                |
    | eGFR                    | varchar(100)               | YES  |     | N/A     |                |
    +-------------------------+----------------------------+------+-----+---------+----------------+

What I need to do is, after an insert on Table 2, perform the following:

1. Select the age_at_visit and sex values from Table 1, for the given patient_id and patient_wpid (these fields have same values on both Table 1 and Table 2 and Table 1 has been filled BEFORE Table 2)

2. Use the inserted values of weight and creatinine in Table2 and calculate a formula, in order to produce, and consecutively store, a value for the eGFR column of Table2.
   The formula is the following:

   eGFR = 175 × (creatinine)^(-1.154) × (age)^(-0.203) × (0.742 if female)

So, what I wrote (and produced quite a few errors since it is my very first attempt with Triggers) is the following:

delimiter //
    CREATE TRIGGER calcEGFR AFTER INSERT ON Table2
    FOR EACH ROW
    BEGIN
    	SELECT age_at_visit, sex FROM Table1 WHERE (Table1.patient_wpid = Table2.patient_wpid AND Table1.patient_id = Table2.patient_id)
    	SET @creatinine_power := SELECT POWER(creatinine,-1.154);
        SET @age_power := SELECT POWER(Table1.age_at_visit,-0.203);
        IF Table1.sex = 'female' THEN
        	SET @sex_addition := 0.742
        END IF;
        IF Table1.sex = 'male' THEN
        	SET @sex_addition := 1
        END IF;	
    
        SET @eGFR_value := (175 * @creatinine_power * @age_power * @sex_addition)
        UPDATE Table2 SET eGFR = @eGFR_value;
    END;//
    delimiter ;

Can you please help me correct this?
Thanks!

[r937]

not sure i can help with the trigger code, but do you have any control over the design of these tables?

because if they were only one table, you wouldn’t need a trigger

also, why store a value that you can calculate on the fly in any SELECT statement?