-- Step 1: Create a database
CREATE DATABASE TriggerDemoDB;

-- Step 2: Use the database
USE TriggerDemoDB;

-- Step 3: Create main table and log table
CREATE TABLE Employees (
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    EmpName VARCHAR(50) NOT NULL,
    Salary DECIMAL(10, 2) NOT NULL
);

CREATE TABLE AuditLog (
    LogID INT AUTO_INCREMENT PRIMARY KEY,
    OperationType VARCHAR(10),
    EmpID INT,
    EmpName VARCHAR(50),
    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Step 4: Create triggers

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER BeforeInsertEmployee
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary < 30000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary must be at least 30,000.';
    END IF;
END;
//
DELIMITER ;

-- AFTER INSERT trigger
DELIMITER //
CREATE TRIGGER AfterInsertEmployee
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (OperationType, EmpID, EmpName) 
    VALUES ('INSERT', NEW.EmpID, NEW.EmpName);
END;
//
DELIMITER ;

-- AFTER DELETE trigger
DELIMITER //
CREATE TRIGGER AfterDeleteEmployee
AFTER DELETE ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (OperationType, EmpID, EmpName) 
    VALUES ('DELETE', OLD.EmpID, OLD.EmpName);
END;
//
DELIMITER ;

-- Step 5: Perform operations and show results

-- Insert data
INSERT INTO Employees (EmpName, Salary) VALUES ('John Doe', 50000);
INSERT INTO Employees (EmpName, Salary) VALUES ('Jane Smith', 60000);

-- Attempt to insert with invalid salary (will fail)
-- Uncomment the line below to test the validation
-- INSERT INTO Employees (EmpName, Salary) VALUES ('Invalid Salary', 20000);

-- View Employees table after inserts
SELECT * FROM Employees;

-- View AuditLog table after inserts
SELECT * FROM AuditLog;

-- Delete an employee
DELETE FROM Employees WHERE EmpName = 'John Doe';

-- View Employees table after delete
SELECT * FROM Employees;

-- View AuditLog table after delete
SELECT * FROM AuditLog;