how to use trigger to set value based on query result would appreciate any example on how to set value on the added row based on query results from different table.

something like this

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    NEW.value = (select value from table2 where id=NEW.id)
    NEW.value2 = (select value2 from table2 where id=NEW.id)
  END;
|

delimiter ;
That looks like it should work. I would run one SELECT to retrieve both values

SUGGESTION #1

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    select value,value2
    INTO NEW.value,NEW.value2
    from table2 where id=NEW.id;
  END;
|

delimiter ;


SUGGESTION #2

If the value and value2 are of datatype INT, do the following

delimiter |

CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    DECLARE v1,v2 INT;

    select value,value2 INTO v1,v2
    from table2 where id=NEW.id;

    NEW.value  = v1;
    NEW.value2 = v2;
  END;
|

delimiter ;


or do DECLARE v1,v2 VARCHAR(45); or whatever datatype is appropriate