CPSC431 Lecture 11

1. CPSC431 Lecture 11

2. Introducing Triggers • Taking Action Before an Event Shopping cart insertion request submitted: If product identifier set to "coffee": If dollar amount < $10: Set dollar amount = $10; End If End If Allow insertion request to process

3. Introducing Triggers • Taking Action After an Event For purposes of example, assume that the technician table looks like this: +----+---------+---------------------------+-----------+ | tID| name | email | available | +----+---------+---------------------------+-----------+ | 1 | Jason | jason@example.com | 1 | | 2 | Robert | robert@example.com | 1 | | 3 | Matt | matt@example.com | 1 | +----+---------+---------------------------+-----------+

4. Introducing Triggers The ticket table looks like this: +-------+-----------+--------------------+---------------------+----+ |rowID| username| title | description |tID | +-------+-----------+---------------------+---------------------+---+ | 1 | smith22 | disk drive |Disk stuck in drive| 1 | | 2 | gilroy4 | broken keyboard | Enter key is stuck | 1 | | 3 | cornell15 | login problems | Forgot password | 3 | | 4 | mills443 | login issues | forgot username | 2 | +-------+------------+--------------------+---------------------+---+ Technician table update request submitted: If available column set to 0: Update helpdesk ticket table, setting any flag assigned to the technician back to the general pool. End If

5. Introducing Triggers • Creating a trigger CREATE TRIGGER <trigger name> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <table name> FOR EACH ROW <triggered SQL statement> DELIMITER // CREATE TRIGGER au_reassign_ticket AFTER UPDATE ON technician FOR EACH ROW BEGIN IF NEW.available = 0 THEN UPDATE ticket SET technicianID=0 WHERE technicianID=NEW.technicianID; END IF; END;//

6. Introducing Triggers • TRIGGER NAMING CONVENTIONS • ad: Execute trigger after a DELETE query has taken place. • ai: Execute trigger after an INSERT query has taken place. • au: Execute trigger after an UPDATE query has taken place. • bd: Execute trigger before a DELETE query has taken place. • bi: Execute trigger before an INSERT query has taken place. • bu: Execute trigger before an UPDATE query has taken place.

7. Introducing Triggers • Viewing Existing Triggers mysql>SHOW TRIGGERS\G *************************** 1. row *************************** Trigger: au_reassign_ticket Event: UPDATE Table: technician Statement: begin if NEW.available = 0 THEN update ticket set technicianID=0 where technicianID=NEW.technicianID; END IF; end Timing: AFTER Created: NULL sql_mode:

8. Introducing Triggers • Integrating Triggers into Web Applications <?php $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk"); $name = $_POST['name']; $email = $_POST['email']; $available = $_POST['available']; $technicianid = $_POST['technicianid']; $query = "UPDATE technician SET name='$name', email='$email', available='$available' WHERE technicianID='$technicianid'"; if ($mysqli->query($query)) { echo "<p>Thank you for updating your profile.</p>"; if ($available == 0) { echo "<p>Because you'll be out of the office, your tickets will be reassigned to another technician.</p>"; } } else { echo "<p>There was a problem updating your profile. Please contact the administrator.</p>"; } ?>

9. Views • Creating and Executing Views CREATE TABLE employee ( rowID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, employeeID CHAR(8) NOT NULL, first_name VARCHAR(25) NOT NULL, last_name VARCHAR(35) NOT NULL, email VARCHAR(55) NOT NULL, phone CHAR(10) NOT NULL, salary DECIMAL(8,2) NOT NULL, PRIMARY KEY(rowID) ); CREATE VIEW employee_contact_info_view AS SELECT first_name, last_name, email, phone FROM employee ORDER BY last_name ASC; SELECT * FROM employee_contact_info_view;

10. Views • Modifying the Returned Column Names CREATE VIEW employee_contact_info_view ('First Name', 'Last Name', 'Email Address', 'Telephone') AS SELECT first_name, last_name, email, phone FROM employee ORDER BY last_name ASC

11. Views • Show views SHOW CREATE VIEW employee_contact_info_view; This produces the following output (slightly modified for readability): ********************** 1. row ************************** View: employee_contact_info_view Create View: CREATE ALGORITHM=UNDEFINED VIEW 'corporate'.'employee_contact_info_view' AS select 'corporate'.'employee'.'first_name' AS 'first_name', 'corporate'.'employee'.'last_name' AS 'last_name', 'corporate'.'employee'.'email' AS 'email', 'corporate'.'employee'.'phone' AS 'phone‘ f rom 'corporate'.'employee' order by 'corporate'.'employee'.'last_name'

