Search code examples
mysqltriggersmamp

MySQL Trigger Error when trying to generate end_date from start_date + duration

Error Message

table

Hi

I am receiving this message when I try to use this trigger to calculate a end_date. The duration Integer is from another table. This is being done in MySQL on Mamp enviorment

Any help appreciated

code used DELIMITER //

CREATE TRIGGER m_duration_ins_tr1

BEFORE INSERT ON memberships FOR EACH ROW
BEGIN DECLARE duration INTEGER; SELECT duration FROM membership_type WHERE
membership_type_id = NEW.member_type; SET NEW.end_date := DATE_ADD(NEW.start_date, INTERVAL duration MONTH); END // DELIMITER ;

Solution

  • I think you just need to replace

    SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type;

    with

    SET duration = SELECT duration FROM membership_type WHERE membership_type_id = NEW.member_type LIMIT 1;

    The bare SELECT was doing nothing to assign to your local variable, but was indeed basically an attempt to return a result set from your trigger as the error suggested.