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
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.
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).
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.