The task: write a trigger to log all updates within
tbl1 table for future audit users' actions.
id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some of them have to be monitored
I'm going to store both old & new values, one row for each field that was changed. The place for log (simplified for this example):
CREATE TABLE z_log( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, table_name CHAR(20) NOT NULL, row_id INT(11) UNSIGNED NOT NULL, field CHAR(20) NOT NULL, old_val VARCHAR(255) NOT NULL DEFAULT '', new_val VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (id) )
The trigger I have created:
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.
Is there any other fast way to log changes only? Thank you.