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

This does not work, as it still calls a new UPDATE and re-invokes the rule.


> >   I'm trying to use a rule to maintain a last_changed field. 
> >
> >   CREATE RULE rule1 AS
> >     ON UPDATE TO thetable
> >     DO UPDATE thetable SET last_changed = now()
> >     WHERE id = new.id;
> 
> Try instead something like
> 
>    CREATE RULE rule1 AS
>      ON UPDATE TO thetable
>      DO INSTEAD UPDATE thetable SET
>     last_changed = now(),
>     field1 = new.field1,
>     field2 = new.field2,
>     ...
>     fieldn = new.fieldn
>      WHERE id = new.id;
> 
> That is, use an INSTEAD rule and replace each field explicitly.

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