Stored procedures allow database engineers to enter code in a procedural language to create some functionality. The code can be executed directly or set to run when certain actions are triggered. Triggers are associated with tables, and can start before or after a specified event occurs. This means that once an operation such as INSERT, UPDATE, DELETE, or TRUNCATE is executed on the table, the trigger will run the corresponding procedure.
While the usage of stored procedures requires the understanding of additional programming syntax, it can be of great advantage for application programmers. Instead of manipulating database records in an application’s code, they can program some algorithms directly in the database layer. This improves the loading speed of the application and significantly decreases the volume of data transfer from the database to the script’s engine and back. On the down side, testing stored procedures is more complicated, since quality assurance engineers need to separate and run their tests under two different programming paradigms – the application’s source code and the programming language used in the database stored procedures.