Thoughts about the smart way to a task

[Steve]

I'm still a database newbie so I would like to solicit thoughts about
the smartest way to do something in sqlserver.

My company has a web application that we customize for each client.

We can do this because everything is database driven. We have
database tables that contain our HTML and database tables as well as
some standard tables for each database. We have an in house app that
lets us tweak both of these things and creates a new web site and
database tailored to each project.

Each of these sites has a table that stores a schedule are clients
use.

The records in this schedule table change when information in other
custom generated tables change.

My company currently uses a legacy foxpro app to update the schedule
table.
The foxpro app contacts sqlserver, reads a table with a list of tables
and scheduling information to check, checks each of those items and
updates the schedule table.

I would like to lose the foxpro app.

At first thought.........as a database newbie.......putting triggers
in each of the tables to update the schedule when something changes
seems the way to go.

However, since we change a part of the schema ( we have an app that
generates the database tables unique to each client ) for each client
I would like a scheme that would not involve having to create a
different trigger for each new table.

I would also like something that updates in real time. Right now the
foxpro app is executed once a day.

I was thinking of making a large stored procedure and putting an
identical call to that procedure in each table.

Each table would have the same trigger in it that would get fired when
the record was altered. It would call the stored procedure with
relevent arguments to update the schedule.

Does this sound like a smart way to solve this problem or am I not
thinking "database enough"?

Any thoughts are welcome.

I would like to build a better solution

Steve

[Erland Sommarskog]

[posted and mailed, please reply in news]

Steve (st**********@yahoo.com) writes:

However, since we change a part of the schema ( we have an app that
generates the database tables unique to each client ) for each client
I would like a scheme that would not involve having to create a
different trigger for each new table.
It seems to me that this is the gist of your problem.

The "proper" design of this, would be to add one column to each table
that specifies which client the row relates to. If you do this, then
you have no problems with your triggers.

Now, you may not want to this, if you don't want clients to see each
other's data. This can be addressed with views, but if you don't do
things right, your clients may be very angry with you.

Yet an alternative would be give each client his own database. This
separates them even more. You could have a model database, from which
the app could create a new database with a RESTORE COMMAND. That model
database would include your tables and trigger.
I was thinking of making a large stored procedure and putting an
identical call to that procedure in each table.

Each table would have the same trigger in it that would get fired when
the record was altered. It would call the stored procedure with
relevent arguments to update the schedule.

But then that table would have to use dynamic SQL to determine which
tables to operate, and that would be messy.

Can you not just change the app, so that it creates the triggers too?
That seems to be the simplest solution.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp