MySQL: TRIGGERS

#MySQL #tutorial #course

00:00:00 intro
00:00:33 setup part 1
00:02:01 BEFORE UPDATE
00:05:45 BEFORE INSERT
00:08:14 setup part 2
00:10:45 AFTER DELETE
00:12:37 AFTER INSERT
00:14:34 AFTER UPDATE
00:16:25 conclusion

CREATE TRIGGER before_hourly_pay_update
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);

CREATE TRIGGER before_hourly_pay_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);

CREATE TRIGGER after_salary_delete
AFTER DELETE ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total – OLD.salary
WHERE expense_name = “salaries”;

CREATE TRIGGER after_salary_insert
AFTER INSERT ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + NEW.salary
WHERE expense_name = “salaries”;

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + (NEW.salary – OLD.salary)
WHERE expense_name = “salaries”;