SQLize Online / PHPize Online  /  SQLtest Online

DROP TABLE IF EXISTS employee; CREATE TABLE employee ( name text, salary int, last_change_role text, last_change_time timestamp ); INSERT INTO employee VALUES ('Aang', 1, NULL, NULL), ('Zuko', 99999, NULL, NULL); CREATE OR REPLACE FUNCTION employee_insert_row() RETURNS TRIGGER AS $$ BEGIN IF (NEW.name IS NULL OR NEW.salary IS NULL OR NEW.salary < 0) THEN RETURN NULL; END IF; NEW.last_change_role = current_user; NEW.last_change_time = current_timestamp; RETURN NEW; END $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE TRIGGER employee_insert_trigger BEFORE INSERT OR UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION employee_insert_row(); CREATE TABLE employee_history( operation_id serial, operation_type text, operation_time timestamp, operation_user text, new_name text, new_salary text ); CREATE OR REPLACE FUNCTION employee_change_history_row() RETURNS TRIGGER AS $$ BEGIN INSERT INTO employee_history(operation_type, operation_time, operation_user, new_name, new_salary) VALUES (TG_OP, current_timestamp, current_user, NEW.name, NEW.salary); RETURN NEW; END $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE TRIGGER employee_change_history_trigger BEFORE INSERT OR UPDATE ON employee FOR EACH ROW EXECUTE FUNCTION employee_change_history_row(); INSERT INTO employee(name, salary) VALUES('Katara', 20); UPDATE employee SET salary = salary + 100 WHERE name = 'Katara' OR name = 'Aang'; UPDATE employee SET salary = 0 WHERE name = 'Zuko'; SELECT * FROM employee_history;