Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I tried using trigger concept. If any new row inserts in the user table my trigger will fire and try to create a dynamic database for that new user. But Inside a trigger create database is not working. How can get it done. Can anybody have any suggestions.Please help of on this

share|improve this question
I'm not certain, but I would wager you're not allowed to run DDL (Database Definition Language) statements inside of triggers -- it would seem to me to be an invitation for security holes... – voretaq7 Nov 18 '12 at 5:45

migrated from serverfault.com Nov 18 '12 at 5:43

3 Answers

up vote 7 down vote

As the documentation says (under Trigger Limitations):

The following Transact-SQL statements are not allowed in a DML trigger:
(...) CREATE DATABASE (...)

There are further limitations, consulting the documentation is highly recommended.

This all means, to my understanding, that you should solve this problem at application level*: check whether your user is successfully created, and then issue a CREATE DATABASE statement. Preferably these two are enclosed in a transaction, so that either both succeed or both fail - this way you can be sure that your data are in a consistent state. You may have to issue a DROP DATABASE if the transaction fails. (Thanks to Jon Seigel for pointing this out.)

*: From this point of view, the 'application' may be a T-SQL block of code (or a stored procedure) as well.

share|improve this answer
1  
CREATE DATABASE can't be put in a user transaction because it affects things outside the database engine. See the Remarks section here. I agree with the rest of your answer, though, so +1. – Jon Seigel Nov 18 '12 at 16:21
@JonSeigel Thanks for your notice. Does this mean that you can't even enclose it in a transaction started by a database abstraction layer? – dezso Nov 18 '12 at 20:41
A DAL merely abstracts the database operations, it doesn't perform any special behaviour. In theory, a DAL could roll it back, but it would have to be coded to manually execute a DROP DATABASE command, as a ROLLBACK won't work. – Jon Seigel Nov 19 '12 at 3:42
up vote 3 down vote

You do not want to provision a seat for your new tenant in a DML trigger. For one, provisioning is usually lengthy and you do not want to block the DML, and likely the HTTP request for new user onboarding behind it. You want to be able to create and onboard the user, then start the provisioning process. If the provisioning goes awry, you can resolve the problem with offline intervention later, and you have the user record to contact and to notify that the account is now properly provisioned (ie. you do not lose a customer). The second reason is the problem you run into, namely that some provisioning operations are simply not do-able from a DML trigger, and as things will evolve and your architecture becomes more complex the provisioning will become more complex (eg. load balance the account resources).

The universal solution is to decouple the two and use queues. The account creation drops a message into a queue to request provisioning the account. A queue monitoring process kicks off (after the user account is committed!) and does the necessary work, asynchronously. SQL Server can handle this via Service Broker and internal activation. This is much better than doing it at application level because is reliable (ie. if the user stops the page load during the account creation, which in turn triggers a cancellation of the application processing the HTTP request, it does not leave an orphaned non-provisioned account).

share|improve this answer
Very important points! – dezso Nov 19 '12 at 14:14
up vote 0 down vote

I would question this whole design. Even if you could create a database, it won't be being backed up, so what's the point of it?

Is there any reason your user needs a whole database? How about just having normal tables, with a userId or userName column, so if there is user-specific data they need, they can just query the relevant table filtered by userId/name.

This has a number of advantages, including (presumably) your database being backed up and maintained as part of a regular maintenance plan, non-proliferation of the same objects over multiple databases.

Have you mentioned this design to your DBA? I'd be keen to know what they think.

share|improve this answer
There definitely are multi-tenant databases around, just think of SaaS. – dezso Nov 19 '12 at 13:52
2  
Both approaches (shared db vs. 1 db per tenant) have pros and cons. Think about systems where tenants can opt in for schema upgrades. Shared db. and scoped queries (act_as_tenant like) tend to be favored at the low end spectrum, while 1 db per tenant tends to be favored at the enterprisey high end of the spectrum. All in all is a separate debate though. And btw provisioning that configures maintenance (ie. regularly backs up the account db) are rather the norm than the exception. – Remus Rusanu Nov 19 '12 at 14:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.