Re: DO INSTEAD and conditional rules

On Apr 26, 2005, at 2:43 PM, Rob Butler wrote:

> For this particular scenario, can't you just create
> two ON DELETE rules? The first would delete from b,
> the second from a. Perhaps an example with a scenario
> like this can be added to the doc's?

No, that approach has the same problem. Once the first rule deletes a
record, it's gone from the view, so the second delete wouldn't know how
to do it.

> So, the short answer is you can only perform one query
> in a rule, but you can have multiple rules defined to
> do what you need.

No, you can have multiple queries--you just have to understand that
those that come first might have an effect on those that come later.

> Can you call a stored proc from a rule? You could
> pass the old.id to the stored proc and do as many
> queries as you like in there without worry that the
> old.id would go away.

Yes, that would be one solution. Another would be to have an ON DELETE
CASCADE on the foreign key constraint. Then, to delete the record from
both tables, you just delete it from the primary key table.

> Just some thoughts. It does suck that old.id goes
> away. Any way of preventing that from happening?

Doesn't sound like it. But your suggestion to use a function is a good
one. (Although Tom did say something about volatile functions...).

Regards,

David