Week 10 - Triggers, Conditionals & Loops

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

Triggers

What is a trigger?

Triggers are a script we write that fires BEFORE or AFTER a DML event (INSERT, UPDATE, DELETE)

Some of you are probably asking - what is a script? A script is some code we can write to get the database to do stuff, like performing an action or returning a value.

So, very simply, we're going to tell the database management system:

When a row gets inserted*, afterwards** do this stuff***.

Now, it's not very precise to say that triggers do stuff, but that's because they're extremely flexible!

We will not be learning how to do every possible thing you can do with a trigger today, as we'll be talking more about programming in SQL as we go, but, if next week you find yourself thinking, "I wish this stored procedure would automatically happen whenever someone did something with the data", you can come back to your trigger syntax!

When not to use triggers

Triggers have a bad reputation. This is because they can be written poorly, and when they're written poorly, they can quietly do things without telling anyone, making them a potential source of a lot of frustration.

Don't use triggers if:

• There is a more narrowly defined tool for your use-case (i.e. the CHECK constraint in MySQL 8+, or other RDBMSs). Triggers are a Swiss army knife, whereas other tools are chef's knives. Triggers can do many things, but they might not be the strongest or easiest tool for a particular job.
• The trigger you're creating doesn't provide any feedback to the database user, and you're not entirely sure that won't be an issue. Triggers can do things quietly, without a "paper trail", unless you make one. Don't make sneaky triggers.
• Your database will grow large, and your triggers might be better off as "options" to include in your code (as a stored procedure or function instead of a trigger) rather than being automatic for any DML operation.

When to use triggers

• Logging database activity

  Despite their reputation, pretty much everyone agrees that triggers are good at keeping a log of database activity. For this use-case, triggers are efficient, unobtrusive, and helpful.

• Strict enforcement of data-logic (with good error messages!)

  There are good reasons to separate your business logic out from your SQL. However, if there are rules pertinent to your database, and you want to give the user clear error messages, triggers can be a good option!

Creating a logging trigger

Let's create a trigger that will log changes on one of our tables.

We'll store the log in a table we create just for the log! Naturally, we need to create this table before we log anything:

CREATE TABLE data_log (
  logId INT PRIMARY KEY AUTO_INCREMENT,
  action  VARCHAR(255),
  author_id   INT,
  last_name_old VARCHAR(255),
  last_name_new VARCHAR(255),
  timestamp TIMESTAMP
);

This table has columns where we can record the action being taken, the id of the row(s) being updated, the old data, the new data, and a timestamp to record when the update was made.

Okay, first off we have this DELIMITER command.

So that's why we set a delimiter to something other than a semicolon at the beginning of our CREATE TRIGGER statement, use the custom delimiter at the end of the statement, and then set the delimiter back afterwards, so that life can go on as normal.

-- Set the delimiter to something 
-- other than a semicolon
DELIMITER //
CREATE TRIGGER {triggerName} 
BEFORE INSERT ON {tableName}
FOR EACH ROW
BEGIN
-- Do stuff
END; 
-- Use our custom delimiter ('//' in this case)
-- to tell the RDBMS that we're done creating 
-- the trigger.
//
-- Set the delimiter back to being a semicolon.
DELIMITER ;

CREATE TRIGGER log_author_updates 
  AFTER UPDATE ON authors ...

As we talked about, we're going to use the CREATE TRIGGER command, and then give our trigger a name. In this case, our trigger is called "log_author_updates".

Then we say that our trigger will run after (not before) there is an update to the authors table.

Why after and not before? Well, in this instance, we've decided that we only want to record these updates if we're sure the update has actually happened. If someone tries to update the authors' table, but there's an error, we will not log that in our log_author_updates table. We're deciding here that we only want to log successful updates.

... FOR EACH ROW ...

This line essentially says, "after this comes the stuff I want you to do for each row that is affected by the DML statement (in this case, for every row that gets updated)".

... BEGIN ...

Technically, you could omit BEGIN and END from this particular trigger. BEGIN and END allow you to use multiple commands in your trigger.

... END;

And we're done!

Throwing an error with a trigger

Before I show you how to create a custom data constraint by throwing an error with a trigger, let's chat for a second about business logic.

As web developers, you know that there's a lot of overlap between stuff you can do in your front-end code, your back-end code, and your database. Nowhere is this more true than when you're validating data.

Want to give your users the fastest possible feedback when they're filling out a form? Use client-side validation Opens in a new window.

Want to keep your server safe, and your application making sense? Use server-side validation Opens in a new window.

Naturally, we've been talking a lot about how to make sure that only the right data ends up in your database, using everything from table structure, to data types, to constraints.

But 'right' according to whom?

There's a bunch of stuff to consider when deciding what counts as the "right data" - security, maintainability, and the purpose of the application.

Requirements generated by the purpose of the application (say, selling t-shirts or lending library books) are referred to as "business logic". While there's bound to be grey areas, it's probably best to keep as much business logic as possible in one place. Business logic is much more likely to change than any other requirements, and it's always easier to change things in one spot, rather than having that logic spread out across your technology stack over different languages and servers.

Business logic usually ends up encoded in your server-side language, where it is secure, but easy to update.

We're just about to learn how to define an error in SQL. But an error according to whom?

If the error you create is not based on database concerns, like the safety, security and logic of the data, but rather based on business logic, like "we don't stock t-shirts in that size", then maybe that error should be defined where you are storing your business logic, likely in your server-side code.

Okay, what are we doing here?

As you can see, we're creating a trigger called 'max_renewals' that fires before there's an update on the withdrawals table. For each affected row, we're going to do some stuff, as described between the words BEGIN and END.

