February 17, 2012 at 12:09 am
I've taken over a database admin role where one of the companies main applications is an access 2010 adp linked to sql server 2000 backend.
On a data entry table where man hours and machine hours are entered there are vba sub routines firing off in the background - for example when the finish hours from the machine are entered the [TotalMachineHours] field on the machine details table is updated. What I'd like to do is get rid of the vba routines and have the job done by database triggers.
I've a couple of questions on this: (a) is this good practice (b) if the access forms are updating the db tables via an updateable snapshot will this actually fire off the triggers (c) I want to go down the line of implementing merge replication so the timesheets can be entered on remote sites - how will the affect the database triggers? Will they still be actioned when the subcriber updates the main db?
Any help or advice on this would be much appreciated.
Pete
February 19, 2012 at 8:28 pm
Hi Pete
This is probably not the correct forum for your questions as they are more about database design than the frontend. Even so, I am a little surprised to see that you have had no responses to date. As I see it, the main question here is whether or not triggers are good or bad, and opinions will be varied. But as they say "fools rush in where angels fear to tread" so I will try to give you something to chew over. Please note that this is my opinion based on my experience.
Triggers are a useful tool but are often abused by database developers who lack experience and therefore knowledge/ideas of other ways to do things. The very worst instances of trigger implementation are cases where a developer has used a trigger to replace foreign key functionality. I'm pleased that is nothing like what you are proposing here.
Personally, I avoid them, but that does not mean I think they are bad. The main reason I tend to avoid them when designing an application and its database, is that they are not very transparent - they are sort of tucked away and easy to miss when trying to understand what is going on with a database that you didn't design and develop yourself. In your case, if you are wanting to get rid of VB code or the snapshots, you could consider using a stored procedure to do the updating - use the AfterUpdate event on your controls/form to execute it.
Triggers can have performance issues when used on a table with very heavy transactional traffic so you need to bear this in mind. This is especially true if you are planning on using any sort of import function that might employ a bulk insert operation. They are also difficult to control and to incorporate in any testing regime - they at least make this a little more difficult.
So in answer to (a), your current use for the trigger functionality seems innocent enough, though I do have some reservations as to the necessity of maintaining a summary value on the database itself. I am not a normalisation freak, but I try to limit de-normalisation as much as possible and so it is natural for me to question it when I come across it. As a developer/designer I tend to think ahead to the people who come after me in terms of maintaining what I build. Given this approach, I tend to consider triggers an over-complication and try to find another approach which is both simple and easy to follow.
In terms of (b), I would say yes, the triggers will fire if there is a change to the data (Insert/Update/Delete), regardless of how that change is being made. However, I am reluctant to give you any sort of answer on (c) as it is out of my direct experience. Instinct suggests that they should fire if any sort of change to the data is taking place, but I can't be definitive about that. I hope someone else can advise in this respect. However, if practical experience shows that the triggers do not fire, there are other ways to deal with it eg a stored procedure executed on a regular basis by a SQL job would be one possible solution.
If you don't get much of a response here, I think you should perhaps do some research on the pros and cons of using triggers and get a feel for other peoples opinions/experience that way. Maybe post on one of the other forums here.
Best of luck.
Regards
Rowan
February 20, 2012 at 6:45 am
I think ProofOfLife has pretty much covered the issue of triggers, and I agree with what he has said. Triggers are difficult to test and debug so I generally avoid them unless I am trying to enforce a complex business rule at the database engine level. I also seldom use the ADP approach as Microsoft no longer is enhancing the functionallity - but they apparently will still support it through at least the next version. I agree with the issue of storing a value that can be calculated - I usually avoid that approach and calculate the value when I need it.
On the issue of replication and triggers, I can shed some light. Triggers will fire anytime the data is updated - presuming you use an UPDATE trigger. However replication does add a layer of complexity, and depending on the number of replicas, a fair bit of administrative overhead. Are the remote sites able to use a web app to do updates? Both require Internet connectivity, although the replicas do allow people to enter data when the link is down, at least for periods of up to 48 hours.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
February 20, 2012 at 11:06 am
One thing to consider is whether the VBA code is causing updating of the front-end UI. If it is then with using triggers it will be difficult to duplicate the functionality. Code that runs when something changes isn't necessarily background in terms of what it might do to affect other information displayed on the screen. For instance if you are updating a YTD total that is displayed on the screen and it is important that the latest data is displayed - if you do VBA code that can happen when data on the screen changes but with triggers it isn't going to happen until the data is actually updated in the database.
February 20, 2012 at 10:17 pm
Thanks very much for the replies. You've helped me see things a little clearer.
The whole point of implementing the trigger was to update the 'Total Machine Hours' field on the MachineDetails table - but that information is entered as part of the daily timesheet info on the DailyOperatingData table so I shouldn't be storing it in two places, I just need a query (view) to show me the maximum value per machine.
It's not so much of a summary though. Well perhaps a running total. The machine operatives have to record what they see on the hour meter of machines at the start and end of their shift so the last value has to be recorded. It wouldn't be enough of a check for them to just note down the difference between start and finish values and would rely on their memory etc...
Wendell, you mentioned about adp not being supported by MS going forward, is the format they are supporting a normal access front end with linked tables?
The reason I looking down the replication route is that the remote sites may not have contant web connectivity... It is possible (since I've realised I don't need triggers and can get rid of the vba subs that update the value on the other table) that I could have a back up plan for the remote site to export data to a txt file that could be imported into sql when connectivity is restored.
I'm trying to keep things simple but it's a complex application. I think the original developer thought about normalisation and decided it was just a passing fad that wouldn't catch on.
Pete
February 21, 2012 at 7:30 am
... Wendell, you mentioned about adp not being supported by MS going forward, is the format they are supporting a normal access front end with linked tables? ...
Well, I didn't say it would not be supported going forward, but it appears it isn't being enhanced beyond what you see in Access 2010. Here are a couple of links from more or less official Microsoft sources:
Access Blog - ADP's and SQL Server 2008
There are a number of ways to create a "front-end" to SQL Server databases. They include the .NET approach, third-party tools such as Power Bulder, SharePoint, and others in addition to Access. We have substantial evidence that the fastest way to a working front-end is using Access - in fact the ratio of development effort for Access ranges from 1/3 to 1/5 depending on the specific needs and the alternative technology. That said, we also discovered that the linked table approach typically takes substantially less effort than using an ADP.
Bottom Line: If you already have an ADP project then don't throw it away unless you are looking at a complete redesign from the ground up. But do be aware of the limitiations with an ADP and the lack of support for some of the new data types introduced in SQL Server 2008 (and presumably in 2012).
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply