|Subject||Re: Updating views|
> I have a really ugly view that is giving me 'attempting to update aversions.
> read only column' when I try to update it. I think I know where the
> problem is but I don't know how to fool Firebird into making it
> updatable. I think that the part of the SELECT that is causing be
> problems is this:
> SELECT .....,
> SUBSTR(EXTRACT(YEAR FROM CAST('NOW' AS DATE)), 3, 2),
> FROM TABLE
> I need to be able to return the 2 digit year from the current date as
> part of the view but I am fairly certain that this is what is causing
> my problem. Is there some way to accomplish the same thing that won't
> cause Firebird to gripe when I try to update the view. I am using
> 1.0.3 and / or 1.5.3. I need something that will work for both
>An updatable view only works for simple queries. Given that you are
returning a field in your view that Firebird would not know how to
deal with if you tried to update it, it should not surprise that it
becomes a read-only view. The same thing would happen if you included
a join or aggregate etc.
In this case, you must create a trigger on the view to perform the
underlying table operations. Also, check the Firebird 2 release notes
because the behaviour changes slightly.