Lecture on Triggers in MySQL

Introduction to Triggers

• Definition: Triggers perform actions automatically in response to certain events on a table (e.g., insert, update, delete).
• Usage: Used to check data, handle errors, audit tables, etc.

Creating and Using Triggers

Example 1: Employee Salary Calculation

• Scenario: Automatically calculate and update employee salary based on hourly pay.
• Steps:
  1. Modify Table: Add salary column to the employees table.
     • SQL Command: ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2) AFTER hourly_pay;
  2. Initial Calculation: Set salary = hourly_pay * 2080 (since there are 2080 work hours per year).
     • Example Calculation: $10/hour results in $20,800/year.
  3. Create Trigger: Automatically update salary when hourly_pay is updated.
     • Trigger Name: before_hourly_pay_update
     • Action: Before updating hourly_pay, set new.salary = new.hourly_pay * 2080.
  4. Testing: Update employee's hourly_pay and observe automatic salary update.

Example 2: Insert Trigger for New Employees

• Objective: Calculate salary automatically upon inserting a new employee record.
• Trigger Name: before_hourly_pay_insert
• Action: Before inserting, set new.salary = new.hourly_pay * 2080.
• Test: Insert new employee and check if salary is set correctly.*

Additional Examples

Example 3: Managing Expenses with Triggers

• Scenario: Update expenses when employee data changes.
• Create Table: expenses with columns expense_id, expense_name, expense_total.
• Trigger for Deletion:
  • Name: after_salary_delete
  • Action: After deleting an employee, reduce expense_total by old.salary where expense_name = 'salaries'.
• Trigger for Insertion:
  • Name: after_salary_insert
  • Action: After inserting a new employee, increase expense_total by new.salary.
• Trigger for Updates:
  • Name: after_salary_update
  • Action: After updating salary, adjust expense_total by the difference between new.salary and old.salary.

Summary

• Main Points:
  • Event-driven execution: Triggers respond automatically to table events.
  • Benefits: Simplifies complex updates, maintains consistency across related tables, facilitates auditing.
  • Triggers are powerful tools for automating database workflows in MySQL.