Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

CREATE TRIGGER trigger1
AFTER UPDATE
ON tbl1
FOR EACH ROW
BEGIN
-- I'm interested in two fields only and if they were changed only
IF new.y != old.y THEN
SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
END IF;
IF new.country != old.country THEN
SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
END IF;
if @y != '' or @country != '' THEN
set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
prepare stmt1 from @my_sql;
EXECUTE stmt1;
end if;
END

But I got error "dynamic sql is not allowed in stored function or trigger".
I can avoid 'execute' by using a lot separated inserts: one for field 'y', one for field 'country' and ~10 inserts more for other fields but there will be performance penalty.

1 Answer
1

CREATE TRIGGER trigger1
AFTER UPDATE
ON tbl1
FOR EACH ROW
BEGIN
DECLARE insert_style INT;
-- I'm interested in two fields only and if they were changed only
SET insert_style = 0;
IF new.y != old.y THEN
SET insert_style = 1;
END IF;
IF new.country != old.country THEN
SET insert_style = insert_style + 2;
END IF;
CASE insert_style
WHEN 1 THEN
insert into z_lot values
('','tbl1',new.id,'y',old.y,new.y);
WHEN 2 THEN
insert into z_lot values
('','tbl1',new.id,'country',old.country,new.country);
WHEN 3 THEN
insert into z_lot values
('','tbl1',new.id,'y',old.y,new.y),
('','tbl1',new.id,'country',old.country,new.country);
END CASE;
END