MYSQL multiple Triggers

Monday, July 7, 2014

I have a MYSQL table and two of the fields are called Rate_per_unit and Cost. First I want the field Rate_per_unit to populate itself from another table called SHD_TEACHER then I want the field COST to populate itself also from RATE in SHD_TEACHER and multiplies by UNITS.


I have the following code which is giving me an error:



CREATE TRIGGER RATE_PER_UNIT_1
BEFORE INSERT ON SHD_SCHEDULE
FOR EACH ROW
SET NEW.RATE_PER_UNIT =
(
SELECT RATE
FROM SHD_TEACHER
WHERE TEACHERID = NEW.TEACHER_ID
LIMIT 1
)

SET NEW.COST = (
SELECT RATE
FROM SHD_TEACHER
WHERE TEACHERID = NEW.TEACHER_ID

) * UNITS


Any help please?


thanks







http://ift.tt/1pUlFJG