Richard ~
Thanks for your response.
Can a trigger be written on a *view*? I can't find anything in the
PostgreSQL docs that answers this question.
I originally wrote these actions (described in my original message) as a
trigger on my base table, but then realized I was getting in deeper and
deeper trouble because (a) I was getting into cascading triggers that I
didn't want and (b) I need to enable some queries to access the base table
without triggering these actions. That's why I set up the view, and then I
assumed that the only way I could implement these actions was as rules.
~ Ken
> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Monday, December 19, 2005 4:08 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> > This rule is supposed to (1) cause an update directed to the view
> > "my_data_now" to be made to the underlying table "my_data", (2) reset
> the
> > "effective_date_and_time" of that row to 'now', (3) insert a record
> > containing the old values into "my_data", and (4) expire that "old"
> record
> > by setting its "expiration_date_and_time" to 'now'.
>
> I think you want a trigger rather than a rule.
>
> Rules rewrite the query structure, triggers let you deal with values on
> a row-by-row basis (for row-level triggers).
>
> --
> Richard Huxton
> Archonet Ltd
