CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Help constructing a Mysql trigger

  1. #1
    Join Date
    Aug 2019
    Posts
    2

    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!

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,315

    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.

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)