MySQL Forums
Forum List  »  Triggers

Trigger to update other table produces error 1442
Posted by: Simon Giddings
Date: August 13, 2011 01:33AM

I have three tables in my database which look like this (fields reduced to minimum for readability)

table client
id_client unsigned int
last_update timestamp
...

table client_std
id_client_std unsigned int
id_client unsigned int
...

table client_prem
id_client_prem unsigned int
id_client_std unsigned int

Ok so far.
Now I wanted to update the "last_update" field in the client table when ever it was updated.
So -
create trigger update_client_trigger after update on client
for each row
begin
update client set last_update=now() where id_client=NEW.id_client;
end $$

create trigger insert_poi_trigger after insert on poi
for each row
begin
update client set last_update=now() where id_client=NEW.id_client;
end $$

This worked great, so far so good.

Here is where it gets difficult. I now wanted to update the same table when one of the depending tables (client_std or client_prem) was updated. Here is what I did for the client_prem table :
create trigger updated_client_prem_trigger after update on client_prem
for each row
begin
update client as c join client_std as s on c.id_client=s.id_client
set c.last_update=now()
where s.id_client_std=NEW.id_client_std;
end $$

create trigger inserted_client_prem_trigger after insert on client_prem
for each row
begin
update client as c join client_std as s on c.id_client=s.id_client
set c.last_update=now()
where s.id_client_std=NEW.id_client_std;
end $$

Now, when I do an update on the client_prem table, I get the following message:
"Can't update table 'poi' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

Can anyone help me resolve this?
Am I going about this in the wrong way ?

Simon

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger to update other table produces error 1442
3284
August 13, 2011 01:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.