Show navigation

Week 10 - Triggers, Conditionals & Loops

This week we'll look at how to create triggers, and how to use conditions and loops in MySQL.

  1. Triggers
    1. What is a trigger?
    2. When not to use triggers
    3. When to use triggers
    4. Creating a trigger
    5. Creating a logging trigger
    6. Throwing an error
  2. Conditions & Loops
    1. If statements
    2. while loops
  3. Exercises

Hopefully this helps some people?

For some of you, today's lesson will feel like you're getting thrown in the deep end, particularly if you don't have any experience with programming concepts like IF statements or WHILE loops.

Maybe this will help a little, though. I'm not going to explain these code blocks during the lesson, but we will learn everything today that is required to understand these code blocks.

If you're the type of person who likes to learn by copy+pasting code and then tinkering with it until it works, here are some code snippets (feel free to delete the comments when you work with them):

Custom function (basic)

DELIMITER //
CREATE FUNCTION capitalize (
// Input variable name and data type
word VARCHAR(255)
)
// Declare data type of return value
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
// Local variable name and data type
DECLARE outputWord VARCHAR(255); 
// Set the value of the variable
SET outputWord = CONCAT(UPPER(SUBSTR(word, 1, 1)), SUBSTR(word, 2));
// Return the variable now that it has
// a value assigned.
RETURN outputWord;
END; //
DELIMITER ;

Custom function (with IF and WHILE)

DELIMITER //
CREATE FUNCTION swearGenerator (
startingLetter VARCHAR(2), 
letters INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE itr INT DEFAULT 0;
DECLARE blockString VARCHAR(64) 
  DEFAULT startingLetter;
// Start if with condition
IF (letters > 12)
// do stuff if condition is true
  THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 
      'Don`t be greedy, max is 12.';
// Stop doing conditional stuff
END IF;
// Do stuff as long as condition is true
WHILE itr <= letters DO
  SET blockString = CONCAT(blockString, '*');
  // Change the value so condition will 
  // eventually be false
  SET itr = itr + 1;
END WHILE;
RETURN blockString;
END; //
DELIMITER ;

Throwing an error with a trigger

Always before
-- Tell MySQL "I'm not done until you see //"
DELIMITER //
-- Give your trigger a name
CREATE TRIGGER no_weird_characters 
-- Specify before or after, and the DML command and table
-- that will trigger this trigger
BEFORE INSERT ON members
FOR EACH ROW
BEGIN
-- Create condition that will throw the error
IF (CONCAT(NEW.first_name, NEW.last_name) LIKE '%>%')
  THEN
  -- Set the error message
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'The `<` character is not allowed in first or last names';
END IF;
END; //
-- Reset delimiter
DELIMITER ;

Logging changes with a log table and a trigger

Always after, and create a table in which to store the log
CREATE TABLE member_log (
logId INT PRIMARY KEY AUTO_INCREMENT,
action  VARCHAR(255),
member_id   INT,
-- Add more sets of columns to capture data 
-- besides just first name
first_name_old VARCHAR(255),
first_name_new VARCHAR(255),
timestamp TIMESTAMP
);
DELIMITER //
CREATE TRIGGER log_member_updates
AFTER UPDATE ON members
FOR EACH ROW
BEGIN
  INSERT INTO member_log 
    (action, member_id, first_name_new, first_name_old, timestamp)
  VALUES
    ('update', NEW.member_id, NEW.first_name, OLD.first_name, NOW());
END; //
DELIMITER ;
This trigger captures updates. To capture inserts and deletions, create two more triggers. You can still log the changes to the same member_log table you're using for updates, just change the hard-coded 'action' value so you can tell them apart.
... ('update', NEW.author_id, NEW.last_name, OLD.last_name, NOW()); ...

Exercises

No lab work this week - but you can get started on your proposal, now that you know how to create triggers and functions. Reminder that your final assignments needs a trigger, plus either a function or a stored procedure (which we'll learn about next week).

In order to complete your final assignment, though, you'll need a solid understanding of how triggers, functions and stored procedures work. The exercises below will give you a refresh on creating normalized tables, and some practice creating triggers. Next week we'll practice creating functions and stored procedures.

  1. Let's take another crack at the tables we created last week. If you need to re-create them, or just refresh your memory, here is the data:

At a track and field competition, there are 6 athletes: Dorothy Dietrich, Fay Presto, Doug Henning, Ellen Armstrong, Edgar Cayce, and Howard Thurston.

There are three events: the javelin throw at 10am, the 100m race at 1pm, and the pole vault at 3pm.

Create a table to represent the athletes, and a table to represent the events. Create a third table called 'results', which shows the following:

In the javelin throw, Dorothy Dietrich won gold, Doug Henning won silver, and Howard Thurston won bronze.

In the 100m race, Fay Presto won gold, Ellen Armstrong won silver, and Doug Henning won bronze.

In the pole vault, Ellen Armstrong won gold, Doug Henning won silver, and Dorothy Dietrich won bronze.

Note: there is no record of who participated in a given event, only who won a medal.

  1. Ensure that the 3 tables have appropriate data types assigned to each column.
  2. Ensure that the 3 tables have appropriate foreign keys.
  3. Create a single table for logging changes to the database. Note: you'll probably want to read questions 5-7 before deciding how to design this table.
  4. Create a trigger that logs data inserted into the results table.
  5. Create a trigger that logs data updated in the results table (including both old values and new values).
  6. Create a trigger that logs data deleted from the results table.
  7. Create a trigger that will throw an error if someone tries to insert a result where the medal is not gold, silver or bronze. Remember that you can check if a value is not in a list with the operator NOT IN.
TriggersConditions & LoopsIf statementswhile loopsExercises