Example Table Creation and Trigger Management

1. Create the employees table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Create the audit_log table:

CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(50),
    employee_id INT,
    action_time DATETIME
);

3. Create a BEFORE INSERT Trigger:

CREATE TRIGGER set_default_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary IS NULL THEN
        SET NEW.salary = 50000;
    END IF;
END;

4. Create an AFTER INSERT Trigger:

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('INSERT', NEW.id, NOW());
END;

5. Create an AFTER UPDATE Trigger:

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit_log (action, employee_id, action_time)
        VALUES ('UPDATE SALARY', NEW.id, NOW());
    END IF;
END;

6. Create an AFTER DELETE Trigger:

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('DELETE', OLD.id, NOW());
END;

Testing the Triggers

Insert a new employee:

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'Engineering', NULL);

Check the employees and audit_log tables to see the effects of the triggers.

Update an employee's salary:

UPDATE employees
SET salary = 80000
WHERE id = 1;

Check the audit_log table to see the log entry.

Delete an employee:

DELETE FROM employees
WHERE id = 1;

Check the audit_log table to see the log entry.

Modifying and Dropping Triggers

Modify a Trigger:

To modify an existing trigger, you need to drop it and recreate it.

DROP TRIGGER set_default_salary;

CREATE TRIGGER set_default_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary IS NULL THEN
        SET NEW.salary = 60000;
    END IF;
END;

Drop a Trigger:

DROP TRIGGER after_employee_insert;