Triggers in MySQL

 

CREATION AND WORKING OF TRIGGERS IN MYSQL

---

1. AIM

To understand the concept of Triggers in SQL and to create various types of triggers in MySQL.

---

2. THEORY

✔ What is a Trigger?

A trigger is a stored program that is automatically executed (fires) when a specific event occurs on a table.

Triggers work on:

• INSERT

• UPDATE

• DELETE

Triggers can fire:

• BEFORE

• AFTER

---

✔ Why Use Triggers?

• Enforce business rules

• Maintain audit logs

• Automatically update values

• Prevent invalid data modification

• Maintain referential integrity

---

✔ Trigger Syntax (MySQL)

DELIMITER $$

CREATE TRIGGER trigger_name
timing event ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END $$

DELIMITER ;

Where:

• timing → BEFORE or AFTER

• event → INSERT, UPDATE, DELETE

---

3. EXAMPLES FOR LAB

---

⭐ Example 1: BEFORE INSERT Trigger (Validate Data)

Aim: Prevent insertion of negative salary.

Table Setup:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

---

Trigger Creation:

DELIMITER $$

CREATE TRIGGER check_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END$$

DELIMITER ;

---

Test Trigger:

INSERT INTO employees VALUES (1, 'Alice', -50000);

Output:

ERROR 1644: Salary cannot be negative

---

⭐ Example 2: AFTER INSERT Trigger (Audit Log)

Aim: Store every inserted employee into a log table.

Create log table:

CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    name VARCHAR(100),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Trigger Creation:

DELIMITER $$

CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log(emp_id, name)
    VALUES (NEW.id, NEW.name);
END$$

DELIMITER ;

---

Test Trigger:

INSERT INTO employees VALUES (2, 'Bob', 45000);
SELECT * FROM employee_log;

---

⭐ Example 3: BEFORE UPDATE Trigger (Prevent Salary Decrease)

Aim: Prevent salary reduction of an employee.

DELIMITER $$

CREATE TRIGGER prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END$$

DELIMITER ;

Test:

UPDATE employees SET salary = 30000 WHERE id = 2;

Output:

ERROR 1644: Salary cannot be decreased

---

⭐ Example 4: AFTER DELETE Trigger (Record Deleted Data)

Aim: Store deleted employee record in a backup table.

Create backup table:

CREATE TABLE deleted_employees (
    id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Create Trigger:

DELIMITER $$

CREATE TRIGGER backup_deleted_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees(id, name, salary)
    VALUES (OLD.id, OLD.name, OLD.salary);
END$$

DELIMITER ;

---

Test:

DELETE FROM employees WHERE id = 2;
SELECT * FROM deleted_employees;

---

⭐ Example 5: UPDATE Trigger to Maintain Last Modified Time

Modify table:

ALTER TABLE employees ADD last_modified TIMESTAMP;

Create Trigger:

DELIMITER $$

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.last_modified = CURRENT_TIMESTAMP;
END$$

DELIMITER ;

---

⭐ Example 6: Trigger Using OLD and NEW

Operation	NEW	OLD
INSERT	NEW	null
UPDATE	NEW	OLD
DELETE	null	OLD

Example:

SET NEW.salary = OLD.salary + 1000;

---

4. HOW TO VIEW AND DROP TRIGGERS

✔ View triggers:

SHOW TRIGGERS;

✔ Drop a trigger:

DROP TRIGGER trigger_name;

---

5. RESULT

Triggers were successfully created, executed, tested and verified in MySQL.