有什么方法可以在MySQL中创建新表时自动创建触发器? [英] Is there any way to automatically create a trigger on creation of new table in MySQL?
问题描述
有什么方法可以在MySQL中创建新表时自动创建触发器吗?
Is there any way to automatically create a trigger on creation of new table in MySQL?
推荐答案
As I've pointed out in your other question, I think a process and security review is in order here. It's an audited database, so nobody (especially third-party service providers) should be creating tables in your database without your knowledge.
您遇到的问题是，以及正在创建的新表，还需要创建另一个表来存储审核/更改的记录，该表的结构与原始表相同，并且可能包含一个时间/日期和用户列.如果第三方提供者正在创建此表，则他们将不知道要创建审核表，因此，即使您可以动态生成触发器，它们也将无法工作.
The issue you've got is, as well as the new table being created, you will also need to have another table created to store the audited/changed records, which will have an identical structure as the original table with possibly a time/date and user column. If a third-party provider is creating this table, they won't know to create the auditing table, therefore even if you could generate your triggers dynamically, they wouldn't work.
创建单个表来保存数据库中所有其他表的所有更改记录是不可能的，因为表之间的结构不可避免地会有所不同.
It's impossible to create a single table that will hold all changes record for all other tables in your database because the structure between tables inevitably differs.
因此:向您自己和/或您的团队提出所有变更请求(例如，提供者要创建TableX，他们提交解释变更原因的变更请求(包括SQL脚本)).
Therefore: make all change requests (e.g. providers wants to create TableX, they submit a change request (including the SQL script) explaining the reason for the change) to yourself and/or your team.
您在数据库的测试副本上执行SQL，并使用相同的结构创建另一个表来保存修改后的记录.
You execute the SQL on a test copy of your database, and use the same structure to create another table to hold the modified records.
然后，您创建并测试必要的触发器，生成新的SQL脚本以创建两个表和触发器，然后在实时数据库上执行它们.您授予提供商使用新表的权限，然后又将它们移走.
You then create and test the necessary triggers, generate a new SQL script to create the two tables and your triggers and execute that on your live database. You give your provider permissions to use the new table and away they go.
每个人都很高兴.是的，它可能需要更长的时间，是的，您还有更多的工作要做，但这比尝试解析查询日志以重新创建已经更改的记录所需的工作量少得多.删除或解析二进制日志，并保持每次更改的最新状态，并在日志文件的格式更改等时修改您的代码.
Everyone's happy. Yes, it may take a little while longer, and yes you'll have more work to do, but that's a hell of a lot less work than is required to try and parse query logs to re-create records that have already been changed/deleted, or parse the binary log and keep up-to-date with every change, and modify your code when the format of the log file changes etc etc.
这篇关于有什么方法可以在MySQL中创建新表时自动创建触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
