q4uEQCPS

1DROP TABLE "employees";
2DROP TABLE "employee_audits";
3
4CREATE TABLE employees(
5    id serial primary key,
6    first_name varchar(40) NOT NULL,
7    last_name varchar(40) NOT NULL,
8  	salary FLOAT
9);
10
11CREATE TABLE employee_audits (
12    id serial primary key,
13    employee_id int4 NOT NULL,
14    last_name varchar(40) NOT NULL,
15    changed_on timestamp(6) NOT NULL,
16  	salary integer,
17  	pct_change FLOAT
18);
19
20CREATE OR REPLACE FUNCTION log_last_name_changes() RETURNS trigger AS
21$log_last_name_changes$
22BEGIN
23    IF NEW.salary <> OLD.salary THEN
24        INSERT INTO employee_audits(employee_id,last_name, salary, pct_change, changed_on)
25        VALUES(OLD.ID,OLD.last_name,NEW.salary,(NEW.salary / OLD.salary) -1,now());
26    END IF;
27    RETURN NEW;
28END;
29$log_last_name_changes$  LANGUAGE plpgsql;
30
31DROP TRIGGER IF EXISTS last_name_changes ON "employees";
32CREATE TRIGGER last_name_changes
33BEFORE UPDATE ON employees
34    FOR EACH ROW EXECUTE PROCEDURE log_last_name_changes();
35
36    INSERT INTO employees (first_name, last_name, salary)
37VALUES ('John', 'Doe',4000.0);
38
39INSERT INTO employees (first_name, last_name, salary)
40VALUES ('Lily', 'Bush',3000.0);
41
42SELECT * FROM employees;
43
44UPDATE employees
45SET salary = 4500
46WHERE ID = 2;