First Trigger

[scaiferw]

I'm new to triggers, but have been working with MySQL for quite a while. Now I need to implement a trigger.

I want to create a trigger on my 'persons' table so that after an update, if the attribute clubid is changed, attribute clubname should be changed.

I have a rough syntax below, which I think should work, but I'm not sure how to limit it updates where the clubid has changed.

Can anyone tell me how to limit it to running if the value clubid has changed, and also whether the syntax below is otherwise OK?

(I know this will fail as is, because the 'lastvisit' attribute in the person record is updated as soon as the visitor arrives on the site and I get "Can't update table 'persons' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.")

Thanks,

Rob

delimiter $$
CREATE TRIGGER tr_UpdateClubName
AFTER UPDATE ON persons FOR EACH ROW
BEGIN
UPDATE persons 
SET clubname = ( 
  SELECT clubname FROM clubs WHERE persons.clubid = clubs.clubno
);
END$$
delimiter ;

[pritaeas]

You can refer to the old and new column, please see the MySQL PDF on the topic.

[scaiferw]

Thank you Priteas. I tried that,

BEGIN
  IF NEW.clubid <> OLD.clubid THEN
    UPDATE persons 
      SET clubname = ( 
        SELECT clubname FROM clubs WHERE persons.clubid = clubs.clubno
      );
  END IF;
END

but I am still getting an error, "Can't update table 'persons' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

When the visitor arrives on the page, a previous bit of code updates the last_visited attribute in the persons table. I assume that is the cause of that error.

Do you have any ideas how to fix this?

[pritaeas]

Oh right, you can't update the record in an update trigger. It could cause an infinite loop if allowed. Why do you need the club name to change, if the id already points to the clubs table ? You wouldn't need the club name in the persons table.

[scaiferw]

Having the clubid and clubname in the same table is not 100% normalized, but it is referred to often enough that I decided that it would complicate matters too much to have to do a lookup each time - that attribute is referenced quite often. The lesser of the evils, as it were.

Can you see how I can make this happen?

[scaiferw]

By the way, Pritaeas, I don't fully understand your note that "you can't update the record in an update trigger". What are the restrictions on what records can be updated?

Is it never possible to update in a trigger the table whose change initiated the trigger?

Like I said, triggers are new ground for me so I'm a little out of my depth here.

(Starting to wonder if I need to be calling a stored procedure from the trigger. Stored procedures are another thing that are new to me.)

Greatly appreciate your assistance, as does the service club for which I am doing this as a volunteer effort.

[pritaeas]

Is it never possible to update in a trigger the table whose change initiated the trigger?

It's not possible to update the record which triggered the update trigger. You can update other records.

Anyway, another option (if you haven't already) is to enable query caching. So, if your join query (in normalized form) is already cached, and nothing has changed, it will return the results nearly instantly.

[scaiferw]

Dang. That makes sense.

Would a stored proc initiated by a trigger help me here? Someone set that up for me on a MSSQL Server project, I'm looking at that code now. Perhaps that will get me around this.

If I can't make this work I may set up a cron job to update this a couple of times a day (about 2,300 records to update) until I have time to go through everything and replace references to persons.clubname, but that's messy and I'd rather not.

[pritaeas]

Would a stored proc initiated by a trigger help me here?

No. You would still not be able to update the same record, as it is triggered by the update trigger (it won't work if the code you call is nested within the trigger).

A cron job is an option, but if you are using the right MySQL version, have a look at the event scheduler.

[scaiferw]

That's a big help, I now have a better grasp of what I'm dealing with here.

I suspect I may be overestimating the cost of db queries here. Perhaps when I'm putting together a roster or something like that I'll just do a single query for all affected records and keep it in an array, I've done this sort of thing before to reduce db calls.

I'll look into Events in any case.

Many thanks again.