12. Views • Incorporating Views into Web Applications <?php $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk"); $query = "SELECT * FROM employee_contact_info_view"; if ($result = $mysqli->query($query)) { echo "<table border='1'>"; echo "<tr>"; $fields = $result->fetch_fields(); foreach ($fields as $field) echo "<th>".$field->name."</th>"; echo "</tr>"; while ($employee = $result->fetch_row()) { $first_name = $employee[0]; $last_name = $employee[1]; $email = $employee[2]; $phone = $employee[3]; $phone = ereg_replace("([0-9]{3})([0-9]{3})([0-9]{4})", "(\\1) \\2-\\3", $phone); echo "<tr>"; echo "<td>$first_name</td><td>$last_name</td>"; echo "<td>$email</td><td>$phone</td>"; echo "</tr>"; } } ?>

13. Practical Database Queries • Sample Data CREATE TABLE vehicle ( vehicleID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(25) NOT NULL, description VARCHAR(100), PRIMARY KEY(vehicleID)); CREATE TABLE member_to_vehicle ( mapID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, memberID SMALLINT UNSIGNED NOT NULL, vehicleID TINYINT UNSIGNED NOT NULL, PRIMARY KEY(mapID));

14. Practical Database Queries • Subqueries SELECT memberID, first_name, last_name FROM member WHERE zip = (SELECT zip FROM member WHERE memberID=1); • Performing Comparisons with Subqueries SELECT first_name, last_name FROM member WHERE daily_mileage > (SELECT AVG(daily_mileage) FROM member); • Determine existance SELECT DISTINCT memberID, first_name, last_name FROM member WHERE EXISTS (SELECT * from member_to_vehicle WHERE member_to_vehicle.memberID = member.memberID); SELECT memberID, first_name, last_name FROM member WHERE memberID IN (SELECT memberID FROM member_to_vehicle);

15. Practical Database Queries SELECT DISTINCT memberID, first_name, last_name FROM member WHERE NOT EXISTS (SELECT * from member_to_vehicle WHERE member_to_vehicle.memberID = member.memberID); SELECT memberID, first_name, last_name FROM member WHERE memberID <> ALL (SELECT memberID FROM member_to_vehicle);

16. Practical Database Queries • Using Subqueries with PHP <?php $mysqli = new corporate_mysqli("localhost", "websiteuser", "secret", "corporate"); $query = "SELECT memberID, first_name, last_name FROM member WHERE zip = (SELECT zip FROM member WHERE memberID=1)"; // ouput the results $mysqli->close(); ?>

17. Practical Database Queries • Cursor Basics • Declare the cursor with the DECLARE statement. • Open the cursor with the OPEN statement. • Fetch data from the cursor with the FETCH statement. • Close the cursor with the CLOSE statement.

18. Practical Database Queries • Creating a Cursor DECLARE calc_bonus CURSOR FOR SELECT employeeID, salary, commission FROM employee; • Opening a Cursor OPEN calc_bonus; • Using a Cursor FETCH calc_bonus INTO empID, sal, comm; • Closing a Cursor CLOSE calc_bonus;

19. Practical Database Queries • Cursor example DELIMITER // CREATE PROCEDURE calculate_bonus() BEGIN DECLARE empID INT; DECLARE sal DECIMAL(8,2); DECLARE comm (3,2); DECLARE calc_bonus CURSOR FOR SELECT employeeID, salary, commission FROM employee; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN calc_bonus; begin_calc: LOOP FETCH calc_bonus INTO empID, sal, comm; IF done THEN LEAVE begin_calc; END IF; IF sal > 60000.00 THEN IF comm > 0.05 THEN UPDATE employee SET bonus = sal * comm WHERE employeeID=empID; ELSEIF comm <= 0.05 THEN UPDATE employee SET bonus = sal * 0.03 WHERE employeeID=empID; END IF; ELSE UPDATE employee SET bonus = sal * 0.07 WHERE employeeID=empID; END IF; END LOOP begin_calc; CLOSE calc_bonus; END// DELIMITER ;

20. Practical Database Queries • Using Cursors with PHP <?php // Instantiate the mysqli class $db = new mysqli("localhost", "websiteuser", "secret", "corporate"); // Execute the stored procedure $result = $db->query("CALL calculate_bonus()"); ?>