如何加快審計觸發？

任務：編寫一個觸發器來記錄tbl1表中的所有更新，以供將來審計使用者的操作。

表tbl1：

id INT(11) 
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

我將儲存舊值和新值，每個更改的欄位一行。日誌的位置（在此範例中進行了簡化）：

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)
)

我創建的觸發器：

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

但我收到錯誤“儲存函式或觸發器中不允許使用動態 sql”。我可以通過使用大量分隔的插入來避免“執行”：一個用於欄位“y”，一個用於欄位“國家”，大約 10 個用於其他欄位，但會降低性能。

有沒有其他快速的方法來只記錄更改？謝謝你。

您可以使用蠻力 INSERT 更改觸發器，如下所示：

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 

引用自：https://dba.stackexchange.com/questions/16175