Database 101-7: Stored Procedures and Triggers

by Ryan Huber

A continuation of the blog post Database 101-6: What does page life expectancy (PLE) do to your database?

Stored Procedures

stored procedure.png

Stepping back to our real-world example, we’ve talked about the data storage and indexes. But we haven’t addressed any complex work or calculations yet.

Imagine again that you’re a tax preparer and you have a set of instructions that you perform on each return, doing the same calculations over and over again to complete it. And to save time, you take this list of instructions, put them on a piece of paper and pin it on your desk for easy reference.

SQL does the exact same thing. SQL’s repeatable instructions are stored procedures. And just like you pinned those instructions to your desk, SQL does the same by compiling them into a repeatable execution plan, which is then stored in a section of RAM called the procedure cache.

Each time a stored procedure is executed, SQL refers to this compiled execution plan to save time.

Trigger

Back to our example one more time, another common problem would be your clients moving to different states. Each time they move, their tax liability would change.

Trigger.png

And you, as their tax preparer, would need to automatically adjust their income tax liability each time this happens.

In SQL, this auto-update action would be done by a trigger. A trigger is just a set of instructions (much like a stored procedure) that automatically fires any time a specified piece of data in a table is modified.  

If you’re a tax preparer, you can imagine how important these saved instructions are. If you have a set of instructions that you’re doing on every single return you process, you want to make sure the instructions are intact and valid, or else you could mess up a lot of your customer’s data very quickly.

In SQL, stored procedures and triggers are no different. If they are changed in any way that doesn’t follow established business logic in FrameworkLTC, it can result in a LOT of bad data.



Ryan Huber is the Senior Database Architect at SoftWriters, a Pittsburgh-based company that offers pharmacy management software solutions to pharmacies serving the long-term care community. Ryan is our in-house SQL guru, providing technical expertise in every facet of database operations, including architecture and code review, resolving performance issues for customers, and formulating a strategy for existing and future product features and enhancements.