是否有一个MySQL“多才多艺”触发器

是否有一个MySQL“多才多艺”触发器 - EG是一个触发器，只有在所有表完成更新后才会触发？(Is there a mysql “multitable” trigger - E.G. a trigger that fires only after ALL tables are done updating?)

几个表有一个触发器，用于生成更新/插入行上的json对象表示。 EG {"email": ..., "relations: N" } //<--an email.json column并将其存储在json列中

关系只是一个数字的联系（让我知道是否有一个词），它使我可以将多个姓名，电子邮件，电话，家庭联系到一个对象中 -

EG the touchRelation.json column

{ "emails": [ {"email": 1@a.com },{"email: 2@a.com"},{"email: N@a.com"}], "teles" : [ {"tele" : ... },{"tele : ...."},{"tele : ...."}], "Names" : [ {"Name" : ... },{"Name : ...."},{"Name : ...."}], "Homes" : [ {"Home" : ... },{"Home : ...."},{"Home : ...."}], }

我遇到的问题是1）更新touchRelations.json EVERY TIME其他表中的其中一个表会获取数据CRUD会浪费且效率低下，特别是如果一次更新多个表

2）我可能无法依赖开发人员在每次查询后调用update_Relations_json（）。

有没有一种简单的方法可以判断一个或多个表是否已更新，并且在所有表的所有更新都完成后只重新生成relations.json？

---

一种可能的解决方案是创建一个“挂起更新”表，将信息存储在一个队列中，并逐个插入/更新队列表中的数据到存储表中，然后调用更新函数，但我确定这不是不是最好的选择。

另一个选择是在db中创建一个JSON解析器，该解析器读取完整的json关系（上面的大关系），更新表然后构建json对象，但这似乎是数据库使用不当。

several tables have a trigger that generates a json object representations of the row on update/insert. E.G. {"email": ..., "relations: N" } //<--an email.json column and stores it in a json column

relations is simply a numeric tie-together (let me know if there is a word for it) that allows me to tie together multiple names, emails, phones, homes into one object -

E.G. the touchRelation.json column

{ "emails": [ {"email": 1@a.com },{"email: 2@a.com"},{"email: N@a.com"}], "teles" : [ {"tele" : ... },{"tele : ...."},{"tele : ...."}], "Names" : [ {"Name" : ... },{"Name : ...."},{"Name : ...."}], "Homes" : [ {"Home" : ... },{"Home : ...."},{"Home : ...."}], }

The problem I'm having is that 1) it would be wasteful and inefficient to update touchRelations.json EVERY TIME one of the other tables gets data CRUD, especially if several tables are updated at one time

2) I may not be able to rely on the developer to call an update_Relations_json() after each query.

Is there a simple way tell if one or more of the tables have been updated and ONLY regenerate relations.json after all updates on all tables have finished?

---

One Possible Solution would be to create a "pending Updates" table that stores the information in a queue and one by one inserts/updates the data from the queue table to the storage table then calls the update function, but I'm sure this isn't the best option.

Another option would be to create a JSON parser in the db that reads the complete json relation (the big one from above), updates the tables then builds the json object, but that seems like a poor use of the database.

最满意答案

我能想到的最佳选择是创建默认值为0的“更新”元数据列。当我们更新手机，电子邮件，姓名或家庭时，元数据列将更改为1（代表更新没有提交给关系JSON列）

接下来创建一个存储过程“request_relations_json（）”，用于检查未决的提交（“更新”元列中的1）。 如果没有更新，则将当前的relations.json列返回给应用程序。 如果有更新，重新生成json然后将其返回给应用程序。

这是hackish，但它不会在每次更新时生成json。 我仍然希望有一个更优雅的解决方案。

The BEST option I can think of would be to create an "updates" meta-data column with a default of 0. When we update the phone, email, name, or home the meta-data column is changed to 1 (representing that an update was not committed to the relations JSON column)

Next create a stored procedure "request_relations_json()" that checks for pending commits (a 1 in the "updates" meta-column). IF there are no updates, return the current relations.json column to the application. If there are updates, regenerate the json then return it to the application.

It's hackish, but it isn't generating json on every update. I still hope there's a more elegant solutions out there.