Re: [SQL] Adding a rule to update a last_changed field

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 thetable SET <-- not the same table
> last_changed = now(),
> field1 = new.field1,
> field2 = new.field2,
> ...
> fieldn = new.fieldn
> WHERE id = new.id;

Cheers,
Brook