MYSQL Triggers

[Steve Warburton]

We have a server application (written by a third party that we cannot change). This listens to a camera and recives an image / status change and populates a MYSQL table with the image / status changes.

I need to action some PHP code when an image arrives or a status changes.

I know I can do a query every 5 secs and extract any changes. I am worried this will produce a big overhead as the tables have 1000,000' of rows.

My idea was to use a 'trigger' on a row change and place this change into another table (TEMP_1).

I would then poll TEMP_1 action the PHP then remove the data to STORED_1.

This would keep the size of TEMP_1 down as a row is deleted after it is processed.

If the above would work what is the syntax of the trigger to do:

SELECT ('COLUMN CHANGED' & 'ROW_ID') from MAIN_TABLE TO TEMP_1

So TEMP_1 would have

STATUS_CHANGE ROW_ID
NEWIMAGE 567

The PHP code would then poll the above and send a message to user 567 via SMS that a new image has arrived.

Or is there another approach to this.