Oops.
This does not work, as it still calls a new UPDATE and re-invokes the rule.
You are right. Your initial mention of using rules made me think of
all the cases I have used this technique for with views. I forgot
that the two tables are the same. What I meant to suggest (which may
not be relevant if you really have only one table) was create a rule
for a view of your table that will update the last_changed field in
the underlying table. I have found that to be very useful in a bunch
of situations where I commonly have one table with lots of different
views and different ways of inserting/deleting/updating as appropriate
to each view. Then the redirection via an INSTEAD rule and setting
last_* fields with rules is valuable.
Sorry about missing the fact that both of your tables were the same.
> CREATE RULE rule1 AS > ON UPDATE TO thetable <-- for views these are > DO INSTEAD UPDATE
thetableSET <-- not the same table > last_changed = now(), > field1 = new.field1, > field2 =
new.field2, > ... > fieldn = new.fieldn > WHERE id = new.id;
Cheers,
Brook