获取触发器以仅在审计表中插入更改的列值
我正在使用触发器来存储审核表中的更改，我只想存储已更改的列中的值。
BEGIN
IF NEW.history_of_repair_trigger_fired = 1 THEN
INSERT INTO history_of_repair SET
edit_date_time=NEW.last_edited_date_time,
edited_by=NEW.edited_by,
repair_id=NEW.repair_id,
tenant_name=NEW.tenant_name,
property_id=NEW.property_id,
priority=NEW.priority,
comments=NEW.comments,
signed_off=NEW.signed_off;
END IF;
END
目前，这也将未更改的值存储在审计表中，这是不可取的。
仅将更改的列存储在审核表中的最佳方法是什么？
I'm using a trigger to store changes in an audit table, I only want to store the values from columns that have been changed.
BEGIN
IF NEW.history_of_repair_trigger_fired = 1 THEN
INSERT INTO history_of_repair SET
edit_date_time=NEW.last_edited_date_time,
edited_by=NEW.edited_by,
repair_id=NEW.repair_id,
tenant_name=NEW.tenant_name,
property_id=NEW.property_id,
priority=NEW.priority,
comments=NEW.comments,
signed_off=NEW.signed_off;
END IF;
END
At the moment this is storing the unchanged values in the audit table too, which is not desirable.
What's the best way of only storing the changed columns in my audit table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据您的评论，我不会担心仅存储更改的值 - 磁盘空间很便宜。 存储实际值的优点是，如果您需要紧急，可以轻松地将记录恢复到某个时间点。 如果您需要生成人类可读的审核记录，请将其存储在 varchar/nvarchar 中，并通过将更改累积到字符串中来构造触发器中的消息。 除了实际值之外，我还可以存储它。 请注意，您还可以提供一个表值函数，它动态地为您构造这个人类可读的列，而不是存储它。
Based on your comments, I wouldn't be concerned about storing only the changed values -- disk space is cheap. Storing the actual values has the advantage that it makes it trivial to restore a record to a point in time if you need to in a hurry. If you need to produce a human-readable audit record, then store it in a varchar/nvarchar and construct the message in your trigger by accumulating the changes into a string. I might store this in addition to the actual values. Note that you could also provide a table-valued function which dynamically constructs this human readable column for you rather than storing it.