Hey everybody, today I need to explain triggers. When an event happens, a trigger does something. I know that sounds very generic. For example, when we insert, update, or delete a record, we can use a trigger to check data, handle errors, or audit tables. We can do a plethora of things.
Here's an example. I have a table of employees. We have an hourly pay column. Maybe I would like a salary column.
Whenever we add an employee or update our hourly pay, the employee's salary will be changed automatically with the trigger. Before creating a trigger, let's update our employees table. We'll add a salary column.
Alter, table, employees, add, column. Salary, the data type will be decimal, 10 digits, precision of 2. We will place this column after the hourly pay column. Then select all from our employees table. Let's see what we have.
There we are. There's our salary column. Let's update our employees table.
Set our salary column equal to the hourly pay times. To calculate an employee's salary using an hourly pay, Sheldon Plankton for example, take the hourly pay, $10 per hour, There's typically 40 hours in a work week, then 52 weeks in a year. Plankton would have a salary of $20,800.
So a shortcut, you could just take the hourly pay times 2,080. There are 2,080 work hours in a typical year. So take the hourly pay times 2,080. Then we will select all from employees. There is everybody's salary currently.
Whenever we update an employee's hourly pay, I would like to also update the salary automatically, with the trigger. I don't want to have to calculate every employee's salary manually. You know, using a calculator, like I'd- just did.
We're going to create a trigger. Create trigger. Then we need a name for this trigger.
Before we update the hourly pay, we're going to do something. I will name this trigger before hourly pay update. We'll use either the keyword before or after.
Do you want to do something before or after? I would like to do something before. Before what? Before an insert, before a delete, before an update. Before an update in this case.
On our table, employees. Then add this line, for each row. We may be working with more than one row using a trigger, so that's why we're including this line.
This trigger can involve one or more rows. Now what are we going to do before we update? an employee's hourly pay.
Let's set the salary equal to the hourly pay times 2080 because there's 2080 work hours in a typical year. Okay, there's one more thing I'm going to add. Let's prefix salary and hourly pay with this new keyword. New dot salary new dot hourly pay. MySQL doesn't know if we're referring to the old salary and the old hourly pay.
We're telling MySQL we're calculating a new salary. Use the new hourly pay when that's changed in place of the old one. Okay, let's take a look.
Let's execute this statement. Our trigger has been created. In your schemas window to find any triggers go to tables, then find that table where we applied the trigger.
Employees, then triggers. I probably need to refresh this. There it is.
Before hourly pay update. Otherwise you can type show triggers. And here is our trigger. Here's the trigger name, event update, the table, the statement, and the timing.
Let's select all from employees. Mr. Krabs is going to give himself an hourly pay raise because he's greedy. Let's update our employees table.
Set the hourly pay equal to $50 per hour, where our employee ID equals 1. So Mr. Crab's salary is about $53,000 per year. Before we update it, we will change the salary of Mr. Crab. this employee from $53,000 to over six figures $104,000. That change happened automatically with the trigger. Before updating the employee's hourly pay, we calculated a new salary automatically.
Let's make another change. Let's increase every employee's hourly pay by $1 per hour. Mr. Krabs is now feeling generous. Update employees.
We will set the hourly pay equal to whatever the hourly pay currently is plus one. We will apply this for every employee. We don't need a where clause.
There, everybody is now making one more dollar per hour and the salaries reflect that. Let's create a new trigger. We will calculate a salary whenever we insert a new employee. More specifically, whenever we insert a new hourly pay. Right now, we have a trigger that only kicks in when we update an hourly pay.
Okay, let's delete Plankton. Plankton got fired. Delete from employees.
where employee ID equals 6. Select all from employees. Their plankton is now gone. Let's create a trigger. Create trigger before hourly pay insert.
Are we doing something after or before? We're doing something before an event. Before, are we updating, inserting, or deleting? We are inserting. Before we insert a new record on our table employees for each row, let's set the new salary equal to The new hourly pay times 2080. Here is our new trigger.
Let's take a look at our employees table. Select all from employees. Let's insert a new employee.
Insert into employees values. I'm just going to follow these columns. Employee ID 6. First name is Sheldon.
Last name Plankton. Hourly pay is $10 per hour. For the salary, I'm going to set this to null.
Our trigger will kick in and set that. Position. Janitor.
Hire date 2023 January 7th. Supervisor ID of 5. Let's see if our salary is calculated. It was. Plankton's hourly pay is $10 per hour, meaning he has a salary of $20,800 per year.
I do have a few more examples. Triggers are something you'll want to master. They're really helpful.
We're going to create a new table this time. create table expenses our expenses table will have three columns an expense ID. The data type will be int.
This will be the primary key. You could set this column to auto increment if you want, but that's overkill for this topic, I would say. I'm going to try and keep it simple. Expense name. The data type is varchar.
50 is good, I suppose. Then expense total. I will set that to be decimal. 10 digits, precision of 2 is fine.
Then let's select everything from our table, expenses. Let's insert some values. Insert into expenses.
We have three rows. One, two, three. ID of 1. The expense name is salaries.
For the value for now I'm going to set that to be zero. The second row will be two. Supplies, zero. ID of three.
Maybe taxes. And zero. I will calculate the expense total of my salaries row. Update expenses.
I will set the expense total equal to, I'll use a nested query, select the sum of salary from employees where our expense name column right here, and then I'll set the expense total to $100. is equal to salaries. This is all of these salaries combined from the employees table.
The total is $254,280. Whenever we delete an employee, we will update this value found within another table, our expenses table. So let's create a trigger. Create trigger after salary delete. Are we doing something before or after?
We're doing something after deleting a salary. After delete on employees. For each row, we will update our expenses table.
and set the expense total equal to whatever the expense total currently is minus that employee's salary. If we're deleting a salary, we will refer to that salary as the old salary. Then add a where clause. Where expense name equals salaries.
Okay, we have created our trigger. Let's take a look at our expenses table. I'm going to delete plankton again. He's fired.
Delete from employees where employee ID equals 6, that's plankton. Then select all from expenses. Look at that, our expense total changed. It's now $233,000.
Let's create a trigger that will update our salaries whenever we insert a new employee. create trigger after salary insert. We're doing something after this time.
After an insert on our table employees for each row we will update Our expenses table, we will set the expense total equal to the expense total plus the employees new salary, new dot salary, where expense name equals salaries. Let's insert a new employee. Let's take a look at our expenses table.
Select all from expenses. We will insert a new employee. Insert into employees. Our values.
Employee ID 6. First name is Sheldon. Last name Plankton. Hourly pay of ten dollars per hour. The salary will be null.
That will be calculated automatically with the trigger. Position is janitor, hire date 2023-01-07, and a supervisor ID of 5. Let's see if this expense total will change. which it does. When we insert a new employee, more specifically a salary, our expense total of salaries will be updated. One last example then I promise we're done.
Let's create a trigger that will update this value when we change an employee's salary, and that will probably be the most complicated one. Let's create a trigger after salary update. We're doing something after we update. On our table Employees, for each row, let's update Expenses, set the ExpenseTotal equal to the ExpenseTotal plus The employee's new salary minus the employee's old salary. What's the difference?
We'll find the difference between the employee's new salary and their old salary. Add that to the expense total. Where expense name equals salaries.
We have created our trigger. Let's select all from expenses. Mr. Krabs is going to give himself another pay raise. Update employees. Set the hourly pay of Mr. Krabs to $100 per hour, where employee ID equals 1. Our expense total underneath salaries is now changed.
It's now $356,200. Well, all right, everybody, that's a trigger. In simple words, when an event happens, do something, such as when we insert, update, or delete something from a table. A few of the benefits is that you can check data, handle errors, and audit tables. It's a really helpful tool to have.
And well, everybody, those are triggers in MySQL.