the trigger with "if-elseif" does not update the table-CodePudding

so, made a trigger for "picks" table strong text

    delimiter //
create trigger picks_description after insert on picks
for each row
begin
if (new.Picks_Thickness between 0.5 and 0.74) then
insert into Picks (Picks_Desc) values ('медиатор для быстрой игры/ведущей гитары');
elseif (new.Picks_Thickness between 0.87 and 3) then
insert into Picks (Picks_Desc) values ('медиатор для тяжелой игры/ритм-гитары');
end if;
end //

and basically after i try to insert new info, it doesnt update the column Picks_desc

CodePudding user response：

You need a before insert trigger and a SET syntax

DELIMITER //
CREATE TRIGGER picks_description BEFORE INSERT ON picks
FOR EACH ROW
BEGIN
IF (new.Picks_Thickness between 0.5 and 0.74) THEN
    SET NEW.Picks_Desc = 'медиатор для быстрой игры/ведущей гитары';
ELSEIF (new.Picks_Thickness between 0.87 and 3) then
    SET NEw.Picks_Desc = 'медиатор для тяжелой игры/ритм-гитары';
END IF;
END //

CodePudding user response：

You can't use INSERT/UPDATE/DELETE in a trigger for the same table that spawned the trigger, because it may result in an infinite loop.

If I test your trigger:

mysql> insert into picks set picks_thickness = '0.6';
ERROR 1442 (HY000): Can't update table 'picks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I am guessing what you really want is not to INSERT a new row, but to change the value of the current row which is spawning the trigger. For that you must change the value before the INSERT, and do it with a SET statement.

create trigger picks_description BEFORE insert on picks
for each row
begin
 if (new.Picks_Thickness between 0.5 and 0.74) then
  SET new.Picks_Desc = 'медиатор для быстрой игры/ведущей гитары';
 elseif (new.Picks_Thickness between 0.87 and 3) then
  SET new.Picks_Desc = 'медиатор для тяжелой игры/ритм-гитары';
 end if;
end

CodePudding user response：

CREATE TRIGGER picks_description 
BEFORE INSERT ON picks
FOR EACH ROW
SET NEW.Picks_Desc = CASE WHEN new.Picks_Thickness between 0.5 and 0.74
                          THEN 'медиатор для быстрой игры/ведущей гитары'
                          WHEN new.Picks_Thickness between 0.87 and 3
                          THEN 'медиатор для тяжелой игры/ритм-гитары'
                          ELSE NEW.Picks_Desc
                          END;

Neither BEGIN-END nor DELIMITER needed.