-
March 27th, 2020, 06:42 PM
#1
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 and values from Table 1, for the given and (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 and in Table2 and calculate a formula, in order to produce, and consecutively store, a value for the 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!
-
March 27th, 2020, 07:08 PM
#2
Re: Help constructing a Mysql trigger
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.
-
October 29th, 2020, 06:23 AM
#3
Re: Help constructing a Mysql trigger
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|