bioplanet 0 Newbie Poster

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:

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)

  1. 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.