I have a number of tables in a MySQL database and my xojo application needs to be aware of all the data-changes in these tables, made by several other applications.
So per table I created 3 triggers, trgTablename_UPDATE, trg_Tablename_ALTER and trg_Tablename_DELETE.
Each of these triggers launches an the same INSERT query in order to add a record describing the changes in just one table called tblCHANGELOG. My application keeps an eye on this table, fetches the changed records only by tablename and unique id. Once I am aware of the data beeing changed I can run my calculation (road distances) on it.
Although this works , it’s a devious method and an huge script creating all the triggers.
So I am looking for a smarter solution. Anybody who can hint me ?
I was looking at exactly this for a while but have not decided on my final solution yet.
What you may be looking for is database notification; this available in Oracle, MS SQL Server, PostgreSQL (Not sure if there are any others), but as I understand it, it is not in MySQL. Essentially it sends a message on a pre-defined communication channel whenever a change happens that you are interested in.
There are a few options depending how quickly you need to be notified and act on those changes:
You could use a database as above that provides this functionality.
You could add a trigger that subsequently adds a row to another table, which you can then periodically check, removing said row once it has been acted upon.
You could use a TCP/UDP/IPC listening process.
Given what Xojo offers in this repsect, I personally favour option 3 since you could make it very flexible. The only possible downside is that everything could then become reliant upon a single process.
I have in the past created a table full of table names (or a query to the database which lists tables following a pattern)
Then used the tablename as a field in a new SQL select statement which generates SQL statements.
Something like
Select 'CREATE TRIGGER update_customer_address UPDATE OF address ON ' + [tablename] + ' BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;'
from list_of_tables;
Take the output as text and execute the whole lot in one go?
I did read that it is possible to add a trigger to a view, in which case you might create a union select of all the tables as a view, and create one trigger on that, but I dont follow the method myself.
Hi Jeff, having the script in place to create the whole bunch of triggers is not the issue, but I don’t want to see a database having so many objects, and next I expect the database engine to get slow with so may triggers. (each trigger can just be related to just one table)
Slowness is going to depend upon transaction count.
If you get one transaction per second, it shouldn’t be an issue.
(Only one trigger fires per table per transaction, after all)
How often does the mileage value have to be correct?
Is it worth doing a full calculation overnight and allowing the data to be out of date for hours at a time?
If so, dont do triggers, just mop up now and then.
No, this is just not acceptable. I need to see things moving quite real time. I am not the public transportation company who “mops up” data every one ore two days and let customers wait to see their expenses or statusupdates.
Yes @Norman Palardy , I know I’d better move to PostgreSQL for many reasons. But the fact is here that I have to deal with the customers’ existing MySQL database. The only thing I can and may do is adding objects to this existing database which their applications will not be aware of, like triggers and some extra tables.
MySQL has no means to send a notification to a client program like PostgreSQL
So you are limited
constantly scan tables watching for changes which will induce other performance related issues as more & more clients do this
You’ll clobber the db just looking for changes
What about writing a listener in a Xojo console app and having it run on the server, then send a message to the clients as and when required. It would reduce the load on the clients having to keep checking in, and so long as there are not too many tables to monitor, it shouldnt overload the server.
my triggers inserts into a small additional table (tablename, changetype and record_id)
my application checks this table every second, which is very fast since records that have been processed are wiped out.
For the time being it works, although it would be nicer if I could activate my application directly by the trigger itself.
I think what you are doing right now is already the smartest way possible. It’s called “busy waiting” (asking repeatedly “did something happen?”), which is the only way to implement your feature as MySQL has no way to notify clients of changes.
