Help constructing a Mysql trigger

I have the following two tables:

TABLE 1:

Code:

---

    +-----------------------------+--------------------------------------+------+-----+---------+-------+
    | 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

Code:

---

    +-------------------------+----------------------------+------+-----+---------+----------------+
    | 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

Code:

---

age_at_visit

---

and

Code:

---

sex

---

values from Table 1, for the given

Code:

---

patient_id

---

and

Code:

---

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

Code:

---

weight

---

and

Code:

---

creatinine

---

in Table2 and calculate a formula, in order to produce, and consecutively store, a value for the

Code:

---

eGFR

---

column of Table2.
The formula is the following:

Code:

---

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:

Code:

---

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!

If you haven't already done so do a bing or Google search for "mysql create trigger after insert example".

From there I would break up the problem into small pieces, i.e., get the basic trigger to fire and insert into another table. Get this working first and add in more functionality until you have what you want.

Quote:

---

Originally Posted by Arjay

If you haven't already done so do a bing or Google search for "mysql create trigger after insert example".

From there I would break up the problem into small pieces, i.e., get the basic trigger to fire and insert into another table. Get this working first and add in more functionality until you have what you want.

---

same think happend to me