Re: Create Rule

On Tue, Feb 17, 2004 at 09:01:51PM +0100, Luc ROLLAND wrote:
> Hello !
>
> I would use a rule to store in each modified records the name of the user and the date of modification.
> I try :
>
> CREATE RULE shoe_mod AS ON UPDATE TO shoe_data
>   DO INSTEAD UPDATE shoe_data
>     SET shoename = NEW.shoename,
>        sh_avail = NEW.sh_avail,
>        slcolor = NEW.slcolor,
>        slminlen = NEW.slminlen,
>        slmaxlen = NEW.slmaxlen,
>        slunit = NEW.slunit,
>        shuser = current_user,
>        shdatmod = current_date
>   WHERE shoename = OLD.shoename ;
>
> but I obtain an infinite loop ...
> How can I do that.

Hi Luc,

Your rule specifies to update the table, and updating the table
triggers the rule, which specifies to update the table... ad
infinitum.

Try creating the rule on a view instead, then updating the view:

CREATE RULE shoe_data_v AS SELECT * FROM shoe_data;
CREATE RULE shoe_v_mod AS ON UPDATE TO shoe_data_v
    DO INSTEAD UPDATE shoe_data
    ...

HTH,
Jeremy