SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE documents (id int, data text); INSERT INTO documents values(1, '22.01.1985'); INSERT INTO documents values(2, '22.01.1985'); INSERT INTO documents values(2, '22.01.1985'); INSERT INTO documents values(2, '22.01.1985'); CREATE TABLE documents_changelog (id int, document_id int, old_data text, new_data text ); CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $documents_changelog$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO documents_changelog SELECT documents.id,documents.id,null, OLD.data from documents; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO documents_changelog SELECT documents.id,documents.id,NEW.data,OLD.data from documents; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO documents_changelog SELECT documents.id,documents.id, NEW.data, OLD.data from documents; END if; end; $documents_changelog$ LANGUAGE plpgsql; CREATE TRIGGER documents_changelog AFTER INSERT OR UPDATE OR DELETE ON documents FOR EACH ROW EXECUTE FUNCTION process_emp_audit(); UPDATE documents SET data='23.01.2020' where id=4; SELECT * from documents; SELECT * from documents_changelog;