| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-04-08 : 08:35:44
|
| Hi everybody, Is there any function that returns the name of the current database, which I could use in a database trigger ? |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-08 : 08:41:51
|
| you can use db_name() to get the current database name.Just curious how you will use this. can you post your requirements and your ideas for a possible solution ?.-------------------------------------------------------------- |
 |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-04-09 : 02:32:03
|
| Thanks Nazim ! Sure, I am free to share my ideas with you. You can guide me if they are not good.Basically, I am developing an Inventory package, or rather converting an Inventory package I had developed in Foxpro 2.6 for Windows to Visual Basic 6.0 - SQL Server 7.0. In this package, the system is such that, multiple companies can be created and for each company and financial year a seperate database is created which is named like "InvMEDI2001", where "MEDI" is the company initials and 2001 is the year of the starting date of the financial year, in this case 1st Apr 2001. There is a common database "InvCommon" which maintains a record of the companies and financial years in "Company" table. The company - financial year databases are created automatically by the package using SQL-DMO. I will need the db_name() function to validatedate fields at the back end using database triggers. E.g. Purchase Order date should fall in the financial year of the database, whose information is present in the database name, e.g. if it were "InvMEDI2001", it would search for the record in the Company table, whose year of starting date of financial year is 2001 and check whether the purchase order date falls within the starting date and ending date of the financial year. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-09 : 08:27:19
|
| Utpal, How will you decide on which database will the row you are adding should go for. like your connection string , does it changes dynamically .Can you elaborate on this-------------------------------------------------------------- |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-04-09 : 14:21:57
|
| Yes, the connection string does change dynamically. When the package is started, no company is selected and no connection is open. There is a "Select" option in the "Company" menu, on clicking which, a connection is opened on the "InvCommon" database and a Company - Financial Year selection list box is opened up, which is based on the "Company" table, from which a company and financial year can selected. On selecting that, the name of the company - financial year database to be opened is derived from its record in the Company table, which is :"Inv" & <Company Initials> & <Year of starting date of financial year>and assigned to a variable named "CurrentDB". Using this variable, the connection string is generated and the company connection is opened. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-10 : 03:19:23
|
| Am confused , if you have a connection pointing to the new db. how will you access the common database?. isnt it vice versa. that is you inserts records in the common database using a trigger from the new database rather ?-------------------------------------------------------------- |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-04-11 : 04:02:47
|
| I am using 2 connection variables : "SysConn" on which "InvCommon" database is opened and "Conn" on which the Company - financial year database (e.g. "InvMEDI2001") is opened. "InvCommon" is only accessed while creating, selecting, modifying & deleting a company - financial year database and during Year-end transfer (closing balances of current year are transferred to opening balances of new year). After completion of any of the above mentioned operations, the "SysConn" connection (of "InvCommon" database) is closed.The "InvCommon" database contains only one table - Company, which maintains a record of the company - financial year databases created and information regarding them. Its structure is as follows :CompanyInitials - PKCompanyNameCompanyAddressFirstDate (of financial year)LastDate (of financial year)It seems from your second question, that you are under the impression that I am storing the data both in the company - financial year database as well as in the "Invcommon" database, which is not so. Or maybe, I did not understand what you are confused about. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-11 : 08:41:52
|
My doubt creeps from this. i dont understand why do you have to again search for the company table and get its start date and end date, when you have created a connection string which points to the Database (isnt it that the connection string is created dymanically upon the period selected). Am of the opinion that Validations are better on a client side, you shouldnt make unnecessary extra cycles to the server to validate somethign which you can do it at the client side.Or Maybe Am missing something.......quote:
The company - financial year databases are created automatically by the package using SQL-DMO. I will need the db_name() function to validate date fields at the back end using database triggers. E.g. Purchase Order date should fall in the financial year of the database, whose information is present in the database name, e.g. if it were "InvMEDI2001", it would search for the record in the Company table, whose year of starting date of financial year is 2001 and check whether the purchase order date falls within the starting date and ending date of the financial year.
-------------------------------------------------------------- |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-04-12 : 03:46:26
|
| I have put the validations both on the client side as well as on the server side. When validating on the client side, it uses client side variables to validate the date fields. I have put validations on the server side to validate direct entry at the back end using the SQL Server Enterprise manager or Query Analyser.Edited by - Utpal on 04/12/2002 03:48:17 |
|
|
|
|
Current database function ?