CODEWITHSUNDEEP
mysqltriggers
Justin808
Justin808

Reputation: 21522

How do I get a result set in a MySQL trigger

On BEFORE INSERT I'm trying to check the values. If a column is blank, I want to load in the default value - based on a row in a separate table. I though something like my code below would do what I needed but I'm getting an error.

Not allowed to return a result set from a trigger Execution stopped!

This is my SQL, I assume its complaining about my select statement.

delimiter |
CREATE TRIGGER `trg_Sites_ID` BEFORE INSERT ON Sites
FOR EACH ROW
BEGIN
  SELECT @CA:=Palette0ColorA,  @CB:=Palette0ColorB FROM Themes WHERE ID=NEW.ThemeID;
  SET NEW.ID = UUID();
  SET NEW.ColorA = IF(NEW.ColorA = '', @CA, NEW.ColorA);
  SET NEW.ColorB = IF(NEW.ColorB = '', @CB, NEW.ColorB);
END
|
delimiter ;

Upvotes: 1

Views: 1541

Answers (1)

eggyal
eggyal

Reputation: 125955

Your SELECT is, as the error message implies, attempting to return data to the client. You could instead use SELECT ... INTO:

SELECT Palette0ColorA, Palette0ColorB INTO @CA, @CB FROM Themes WHERE ID=NEW.ThemeID

Upvotes: 1

Related Questions