-- Create the original table
CREATE TABLE employees1 (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- Insert data into the original table
INSERT INTO employees1 VALUES (1, 'MOHAN', 'DEVELOPER', 20000);
INSERT INTO employees1 VALUES (2, 'KAVIN', 'TESTER', 30000);
INSERT INTO employees1 VALUES (3, 'SELVA', 'HELPER', 45000);
INSERT INTO employees1 VALUES (4, 'NITHI', 'SQL TECH', 30000);
INSERT INTO employees1 VALUES (5, 'KASI', 'PLSQL TECH', 25000);

-- Check data in the original table
SELECT * FROM employees1;

-- Create the history table
CREATE TABLE employees_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY, -- Correct auto-increment with primary key
    employee_id INT,
    name VARCHAR(100), -- Correct data type for MySQL
    position VARCHAR(100), -- Correct data type for MySQL
    salary DECIMAL(10, 2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Add the missing column to store the timestamp
);

-- Check data in the history table
SELECT * FROM employees_history;

-- Set the delimiter for trigger creation in MySQL


-- Create the trigger to log old values before update
CREATE OR REPLACE TRIGGER before_employee5_update
BEFORE UPDATE ON employees1
FOR EACH ROW
BEGIN
    -- Insert the old values into the history table
    INSERT INTO employees_history (employee_id, name, position, salary, updated_at)
    VALUES (OLD.employee_id, OLD.name, OLD.position, OLD.salary, CURRENT_TIMESTAMP); -- Use CURRENT_TIMESTAMP instead of SYSDATE()
END;
/

-- Reset the delimiter back to the default


-- Perform an update operation to test the trigger
UPDATE employees1
SET name = 'John Doe', position = 'Software Engineer', salary = 70000
WHERE employee_id = 1;

-- Check data in the original and history tables after the update
SELECT * FROM employees1;
SELECT * FROM employees_history;