在MySQL触发器中使用IF语句插入多行

I have a trigger that stores changes made in a separate table when a user edits the data. This data is written out on a web page beneath the current data in human readable format, i.e.

我有一个触发器,用于存储用户编辑数据时在单独的表中所做的更改。该数据以人类可读格式写在当前数据下面的网页上,即

23/04/09 22:47 James Smith changed Tenant Name from "George Hill" to "George Hilling".

23/04/09 22:47詹姆斯史密斯将租客姓名从“乔治希尔”更改为“乔治希林”。

The trigger I have looks a bit like this - (this is the abridged version).

我看起来有点像这样的触发器 - (这是删节版本)。

Two questions:

A) Is this quite costly performance-wise and if so is there a better approach to take? B) Is there a tidier way to do this without all the IFs, using some sort of loop perhaps?

A)这在性能方面是否相当昂贵,如果有,是否有更好的方法? B)如果没有所有的IF,使用某种循环可能有更简洁的方法吗?

DELIMITER //
CREATE TRIGGER repair_history AFTER UPDATE ON repairs
FOR EACH ROW
BEGIN
INSERT INTO repair_edit SET repair_id=NEW.repair_id,
edit_date_time=NEW.edit_date_time, edited_by=NEW.edited_by;

IF OLD.tenant_name != NEW.tenant_name THEN
INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='tenant_name',
former_field_value=OLD.tenant_name, new_field_value=NEW.tenant_name;
END IF;

IF OLD.priority != NEW.priority THEN
INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='priority',
former_field_value=OLD.priority, new_field_value=NEW.priority;
END IF;

IF OLD.property_id != NEW.property_id THEN
INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='property_id',
former_field_value=OLD.property_id, new_field_value=NEW.property_id;
END IF;

IF OLD.type_id != NEW.type_id THEN
INSERT INTO repair_history SET edit_id=LAST_INSERT_ID(), field='type_id',
former_field_value=OLD.type_id, new_field_value=NEW.type_id;
END IF;

END; //
DELIMITER ;

2 个解决方案

#1

A) Is this quite costly performance-wise

A)这在性能方面是否相当昂贵

Profile it. Only you know what hardware you're using, and what volume of updates your database has to handle.

简介它。只有您知道您正在使用的硬件,以及您的数据库必须处理的更新量。

and if so is there a better approach to take?

如果是这样,有更好的方法吗?

Consider what's taking the mosst time in your trigger. Is it the comparisons, or the inserts?

考虑一下你的触发器中最重要的时间。是比较还是插入?

B) Is there a tidier way to do this without all the IFs, using some sort of loop perhaps?

B)如果没有所有的IF,使用某种循环可能有更简洁的方法吗?

Unfortunately, I don't know of a way to index into the columns of pseudo tables NEW and OLD

不幸的是,我不知道如何索引伪表NEW和OLD的列

#2

Why not put a trigger on each column that can be updated and checked. This will have a performance hit though. What you may want to do is do a unit test where you have the trigger and don't have it, and see what the difference in time is when you are changing many rows quickly.

为什么不在每个可以更新和检查的列上设置触发器。不过,这会有性能提升。您可能想要做的是进行单元测试,在那里您有触发器而没有触发器,并查看快速更改多行时的时间差异。

http://forums.mysql.com/read.php?99,174963,175381#msg-175381

#1