MySQL triggers

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 ?

3 triggers per table, and you have already added them?

Which part do you want to change at this stage?
(Are you envisaging dozens of new tables arriving soon?)

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:

  1. You could use a database as above that provides this functionality.
  2. 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.
  3. 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.

No no, I am still looking for a smarter solution.

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 =; 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)

Using a Union Select means that you need every table redundant and run timer driven and performance consuming queries frequently.

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.

Quite honestly - PostgreSQL and its notifications are what you’re looking for
Way simpler
mySQL doesn’t have this or the very clear licensing :slight_smile:

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

  1. 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

  2. maybe invoke an external; program from a mysql trigger that sends notifications about changes
    But this means you have a mysql connection + some other listener in your app

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.

The triggers seem like on reasonable solution. Have you looked into reading the transaction log?

Most databases have logs like this that are used for a variety of purposes. Sometimes third party tools can parse and read them.

What I do now is the following:

  1. my triggers inserts into a small additional table (tablename, changetype and record_id)
  2. 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.

Are the calculations you have in the app too complex to be done in the SQL 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.

@Craig Boyd 's suggestion of tapping into the database replication strategy for mySQL is probably the ideal solution.

I must say though that even with local servers this mechanism is fraught with issues.

This article might be of some help:

Yes, I need api from map-supplier to calculate route which can take up to more than one second. So there is also need for asynchronous process.