Automatic update on a table, depending on values from different tables
I would like to set an automatic update on a table that will update/add/delete rows in different database tables with the same table structure depending on a value in one of the columns and other tables.
The idea is to maintain the core data only on one place (DB1) and all changes to be automatically populated across the relevant database tables (DB2 and DB3).
Prefer not to use Views, since p_cross table will have millions of rows.
For example we have the following scenario:
========= DB1: ========= project -------------------------------- | id | project| db | table | -------------------------------- | 1 | A | DB2 | p | | 2 | B | DB3 | p | -------------------------------- p ------------------------ | id | name | comment | ------------------------ | 1 | first | | | 2 | second| | | 3 | third | | | 4 | fourth| | ------------------------ p_cross --------------------- | id_project | id_p | --------------------- | 1 | 2 | | 1 | 4 | | 2 | 1 | | 2 | 3 | | 2 | 2 | --------------------- ========= DB2: ========= p ------------------------ | id | name | comment | ------------------------ | 2 | second| | | 4 | fourth| | ------------------------ ========= DB3: ========= p ------------------------ | id | name | comment | ------------------------ | 1 | first | | | 2 | second| | | 3 | third | | ------------------------
So I would like to set an automatic update on db1.p , that will lookup in db1.p_cross and db1.project which databases and tables to update.
For example if I update "second" to "second_upd" in db1.p , it will lookup in db1.p_cross which projects are relevant to this record, will lookup db1.project table the corresponding id_project and will see which database and table to update - in our case db3.p
And clues, hints or ideas?
链接地址: http://www.djcxy.com/p/94230.html下一篇: 根据不同表格中的值自动更新表格