question about MySQL triggers

ringo747 · 17 Apr 2007 at 02:05

Hi,

I am currently trying to implement some RSS feed creation stuff which is causing me some problems so I have decided to rethink my plans and have came up with the following idea.

I want to fish data out of my MySQL database and dump it into an XML (RSS) file. This in itself is probably easy enough and I have got some help on here with that before. However, I will need some way of ensuring that the RSS feeds remain in sync with the database. Is it possible therefore to run a certain PHP script to dump the database into an XML file ONLY when an update or insert is performed on a table within the database? I have looked at triggers before using JDBC but not sure how MySQL works with them.

If anyone understands this and has an answer I'd appreciate it.

Cheers

growse · 17 Apr 2007 at 09:55

From what I can see:

26.5.10: Can triggers call an external application through a UDF?

No, not at present.

Taken from the MySQL docs. If you're doing what I think you're doing, then you probably just want it as a 5 minute cronjob....? Build the file every 5 minutes rather than every time the database changes.

ringo747 · 17 Apr 2007 at 10:34

yeah I had thought of cronjobs and suggested that to my project supervisor but he seemed to not like the idea as it was 'wastage' due to to the constant dumping of the database even though no content had changed. I cannot see anyother workaround so cronjob is probably the best option. Now to work out how to do that!

growse · 17 Apr 2007 at 11:25

jonnyc747 said:
yeah I had thought of cronjobs and suggested that to my project supervisor but he seemed to not like the idea as it was 'wastage' due to to the constant dumping of the database even though no content had changed. I cannot see anyother workaround so cronjob is probably the best option. Now to work out how to do that!

Get the cron script to look at the latest insert into the table and compare it to what it found before. If they're the same, it doesn't need to do anything

ringo747 · 17 Apr 2007 at 13:16

growse said:
Get the cron script to look at the latest insert into the table and compare it to what it found before. If they're the same, it doesn't need to do anything

ah ok that sounds good idea! I might come back to you for some help on that. Appreciate the help.

By the way I will only be using this locally so is it possible to run cron on my MacBook Pro or does it only work on an actual server? I'm using MAMP as my local web server thing.

growse · 17 Apr 2007 at 13:21

jonnyc747 said:
ah ok that sounds good idea! I might come back to you for some help on that. Appreciate the help.

By the way I will only be using this locally so is it possible to run cron on my MacBook Pro or does it only work on an actual server? I'm using MAMP as my local web server thing.

I'd imagine OSX will have some sort of task scheduling thing in it. This may just be some fancy interface to cron or it might be something else entirely. No idea though...

pinkaardvark · 17 Apr 2007 at 15:34

You could write the functionality into the data manipulation stored procs. But this would impact the time to return if you are returning anything from them.