July 13, 2016 at 11:47 pm
I have an issue with an application I am writing. The data model is comprised of tables that represent a system that will track inventory for IT, e.g. Computers, Software and Licenses. In order for this to be consistent, an intersect table was added, and within the License and Software tables there are columns for LicenseType and SoftwareType. I have uploaded an attachment of the data model to help with this dilemma.
The database should default new software to have a trial license associated with it by default upon Software insert. I am not sure if I have the LOV table structured properly, and how to set their values and write the trigger accordingly. The values added to the LOV tables are GPL, Commercial for SoftwareType, and GPL, Commercial, and Trial for LicenseType. The SoftwareType column in the Software table and the LicenseType column in the License table have the same naming convention and should give clarity to where a particular value came from. Should these be foreign keys in their respected tables, or is no key needed here? Can I still reference the values in the LOV tables without putting foreign keys in the Software and License tables, and if so, how do I create this trigger to do what I need it to do?
July 14, 2016 at 3:17 am
Why do you need a trigger to do this? Simply put a default constraint on the column.
...
July 14, 2016 at 7:08 am
Foreign Keys are an important part of the data integrity problem (and can also offer benefits to the optimizer in some cases).
I agree with the other poster that a Default Constraint could help with the initial value issue, but only if you code your INSERT logic to make use of that. Personally I might also make my code use a stored procedure for INSERTs to that table and then code it to explicitly use DEFAULT for each new row if it isn't specified that they purchased a full license right off the bat for example.
https://msdn.microsoft.com/en-us/library/ms174335.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2016 at 11:20 am
Guru
Thank you for the response. Let me explain the task a bit better, my apologies on the vagueness. This is what I was envisioning.
Task .. Create a new Software Recorded
… fill in data in screen..
… Hit Save
…….
… An INSERT Statement will create a ‘Software’ table record with fields filled in from data screen
… on INSERT Trigger inside ‘Software’ table .. is called
… that trigger will execute an INSERT License record.. and use the new ‘Software’ record ID as the FK ID inside the License record .. the License Type filed of the record will be ‘TRIAL’
So at the end of it all .. I have a new Trail License record automatically built for any new Software records created..
The other option is to do all the above in code after SAVE..
Using INSERT trigger allows us to keep data clean in the event someone tries to add data via something like a SQL Server Management Studio tool
I realize Default Constraints are things you use if you want to automatically fill in fields ‘inside’ the same record you are inserting.. not a child record of another table [License is a child table to Software] which I have here. I should have provided you a capture of the full model for version 1 of the application.
July 14, 2016 at 11:39 am
To add some more clarity, The changes I made to the data model earlier last week required a license to be used to install a piece of software. I am doing this to make the interface easier to use. In doing this, I opened up an issue with "free software". Free software doesn't get a licensed, or a license isn't applied until a bit after the software is installed. The default trial license was an attempt to solve that. If I say the first license a piece of software gets is a "trial" license, the constrain is still honored. By adding a trigger to automatically create this trial license, our NA wont have to do this by hand. I want it done at the database level to make the interface make sense.
So, I am having concerns as to what is the right trigger to use in this approach. How would this trigger be written?
July 14, 2016 at 11:42 am
You could do what you want to do and it will work just fine. There are many examples to be found of simple trigger code like you need.
I would still do this using a sproc personally. If you have someone do the insert using SSMS then that isn't via the app and you either wouldn't need the trial license or the person doing it would know that it is required and simply use the sproc in the first place because they were a support staffer. This is known as production control. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply