BEGIN; SELECT evergreen.upgrade_deps_block_check('0623', :eg_version); CREATE TABLE config.org_unit_setting_type_log ( id BIGSERIAL PRIMARY KEY, date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), org INT REFERENCES actor.org_unit (id), original_value TEXT, new_value TEXT, field_name TEXT REFERENCES config.org_unit_setting_type (name) ); -- Log each change in oust to oustl, so admins can see what they messed up if someting stops working. CREATE OR REPLACE FUNCTION ous_change_log() RETURNS TRIGGER AS $ous_change_log$ DECLARE original TEXT; BEGIN -- Check for which setting is being updated, and log it. SELECT INTO original value FROM actor.org_unit_setting WHERE name = NEW.name AND org_unit = NEW.org_unit; INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (NEW.org_unit, original, NEW.value, NEW.name); RETURN NEW; END; $ous_change_log$ LANGUAGE plpgsql; CREATE TRIGGER log_ous_change BEFORE INSERT OR UPDATE ON actor.org_unit_setting FOR EACH ROW EXECUTE PROCEDURE ous_change_log(); CREATE OR REPLACE FUNCTION ous_delete_log() RETURNS TRIGGER AS $ous_delete_log$ DECLARE original TEXT; BEGIN -- Check for which setting is being updated, and log it. SELECT INTO original value FROM actor.org_unit_setting WHERE name = OLD.name AND org_unit = OLD.org_unit; INSERT INTO config.org_unit_setting_type_log (org,original_value,new_value,field_name) VALUES (OLD.org_unit, original, 'null', OLD.name); RETURN OLD; END; $ous_delete_log$ LANGUAGE plpgsql; CREATE TRIGGER log_ous_del BEFORE DELETE ON actor.org_unit_setting FOR EACH ROW EXECUTE PROCEDURE ous_delete_log(); COMMIT;