Homework SourseBased on an example database from your workplace or an appli
Based on an example database from your workplace, or an application that interests you, describe how views, stored procedures, and triggers could be utilized
Solution
Stored Procedures
Stored procedures are one of numerous mechanisms of encapsulating database logic in the database. They are similar to regular programming language procedures in that they take arguments, do something, and sometimes return results and sometimes even change the values of the arguments they take when arguments are declared as output parameters. You will find that they are very similar to stored functions in that they can return data; however stored procedures can not be used in queries. Since stored procedures have the mechanism of taking arguments declared as OUTPUT they can in theory return more than one output.
Stored Functions
Stored Functions are very similar to stored procedures except in 3 major ways.
Views
Views are one of the greatest things invented since sliced bread. The main beauty of a view is that it can be used like a table in most situations, but unlike a table, it can encapsulate very complex calculations and commonly used joins. It can also use pretty much any object in the db except for stored procedures. Views are most useful when you always need to join the same set of tables say an Order with an Order Detail to get summary calculation fields etc.
Triggers And Rules
Triggers are objects generally tied to a table or view that run code based on certain events such as inserting data, before inserting data, updating/deleting data and before these events happen.
Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.
They are useful for making sure certain events always happen when data is inserted or updated - e.g. set complex default values of fields, inserting logging records into other tables.
Triggers are especially useful for one particular situation and that is for implementing instead of logic. For example as we said earlier, many views involving more than one table are not updateable. However in DBMS such as PostgreSQL, you can define a rule on a view that occurs when someone tries to update or insert into the view and will occur instead of the insert. The rule can be fairly complex and can layout how the tables should be updated in such a situation. MS SQL Server and SQLite let you do something similar with INSTEAD OF triggers. Note the term Rule is a little confusing in DBMS because they mean quite different things. In Microsoft SQL Server for example a Rule is an obsolete construct that was used to define constraints on tables. In PostgreSQL a Rule is very similar to a trigger except that it does not get triggered per row event and is defined without need of a handling function.
| Feature | Rating |
|---|---|
| Works in various kinds of databases | 3 (many databases such as DB II, Oracle, SQL Server, MySQL 5, PostGreSQL, FireBird support them). There are also a lot that don’t e.g. MySQL < 5.0, MS Access (although parameterized queries serve a similar role) |
| Can be called by multiple applications and interfaces | 4 (generally they can be called, but the use of OUTPUT arguments is not always usable) |
| Can take an undefined number of arguments | 2 (note most databases allow to define optional arguments, but this can become very unwieldy to maintain if there are a lot because you end up duplicating logic even within the stored procedure so is generally avoided) |
| Reusability within the database | 3 (you can not reuse them in views, rarely in stored functions and other stored procedures unless the stored procedure using it does not require a return value or result query). This varies slightly from DBMS to DBMS. |
| Can be used to change data in a table without giving rights to a user to change table directly | 4 In general true for most DBMSs that support them. |
| Can return varying number of fields given different arguments. | 3 –again in theory it can, but very hard to maintain since you would often be duplicating logic to say return one field in one situation and other set of fields in another situation or update a field when the field is passed in as an argument. Note that in many databases such as for example SQL Server and Oracle, one can return multiple result sets with a stored procedure, but the receiving end needs to be able to do a next result set call and know the sequence in which the result sets are being sent. |
| Long stretches of SQL easy to read and maintain | 5 (one of the great strengths of stored procedures is that you can have long transactions of sql statements and conditional loops which can be all committed at once or rolled back as a unit. This also saves on network traffic. |
