Trigger a change in one table to change another table

kml2katz

I need the following to happen: (I am using mySQL as the database.)

When a record gets added or updated in a certain table with a certain value in a certain column, it "captures" the value of another column in the record that it just added or updated.

It will then change a value in another column that is in another table. It will use the value from the previous table to update a record.

When the last update to the other table happens, it will automatically submit an email from a webpage that I have built using PHP and Dreamweaver.

I have this all set up to do manually, but my client wants this to happen automatically. Can someone help me out?

dagon

why not edit the script that does the adding or updating, it can then do the rest (function\include) when the particular value comes up

kml2katz

I didn't write the script that updates the database. That came from PayPal. This post could almost go into the Newbie forum, but I thought that I could get more answers in forum.

So I still need to know how to code this, but thanks for replying.

dagon

feel free to ask a specific question with some code if appropriate.

Sxooter

What you are looking for are triggers. PostgreSQL and later versions of MySQL support triggers.

dagon

a db trigger can't call an external script, which is what he asked for.

Sxooter

dagon;10906591 wrote:
a db trigger can't call an external script, which is what he asked for.

It certainly can in my database.

dagon

Sxooter;10906594 wrote:
It certainly can in my database.

cool, how?

Sxooter

PostgreSQL supports a plethora of pl languages, like plPerl, plJ, plRuby, plR, plsh (call shell commands from pg) and plPHP, so you can even write your triggers in the same language you write your business logic level stuff.

So sending an email from plPHP is really about the same as sending an email from php, you just call the mail() function and off it goes.

dagon

the original poster specificity said mySQL, oh well nice try.

Sxooter

Yeah, sadly, his SUBJECT line specifically said updating one table when a first is updated. THAT you do with triggers. The rest of what he wants to do he is constrained by his choice of software to using an external program to make it work.

At least that's how I see it. At least my answer helps him solve his problem somewhat. But I guess you're more interested in "winning" as opposed to solving his problem.

dagon

Sxooter;10906693 wrote:
At least that's how I see it. At least my answer helps him solve his problem somewhat. But I guess you're more interested in "winning" as opposed to solving his problem.

Winning? There are prizes? Can i get a red tricycle?

I don't know why you felt the need to denigrate me, but have a nice life. I wont be posting in this thread again.

Sxooter

dagon;10906371 wrote:
why not edit the script that does the adding or updating, it can then do the rest (function\include) when the particular value comes up

The reason you don't put all your db logic into the application, is that you may have > 1 code path leading to putting that data into the database, and it is usually better to ensure data integrity with triggers and foreign key relationships where the db makes sure you can't put messed up data into it. Also, it's possible for future developers to NOT know why the code works the way it does and make changes that cause subtle data errors that you don't find until months or years have gone by and your data is a garbled mess.

If every time a row is added to table a a row in table b either needs to be updated or inserted, it's far better to handle it in the db where you can be sure either everything goes in, or nothing goes in and you get an application error.

Sxooter

dagon;10906695 wrote:
Winning? There are prizes? Can i get a red tricycle?

I don't know why you felt the need to denigrate me, but have a nice life. I wont be posting in this thread again.

Yeah, your "nice try" post was in no way denigrating.

Sxooter

dagon;10906684 wrote:
the original poster specificity said mySQL, oh well nice try.

Oh, and MySQL can call external programs with triggers as well. How well it works and how well it's been tested, I have no idea. udf calls external program

Sxooter

But the usual way of solving this problem is to set up a mail queue, and then cron it up or make it a daemon to process it. That way you're not relying on your database sending emails. Having an already heavily taxed database server send emails is probably not the best use of resources for most people.

Make a table that has all the things to be sent. Write a trigger that puts entries in for each email the user "sends". Have your cron job pick it up every minute or two and email it for you, on another machine. Lock the records and have the script check to see if it's already running and terminate.

If there is a problem sending emails it shouldn't DOS the database server with filled hard drives, stalled processes, etc. If you really wanna have the database trigger the email, then have an internal server that answers to your database and send your mail in queued mode. Then they go sit on another server that happily takes email from the db server and sits it in a queue.

So, what are the requirements here in terms of timeliness and load and numbers? If you need to send a million copies of a newsletter at night, off peak hours, you use the multiple machines sending emails with a list from the db. Add machines until connection is saturated for more performance.

If you need to send more timely emails, like registration emails, then you probably do it some other way. I'd have and internal email server and the app send the email myself. Triggered table would be nice for logging what we sent or couldn't send. Here even cascading updatable fks would work fine. Since those are implemented as triggers in postgresql, I tend to think trigger whether it's a FK-PK relationship, which is way easier to implement for most people than writing a trigger. So, if you can make with work with a cascading FK relationship I'd do that too.

So, in a nutshell, OP, what are you trying to accomplish?