Re: Why can't I put a BEFORE EACH ROW trigger on a view?

On 02/24/2007 06:47:56 PM, Joshua D. Drake wrote:
> Karl O. Pinc wrote:
> >
> > On 02/24/2007 06:25:54 PM, Joshua D. Drake wrote:
> >
> >> create your table, create your triggers on the table.
> >> Use a view on top of the table for viewing (there is no such thing
> as
> >> a
> >> select rule (well there is, but it is called a view)).
> >
> > Yes, and you can't put a BEFORE EACH ROW trigger
> > on a view, which is my problem. I have _additional_
> > constraints to place on modifications done through
> > views, and trickyness involved in modifying the
> > underlying tables.
>
> Why can't you put the before each row trigger on the base table?

Consider this:

I have two columns in the view A and A1, A1 is the result
of calling a reversible function on A. If the user updates
A, the underlying A should be updated. If the user updates
A1, the underlying A should be updated by computing
the proper value for A by calling the inverse of the function
used to compute A1. If the user specifies both A and A1,
then check to see if the values are in conflict. If so
raise and exception. If not update the underlying A.

And this:

Table X has an Id column and a Name column. It's
joined with another table to make a view. 99.9% of
the time, the Name value is unique in X. If the
user updates the view and supplies a Name
we want the view to check if it's unique and,
if so, come up with the right Id so that the underlying
table can be modified. If the user specifies an
Id also, we again need to check for consistency
with the specified Name and raise an exception
if there's an inconsistency. We do not want the
user using the view to update the Name through
the view. The view is a 1 (of X) to many (of
the table X joins with, say Y), and this confuses the
user. We want the user to use the view to to "move" a row
of Y -- re-relate it to a different row in X by
changing the Name value.

Get very many of these situations and you can
see where it'd be much easier to have all the
logic in one trigger that does all the work.

Most of the problem comes down to raising
exceptions. If you could raise exceptions
in SQL code you might possibly be able to
write large, multi-statement rules and
not have lots of rules with lots of
conditions attached. But why?

Raising your own exceptions are nice because they can
mean something to the user -- with data
values put in the middle of explanatory text,
etc.

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein