update in trigger : same table => crash : other solution in this case ?

[Stéphane Lambert]

Hi everybody

I have a table with two columns (newsletter and journal from the table
mailing) with 0 or 1 as value.

My trouble is that in my new request, if I update or create one of them, I
need to update or create the other with the same value because they are the
same today. And possibly without changing all the existing code of the
existing application. If the user suscribe to one of them, he subscribes to
the two of them. If the user unsucribe for one of them, he has to unsucribe
for the two of them.

So I tried :
1 / a trigger with some test inside : example : IF(
new.newsletter='1' ) THEN UPDATE mailing SET journal=1 WHERE id = new.id;
END IF;

The trouble is my trigger On Update concern the same table as the original
update, and today this just does not work => (Can't update table 'myTable'
in stored function/trigger because it is already used by statement which
invoked this stored function/trigger.)


2 / I tried some test inside before sending the query : exemple IF
(new.newsletter='1' ) THEN SET new.journal='1'; END IF;

The trouble is all existing query does NOT call the 2 columns in the Update
or Insert existing queries. Some of them send only one of them for the
create or the update.

The solution 1 shoud have been OK because this DO work with Oracle, Informix
and Sql Server.

Please don t tell me I will have to go to rewritte all the call in the
existing application : there must be a solution ?

Please ?.........

Cheers

Stéphane