August 14, 2017 at 7:02 am
Hi,
Im working in a small VB NET 2017 application with SQL Express 2012 database that will have max 5 users connected at the same time.
I have been reading about triggers and functions and im wondering what would be the best to update stock in the following exemple.
Table products (product info and stock)
Table entries(product entrei registe)
Table used(used or sold products)
What would be the best sollution:
A trigger that everytime a new entrie or used is added will add or subtract on the stock in Products table or create a function and call it from the GUI side?
Thanks...
August 14, 2017 at 7:14 am
divinomestre79 - Monday, August 14, 2017 7:02 AMHi,
Im working in a small VB NET 2017 application with SQL Express 2012 database that will have max 5 users connected at the same time.
I have been reading about triggers and functions and im wondering what would be the best to update stock in the following exemple.Table products (product info and stock)
Table entries(product entrei registe)
Table used(used or sold products)What would be the best sollution:
A trigger that everytime a new entrie or used is added will add or subtract on the stock in Products table or create a function and call it from the GUI side?Thanks...
Do you need to persist the value? Can you just create a view which performs the calculation for you on the fly?
A secondary question: why not use SQL Server 2016 rather than 2012?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 14, 2017 at 7:40 am
A secondary question: why not use SQL Server 2016 rather than 2012?
Couse some of the users have as operating system Windows 7.
I have tryed also 2014 but also had some issues in Windows 7 so i went for 2012.
As for the query, some of the clients require that when they check a product or navigate tro the products in the products form to have the stock and the ordered quantiry visible.
So the form that creates products and is used to edit them is the same, and i already have to besides the produtc table to load a query to display Units(m, m², etc...) another to display category, other for VAT and i notice that even with little info on each it slows down performance of the app.
So i tough of using a trigger/function/stored procedure to have it calculated this way.
August 14, 2017 at 7:45 am
divinomestre79 - Monday, August 14, 2017 7:40 AMA secondary question: why not use SQL Server 2016 rather than 2012?
Couse some of the users have as operating system Windows 7.
Are you planning to run the instances locally then? If not, then you will have no problem connecting to a 2014+ instance from Windows 7. I, myself, connect to 2016 and 2017 (RC2) instances on an almost daily basis from my Windows 7 Desktop.
if not, how are you planning to synchronise the data between separate instances?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 14, 2017 at 7:51 am
This ment for small business, so will likly by in lan fom 1 to 3 computers, max 5.
No server, will be local instance that will be configured to alow TCP connection, and in some cases the instance is installed on Windows 7.
The targer are is small construction companis, and they dont upgrade hardware often, so most of them are still with 7, have office at home, and wil use they personal PC or laptop to use the software.
August 14, 2017 at 7:58 am
I have read somewhere that triggers may couse a increase on server workload if the correct term to use.
So i m wondering since there will be a reduced number of users, and also a reduced number of transactions afecting this trigger if wouldnt be a solution.
August 14, 2017 at 8:33 am
Triggers can cause a lot of overhead. Emphasis on can. It all depends on what the trigger is doing, and how well it was designed. A poorly designed trigger is going to have a much greater negative effect on performance.
On the subject matter of your actual post, however, I wonder why you want a trigger. We can only really see a slice of the pie (so the speak) from your original question. What is your actual goal with the trigger and when will it trigger? Fore xample, are you looking to update stock levels when someone says that have used a product?
I'm wondering if a better option might be to use Stored Procedures, most likely wrapped in a transaction with a TRY CATCH; that will ensure that if you do one update and the second fails, you can roll back the whole process.
Perhaps a little more detail on what you're envisaging might provide us with more insight so we can provide more in-depth answers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 14, 2017 at 8:34 am
OK, if you know that your VB.NET application is the only way that entries in the tables you mention can be changed, best practice is that you code whatever stored procedures you need to ensure the accuracy of your stock data.
Triggers are best avoided, if it is practical to do so, because they tend to make databases more difficult to maintain and troubleshoot than procs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 14, 2017 at 8:42 am
Phil Parkin - Monday, August 14, 2017 8:34 AMOK, if you know that your VB.NET application is the only way that entries in the tables you mention can be changed, best practice is that you code whatever stored procedures you need to ensure the accuracy of your stock data.
Looks like we're thinking along the same lines Phil. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 14, 2017 at 8:44 am
Yes, application will be distributed with SQL EXPRESS, SMO, SQL SysClrTypes that SMO requires.
So the only way to access the database will be tro my VB NET application.
Unless the client decided to get a copy of SQL Management studio but then it will be hes responsability.
August 14, 2017 at 8:49 am
Thanks for the help...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply