June 6, 2005 at 2:07 pm
We are creating a set of lookups tables that will be shared among many databases in our organization (on the same server).
Ideally, these tables would exist in their own database to which most users have read-only access. As users begin creating new databases with tables that reference these lookups, we would like the ability to build relationships to enforce integrity and cascade update between tables in the "lookup" database and in individual project databases.
I don't believe this is possible in SQL Server 2000 with relationships. However, I believe triggers can access data in other databases. In the project databases, does it seem reasonable that we would have a set of triggers checking for integrity when a value is inserted or updated? Then in the lookup database we might have triggers that trap for value updates and call procedures in the individual databases to update? Seems like a pain, but I'm not sure how else to approach this.
We will have end users who are linking to these tables in MS Access (not necessarily with a front end).
Any suggestions would be greatly appreciated. Thanks, Angie
June 6, 2005 at 2:16 pm
I think that the safest way to handle this would be to have a copy of the table in each database. That way you could create your foreign keys as usual and you wouldn't need a trigger on each table to maintain the integrity.
I would still use a master db where all the lookup tables would be kept. I would use these tables to maintain the data. The changes would have to be transmited with the use of triggers on those tables (delete a row in the master db, use the delete trigger to deploy the changes on all the dbs). Same thing for inserts/updates. That way you only need 3 triggers per lookup tables, vs one for each table that uses the lookup, and you only have to update the triggers in the master db when you add a table or a database.
June 6, 2005 at 3:30 pm
Thank you for the input. I'll see if I can talk everyone into going for a strategy using triggers. -Angie
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply