Question

The database(or the tables) are follow:

CREATE TABLE employees (
        id SERIAL NOT NULL PRIMARY KEY,
        name TEXT NOT NULL,
        salary REAL NOT NULL DEFAULT 25000.0
);

CREATE TABLE employee_audit_log (
        employee_id INTEGER NOT NULL,
        occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees(name, salary)
VALUES
('Arnold Schwarznegger', 35000),
('Yuri Gargarin', 27000),
('Anakin Skywalker', 450000),
('Said Faroghi', 15000),
('Zino Holwerda', 8500);

1. Create a trigger for the employees table so that a new row is inserted in employee audit_log
to log an action (insert or update) performed on an employee.

2. Perform an insert statement which adds a new employee named, "Poppy Harlow"
with a salary of 75,000. Also update any existing employees whose salary
is below the default of 25,000 to be set to that default.

3. Get a list of the name and timestamp of any employees whose information has been
changed. This should return the name of the new employee you've just inserted,
and the others whose salary has changed.

Answer 1

For Creating Triggers:

Trigger for Update:

DELIMITER $$
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log(employee_id)
   Values (New.id);
END$$
DELIMITER ;

Trigger for Insert:

DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log(employee_id)
   Values (New.id);
END$$
DELIMITER ;

Insert Statement :

INSERT INTO employees(name, salary)
VALUES
('Poppy Harlow', 75000);

Update Statement:

UPDATE employees
SET salary = 25000
WHERE salary < 25000;

Query for displaying Names:

SELECT name,occurred_at
FROM employees
INNER JOIN employee_audit_log
ON employees.id = employee_audit_log.employee_id;