Goals

Explore triggers and stored procedures within a database, see how they work and how they are engaged. Create your own and see what happens. Instructions

Use the same database as Lab 2 (see Lab 2s instructions if you dont have that sample database)

Execute the following SQL: CALL film_in_stock(3,2,@totalInventory); - record the results in your write-up

Review the code for the procedure film_in_stock. In phpMyAdmin, you can do this by going to Routines and clicking edit next to film_in_stock. In pure SQL, do: SHOW CREATE PROCEDURE film_in_stock you will have to look at the Create Procedure column in order to see the code itself. I have included a snapshot of the procedure details as well in this document, but it will be more informative if you review the code in your database, rather than just the snapshot.

Execute the following SQL: SELECT get_customer_balance(546,NOW()) AS balance; - record the results in your write-up

Review the code for the function get_customer_balance. In phpMyAdmin, you can do this in a similar way that you looked at the procedure. In pure SQL, do SHOW CREATE FUNCTION get_customer_balance and observe the value of the Create Function column. A snapshot has also been included below.

Execute the following SQL: INSERT INTO payment (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update) VALUES (NULL, 3, 2, 34, 2.38, 2010-01-01, CURRENT_TIMESTAMP);

Look up the record that you just inserted (hint: it should have the largest payment_id value now), is there anything different about the values that are displayed compared to what the insert should have performed? Describe any differences in your write up

Review the code for the trigger payment_date. You can view this in phpMyAdmin from either the Triggers tab at the database level, or the Triggers tab within the payment table. In pure SQL: SHOW TRIGGERS FROM sakila WHERE Table = payment. A snapshot has also been included below.

Create a new Procedure, Function, or Trigger to perform the tasks under the New Operations section. Be sure to include your SQL and any explanation you will to provide in your write up

Create queries to answer the questions under the Queries section. Be sure to include your SQL and results in your write up

Procedure

Notes Procedures in SQL are one of the ways that you can create some control flow in your databases. Procedures can be though of like callable functions, with input and output parameters, as well as the ability to print results to the caller. The syntax for a procedure is rather complicated, but phpMyAdmin does a good job of breaking down the different components into easy to use web form fields. The most complex field in the interface is the Definition text box. This text box is where you will put the body of the procedure. In the film_in_stock procedure, you can see two different SQL statements between the BEGIN and END statements. The first performs the query to list the ids of the inventory that is in stock for that particular film and store, while the second query counts the number in stock and stores it in the parameter p_film_count using the special case of the INTO clause at the end of the query.

Function Notes

Functions in SQL are similar to Procedures, except that all parameters are inputs, and there is a defined singular output. Functions are also restricted from modifying the database state (a restriction not imposed on procedures - thus procedures can not be called from within functions, but the reverse is allowed). Notice with the get_customer_balance function, the use of variables using the DECLARE statement, which is then used to allow for three very different queries to be executed and their results combined for the end result of the function.

Trigger Notes

Triggers in SQL are actions that can be taken before or after various SQL queries are performed. The trigger effectively watches for various mutation operations (INSERT, UPDATE, DELETE) and can perform some action BEFORE or AFTER the query is executed. In the case of payment_date, it is set to execute BEFORE INSERT actions. The syntax of triggers themselves is somewhat complex, as you are able to execute actions on a row-by-row basis within the set of rows being mutated. In our example trigger, we see the payment_date field being adjusted to the value of NOW(), instead of the value that it was originally given. The keywords FOR EACH ROW telling the DBMS to perform the SET operation on each row being inserted.

Queries

Q1: Create a list of each customers first and last name and their balance as of today. Sort the list by the customers balance so that those with outstanding balances appear at the top. Include the top 5 results in your write-up. New Operations

O1: Create a way to determine the number of films in stock at each store, with a single query by the user. The result should be a table of store ids and the number of films in stock.

O2: Create a way to determine the number of films that an actor is that are contained in this database. The result should be a single integer.

O3: Create a way to update a staff members password when their username is modified. The staff members password should be the result of MD5(username)

What to Turn In

You should turn in a document that includes the SQL and results for the queries that you built and executed, as well as the written portions of the assignment. It should all be in one file, and formatted in a way that is easy to read and understand (i.e. label what question you are answering with each query)

Snapshot Details Routine name Film_in_stock Type PROCEDURE Direction Name Length/Values Options Type INT IN p_film_id V Drop Parameters IN p_store_id INT Drop OUT v p_film_co INT V Drop Add parameter 4 6 1 BEGIN SELECT inventory_id 3 FROM inventory WHERE filmid = p_film_id 5 AND store_id = p_store_id AND inventory_in_stock/inventory_id); 7 SELECT COUNT(*) 9 FROM inventory 10 WHERE film_id = p_film_id 11 AND store_id = p store id 12 AND inventory_in_stock/inventory_id) 13 INTO p_film_count; 14 END 8 Definition Is deterministic Definer root@localhost Security type DEFINER SQL data access READS SQL DATA Comment Routine name get_customer_balance Type FUNCTION Name Type Length/Values Options Parameters p_customer INT Drop DATETIME --- p_effective Drop Add parameter DECIMAL Return type Return length/values Return options 5,2 BEGIN #OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER ID AND A DATE #THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS: # 1) RENTAL FEES FOR ALL PREVIOUS RENTALS # 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE # 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 DECLARE v_rentfees DECIMAL (5,2); #FEES PAID TO RENT THE VIDEOS INITIALLY DECLARE v_overfees INTEGER; #LATE FEES FOR PRIOR RENTALS DECLARE v payments DECIMAL (5,2); #SUM OF PAYMENTS MADE PREVIOUSLY SELECT IFNULL (SUM(film.rental_rate), 0) INTO V_rentfees FROM film, inventory, rental WHERE film.film_id = inventory.film_id AND inventory. inventory_id = rental inventory_id AND rental.rental_date film.rental_duration, ((TO_DAYS (rental.return_date) - TO_DAYS (rental.rental_date)) - film.rental duration), 0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date film.rental_duration, ((TO_DAYS (rental.return_date) - TO_DAYS (rental.rental_date)) - film.rental duration), 0)),0) INTO v_overfees FROM rental, inventory, film WHERE film.film_id = inventory.film_id AND inventory.inventory_id = rental.inventory_id AND rental.rental_date