Then we get to that magical work, IF.

Hopefully I'm not the first prof to describe to you how an 'if statement' works in programming - it's pretty much the same across all the programming languages you'll learn in this program, including Javascript and PHP.

But in case you need a refresher, or you just want to see the SQL-specific syntax, it goes like this:

IF {condition}
  THEN {action}
ELSEIF {condition}
  THEN {action}
ELSE {action}
END IF;

In other words, IF if something is true, THEN do a thing. ELSEIF if it's not true, but this other thing is true, THEN do this other thing. ELSE if none of those things are true, do this thing. END IF: stop worrying about this logic, and go ahead with the rest of your code.

Note that the ELSEIF and ELSE statements are optional - you don't need them in there if you just want to write an IF statement that checks if one thing is true, and doesn't do anything if it's not true.

...IF (NEW.renewals > 2)...

So our IF condition checks to see if the new value that the update statement is trying to add is greater than 2.

The SQLSTATE defines whether the thing you're trying to do has succeeded or failed. We're setting the value to '45000', because that's the code for a user-defined error.

There are a lot Opens in a new window of SQLSTATE codes out there, but this is the one to use when you're defining an error that wouldn't otherwise be an error.

Then we get to set the text of the message that the database returns.

Once you set the SQLSTATE to an error state, this will stop the execution of any code, meaning our trigger will prevent an update that matches our condition.

Conditions & Loops

Conditions and loops are logic you can create and use in your triggers, custom functions, and (starting next week) your stored procedures!

If statements

IF {condition}
  THEN {action}
ELSEIF {condition}
  THEN {action}
ELSE {action}
END IF;

In other words, IF if something is true, THEN do a thing. ELSEIF if it's not true, but this other thing is true, THEN do this other thing. ELSE if none of those things are true, do this thing. END IF stop worrying about this logic, and go ahead with the rest of your code.

ELSEIF and ELSE statements are optional - you don't need them in there if you just want to write an IF statement that checks if one thing is true, and doesn't do anything if it's not true.

What about case statements?

If you're already programming in another language, you've probably come across 'case statements'. If you want to use them in your SQL functions and procedures, go nuts Opens in a new window.

Those of you who are comfortable with them already, they work the same as they do in other languages - you'll just need to look up the syntax.

Those of you who haven't used them before - case statements are used to handle complex if/elseif structures, but you can accomplish all the same stuff with your if statements, so I thought, in the interests of keeping this lesson short, I'd skip the case statement syntax. If it's something you're really keen on, though, let me know!

What about cursors?

You may have heard about cursors - these handy tools let us create a select statement, and then do a thing for each row in the results. And they're great! Just like if statements and while loops, they're things we can use in triggers, functions and stored procedures alike. But I feel like I'm throwing enough at you today, so I'm going to save them for another week (if we have time).

while loops

Loops they... well, they loop! They repeatedly run code, and you're responsible for telling them when to stop. Far and away the most common pattern for doing this is by saying the following:

The idea is, if you do "something" enough times, and keep adding 1 to x every time you do it, x will eventually equal y, at which point the loop stops.

Notice that if x were less than y, and you, say, subtracted by one every time, x would never be greater than or equal to y, meaning the condition for stopping would never be met, and something would keep getting done forever. Infinite loops are not something I recommend.

Here's the syntax for a WHILE loop in SQL:

WHILE {condition} DO
{do stuff}
END WHILE;

Here's the syntax for a WHILE loop with our handy iterator pattern in SQL:

DECLARE x INT DEFAULT 0;
WHILE x < 10 DO
-- do stuff
SET x = x + 1;
END WHILE;

Okay, let's look at a dumb version of this:

DELIMITER //
CREATE FUNCTION countToTen()
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
-- Declare a variable that we'll 
-- keep adding +1 to. 
  DECLARE x INT DEFAULT 1;
-- Declare a variable that we can 
--  write our string into.
  DECLARE myString VARCHAR(255) DEFAULT '';
-- Keep doing the stuff in the loop as 
-- long as x is less than or equal to 10
  WHILE x <= 10 DO
-- Set the text in our myString variable. 
-- The text will be whatever's already in the 
-- myString variable, plus a space, plus 
-- whatever number has been counted to 
-- in the x variable.
    SET myString = CONCAT(myString, ' ', x);
-- Add 1 to whatever the value of x is.
    SET x = x + 1;
-- End the loop. This only happens when the 
-- loop can't do the stuff after DO anymore, 
-- because the condition for the WHILE loop 
-- is no longer true.
  END WHILE;
-- Return the value saved into 
-- the myString variable.
  RETURN myString;
END; //
DELIMITER ;

Alright, now, here's a much more fleshed out example of a function using a WHILE statement. See if it all makes sense!

DELIMITER //
CREATE FUNCTION bto ( 
  buhs  INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  DECLARE iter8 INT DEFAULT 0;
-- Note: you can't declare defaults 
-- for parameters, but you can declare 
-- defaults for internal variables.
  DECLARE lyric VARCHAR(255) 
    DEFAULT 'You ain''t seen nuthin'' yet ';
-- Throw an error if our string is gonna 
-- go over 255 characters.
  IF (buhs > 55)
    THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 
        'Maximum number of buhs is 55';
  END IF;
-- Start our loop
  WHILE iter8 <= buhs DO
    IF (iter8 = buhs)
      THEN SET lyric = CONCAT(lyric, 'baby!');
    ELSE 
      SET lyric = CONCAT(lyric, 'buh-');
    END IF;
    SET iter8 = iter8 + 1;
  END WHILE;
 RETURN lyric;
END; //
DELIMITER ;