Unit 2: Stored Procedures and Triggers
Much of the work of database retrieval can be done on the server side as opposed to the client side. The server can execute commands more efficiently and is capable of sending only the result set back to the client, rather than sending the entire data set back and forth between client and server. In this unit, we will look at stored procedures, or blocks of code stored and executed on the server. We will also look at triggers, which are blocks of code that execute on the server when certain events occur, such as the addition or deletion of data from a table. Lastly, we will study rights when running a stored procedure.
Completing this unit should take you approximately 9 hours.
2.1: Stored Procedures and Their Usage in Relational Database Management Systems
Read this chapter, which will provide and appropriate overview of using Stored Procedures and Functions in MySQL Server.
Read sections 18.1 and 18.2 for an overview of stored procedures and stored procedure syntax in RDMS. As you read please keep in mind the effects that stored procedures are likely to have on the Database System.
2.2: Stored Functions
Read sections 18.3 and 18.4 for an overview of stored functions, triggers, and definer and invoker rights in RDMS. As you read compare and contrast the results that can be obtained utilizing Stored Functions in a Database System.
2.3: Constraints and Triggers
Read this article, which gives an overview of SQL, including constraints in SQL, which are used to ensure database data integrity.
Read this article, which describes SQL syntax and examples for constraints.
Read this article, which illustrates constraints and triggers supported by Oracle. A trigger consists of code that is executed when a predefined event occurs at the database, schema, view, or table level. Constraints are simpler than triggers, and are defined at the column or table level, and only check for data integrity.
Read this example of a trigger.