Using Triggers with FEDERATED Tables
In a recent blog entry I discussed some research I had done into the behavior of MySQL FEDERATED tables. Since then the question arose in the forums regarding the use of triggers with FEDERATED tables, so I performed some additional tests to see how triggers would behave.
The question was asked if triggers could be used with FEDERATED tables, and I verified that triggers could indeed be created on FEDERATED tables. With the assertion that the a trigger on a FEDERATED table did not actually fire, I set up a base table and an associated FEDERATED tables with triggers attached to both tables and verified they did in fact get called.
A 'before insert' trigger on a FEDERATED table gets called before the corresponding 'before insert' trigger on the base table, and this is rather what I would expect. If the FEDERATED table's trigger makes an alteration to the row, the altered version is what the base table's trigger is presented with.
An 'after insert' trigger on a FEDERATED table gets called after the corresponding 'after insert' trigger on the base, and this is again what I would expect. However, there is an anomaly here. If the base table's 'before insert' trigger made alterations to the row which subsequently were written to the table, the base table's 'after insert' trigger sees these changes, but the FEDERATED table's 'after insert' trigger does not. It only sees any alterations made by the FEDERATED table's trigger.
So I can summarize my testing of before and after insert triggers by concluding that information flows during an insert into a FEDERATED table from the server with the FEDERATED table to the server with the base table, but not vice versa.
The question was asked if triggers could be used with FEDERATED tables, and I verified that triggers could indeed be created on FEDERATED tables. With the assertion that the a trigger on a FEDERATED table did not actually fire, I set up a base table and an associated FEDERATED tables with triggers attached to both tables and verified they did in fact get called.
A 'before insert' trigger on a FEDERATED table gets called before the corresponding 'before insert' trigger on the base table, and this is rather what I would expect. If the FEDERATED table's trigger makes an alteration to the row, the altered version is what the base table's trigger is presented with.
An 'after insert' trigger on a FEDERATED table gets called after the corresponding 'after insert' trigger on the base, and this is again what I would expect. However, there is an anomaly here. If the base table's 'before insert' trigger made alterations to the row which subsequently were written to the table, the base table's 'after insert' trigger sees these changes, but the FEDERATED table's 'after insert' trigger does not. It only sees any alterations made by the FEDERATED table's trigger.
So I can summarize my testing of before and after insert triggers by concluding that information flows during an insert into a FEDERATED table from the server with the FEDERATED table to the server with the base table, but not vice versa.
posted by Bob Field at 12:02 PM
2 Comments:
We have a software package that currently only runs on MySQL4. For reporting purposes we have a federated copy of the MYSQL4 database running on MySQL 5.0.24.
I put an AFTER INSERT trigger on the 'jobs' table in MySQL5, to insert records into a reporting table called 'easylabel' (which isn't on the MySQL4 database).
The trigger works if you insert a job record while in MySQL5, but new jobs added in the MySQL4 database don't fire the trigger on the federated jobs table.
I assume that this is because the federated jobs records are 'fed through' from the MySQL4 jobs table, not actually 'inserted'.
Anyone?
p.s. solved the problem by creating a view
That is the basic behavior of triggers and FEDERATED tables that I have observed. The server where the table actually resides is not aware of any FEDERATED tables on other servers. Triggers on FEDERATED tables only get called when the FEDERATED table is accessed.
Post a Comment
<< Home