>> This works for update, but will not work for insert/delete because of

"Henrik Johansson" <nospam_Henrik.Johansson@mh.se> wrote in message
news:uTcoiy2aEHA.716@TK2MSFTNGP11.phx.gbl...
> Our schedule management system for the university runs as one database for
> each campus.
> We want to consolidate the databases to one single database for alot of
> reasons, but we have for the moment no possibility to modify the app to
fit
> the requirements for "intercampus work".
> Until the app is modified to support the extra field which tells what
campus
> the data belongs to, I thaught that we could maybe use updatable views in
> each campus database collecting data from tables in a common database and
by
> that way trick the app that it has the data in the database it work with.
>
> create view datatbl as
> select campus='a',datacolumns...
> from commondb..datatbl
> where campus='a'
>
> This works for update, but will not work for insert/delete because of
> selecting a constant value.
> Insert requires "select campus,datacolumns..." to work, but that will try
to
> insert a null-value into the campus-field when the app isn't sending that
> column value and fail when the field doesn't allow null.
>
> The campus name can be found in another table, but trying to use join to
get
> that information returns the same error as when using a constant value.
>
> Any solution for my problem?
> /Henrik
>
>

> I think I'll nead to read up on INSTEAD OF triggers, which sounds to be
> the
> solution.

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:%23JCGeq4aEHA.3804@TK2MSFTNGP10.phx.gbl...
> >> This works for update, but will not work for insert/delete because of
> selecting a constant value. <<
>
> When a value is not provided, the columns needs to have a DEFAULT to
> use. Your choices are (1) copy all the procedures and add the campus
> name to their name, then put in the appropriate constant for that campus
> or (2) use some kind of INSTEAD OF trigger on the VIEWs that has the
> targeted campus.
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!