Database Trigger Usage - Best Practices

Brief Background:

I currently maintain a fifteen year old inventory management system. In an effort to provide real-time quantity on hand for both single items and kits (possibly nested down to the Nth level), inventory is allocated to orders on the fly when a page is requested (allocation priority is determined by date received and date expected). Depending on how many orders are in the system, and more specifically, how many of those orders contain nested kits, performance can be an issue.

The Question(s):

We're currently in the process of designing a new inventory management system using SOA principles. Instead of allocating the inventory on the fly, we would like an allocation table to be updated after every insert, update, and delete occurs that could affect the items allocation data. There is some debate as to whether the allocation table should be updated by triggers in the DB, or by the C# order service.

• Which method would best adhere to SOA principles?
• Whi开发者_运维问答ch method would be more performant?
• Is this the proper use of a trigger?
• Is either method generally accepted by the community at large as the "proper way to do things"?

Thanks in advance for your time!

I think the 3 other answers from HLGEM, Jimbo, Joel Brown are all correct.

Just to answer each of your specific questions

Which method would best adhere to SOA principles?

Either depends on the rest of your architecture. How many other systems do you have? Do this other systems need to access the DB directly or do they access the DB via this service?

Which method would be more performant?

No real difference either way.

Is this the proper use of a trigger?

Yes

Is either method generally accepted by the community at large as the "proper way to do things"?

No the community seems to be split on this decission and it should be evaulated case by case.

I think this comes down to the old religious war of whether or not to have any business logic in TSQL (stored procs or triggers). Some people love to drive as much logic into the database layer as possible while others think that this is the worst thing you can do.

Have a look at this question for a lengthy debate of the various pros and cons. Whichever argument you find most compelling is the way you should go.

Triggers are the only solution where you can guarantee the data will stay in synch. You cannot guarantee that values won't be changed on fly outside of the application (say to fix some large data issue) and so a trigger is the only correct solution.

If the insert, update, and delete is performed by stored procedures then the allocation table should be updated inside those stored procedures also - this would be the ideal solution.

Otherwise, if the insert, update, and delete is being performed by the application then triggers will do what you need - but bear in mind that triggers should always be avoided and if you must have them keep them as simple as possible, this is because triggers can quickly become very difficult to debug.