🔹 Table Setup (Example)

CREATE TABLE employees (

   emp_id INT PRIMARY KEY,

   name VARCHAR(50),

   salary INT,

   rating INT

);

CREATE TABLE appraisal_log (

   log_id INT AUTO_INCREMENT PRIMARY KEY,

   emp_id INT,

   action VARCHAR(20),

   old_salary INT,

   new_salary INT,

   log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

🔹 1. INSERT Trigger (After Adding Employee)

👉 Trigger

CREATE TRIGGER trg_after_insert

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, new_salary)

  VALUES (NEW.emp_id, 'INSERT', NEW.salary);

END;

👉 Operation

INSERT INTO employees VALUES (101, 'Amit', 50000, 4);

🔹 2. UPDATE Trigger (Appraisal Salary Change)

👉 Trigger

CREATE TRIGGER trg_after_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, old_salary, new_salary)

  VALUES (NEW.emp_id, 'UPDATE', OLD.salary, NEW.salary);

END;

👉 Operation

UPDATE employees

SET salary = 60000

WHERE emp_id = 101;

🔹 3. DELETE Trigger (Employee Removal)

👉 Trigger

CREATE TRIGGER trg_after_delete

AFTER DELETE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, old_salary)

  VALUES (OLD.emp_id, 'DELETE', OLD.salary);

END;

👉 Operation

DELETE FROM employees

WHERE emp_id = 101;

🔹 4. BONUS: BEFORE UPDATE Trigger (Auto Appraisal Based on Rating)

👉 Trigger

CREATE TRIGGER trg_before_update

BEFORE UPDATE ON employees

FOR EACH ROW

BEGIN

  IF NEW.rating = 5 THEN

     SET NEW.salary = NEW.salary + 10000;

  ELSEIF NEW.rating = 4 THEN

     SET NEW.salary = NEW.salary + 5000;

  END IF;

END;

👉 Operation

UPDATE employees

SET salary = 60000, rating = 5

WHERE emp_id = 102;

👉 Output

👉 Salary automatically becomes 70000 (60000 + 10000)

🔹 Final Understanding

• INSERT Trigger → Logs new employee

• UPDATE Trigger → Tracks appraisal changes

• DELETE Trigger → Tracks employee removal

• BEFORE UPDATE Trigger → Automates appraisal logic

you can maintain INSERT, UPDATE, and DELETE operations in a single table — and in fact, that’s the recommended approach for logging (audit table).

🔹 Concept

Instead of creating separate tables, you use one common log table (like appraisal_log) and store:

• Type of operation (INSERT / UPDATE / DELETE)

• Old values

• New values

• Timestamp

🔹 Single Log Table Design

CREATE TABLE appraisal_log (

   log_id INT AUTO_INCREMENT PRIMARY KEY,

   emp_id INT,

   action VARCHAR(10),   -- INSERT / UPDATE / DELETE

   old_salary INT,

   new_salary INT,

   log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

🔹 Triggers Using Same Table

✅ INSERT Trigger

CREATE TRIGGER trg_insert

AFTER INSERT ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, new_salary)

  VALUES (NEW.emp_id, 'INSERT', NEW.salary);

END;

✅ UPDATE Trigger

CREATE TRIGGER trg_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, old_salary, new_salary)

  VALUES (NEW.emp_id, 'UPDATE', OLD.salary, NEW.salary);

END;

✅ DELETE Trigger

CREATE TRIGGER trg_delete

AFTER DELETE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO appraisal_log(emp_id, action, old_salary)

  VALUES (OLD.emp_id, 'DELETE', OLD.salary);

END;

Point (Interview Line)

👉 “Yes, all three operations can be logged in a single audit table using different triggers and an action column to distinguish operations.”