Tutorial to set up trigger

TriggerSetup.txt

//*************************************************************
// AUDIT SETUP
1. CREATE EXTENSION hstore;
2. -- Add trigger function (Below)
3. -- Add audit trigger from https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql
4. CREATE TABLE audit.business_audit_log (
    id serial primary key NOT NULL,
    business_edit_id numeric NOT NULL,
	timestamp TIMESTAMPTZ DEFAULT current_timestamp,
	action text NOT NULL CHECK (action IN ('I','D','U','T')),
	type text,
	row_data hstore,
	changed_fields hstore,
	by_id numeric NOT NULL,
	by varchar,
	comment text
);
5. Drop trigger if exists

	DROP TRIGGER log_business_edit ON business_edit;

	CREATE TRIGGER log_business_edit
	AFTER INSERT OR UPDATE ON business_edit
	FOR EACH ROW EXECUTE PROCEDURE log_business_edit();

6. Add Trigger to businesses table. https://wiki.postgresql.org/wiki/Audit_trigger_91plus
   - SELECT audit.audit_table('businesses', 'true', 'false', '{geom}'::text[]);

//*************************************************************
//*************************************************************
// Trigger for step 2.
--
-- AFTER TRIGGER - Function and trigger to amend summarized column(s) on UPDATE, INSERT.
--
CREATE OR REPLACE FUNCTION log_business_edit() RETURNS TRIGGER AS $body$
DECLARE
	audit_id integer;
	by_name VARCHAR;
	by_id integer;
	action text;
	type text;
	h_old hstore;
    h_new hstore;
	excluded_cols text[] = '{row_data, changed_fields, started_at, ended_at}'::text[];
	comment text;
	update_vec text[];
	pair text[];
BEGIN
	action = substring(TG_OP,1,1);
	by_id = NEW.last_modified_by_id;
	by_name = NEW.last_modified_by;
	comment = NEW.last_modified_comment;
	IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
		h_old = hstore(OLD.*) - excluded_cols;
		h_new = (hstore(NEW.*) - h_old) - excluded_cols;
		RAISE INFO 'audit h_new is currently %', h_new;
		audit_id = OLD.id;
		IF h_new = hstore('') THEN
            -- All changed fields are ignored. Skip this update.
            RETURN NULL;
        END IF;
		
		IF (NEW.record_status = 0) THEN
			-- Not permitted to update to proposed
			RAISE EXCEPTION '[log_business_audit] 0 - CANNOT RE-PROPOSE RECORD. PLEASE SUBMIT NEW ONE';
			RETURN NULL;
		ELSEIF (NEW.record_status = 1 AND NEW.status = 0) THEN
			-- Update to Withdrawn. Has to be set inactive
			IF (OLD.record_status = 4) THEN
				-- If its accepted/retired record, reject.
				RAISE EXCEPTION '[log_business_audit] 1 - CANNOT WITHDRAW ACCEPTED/RETIRED RECORD';
				RETURN NULL;
			END IF;
			type = 'WITHDRAWN';
		ELSEIF (NEW.record_status = 2 AND NEW.status = 0) THEN
			-- Update to Rejected. Has to be set inactive
			IF (OLD.record_status = 4) THEN
				-- If its accepted/retired record, reject.
				RAISE EXCEPTION '[log_business_audit] 2 - CANNOT REJECT ACCEPTED/RETIRED RECORD';
				RETURN NULL;
			END IF;
			type = 'REJECTED';
		ELSEIF (NEW.record_status = 3 AND NEW.status = 0) THEN
			-- Update to Replaced. Has to be set inactive
			IF (OLD.record_status != 4) THEN
				-- Not allowed to replace 'Non-Accepted' record.
				RAISE EXCEPTION '[log_business_audit] 3 - CANNOT REPLACE RECORD THAT IS NOT ACCEPTED';
				RETURN NULL;
			ELSEIF (OLD.status = 0) THEN
				-- Not allowed to replace 'retired' record.
				RAISE EXCEPTION '[log_business_audit] 3 - CANNOT REPLACE RECORD THAT IS RETIRED';
				RETURN NULL;
			END IF;
			type = 'REPLACED';
		ELSEIF (NEW.record_status = 4) THEN
			-- Update to Accepted/Retired.
			IF (OLD.record_status = 2 OR OLD.record_status = 3) THEN
				-- Not allowed to accept 'WITHDRAWN/REJECTED' record.
				RAISE EXCEPTION '[log_business_audit] 4 - CANNOT ACCEPT RECORD WITHDRAWN/REJECTED';
				RETURN NULL;
			ELSEIF (OLD.record_status = 4 AND OLD.status = 0) THEN
				RAISE EXCEPTION '[log_business_audit] 4 - CANNOT ACCEPT REPLACED RECORD';
				RETURN NULL;
			END IF;
			IF (NEW.status = 0) THEN
				-- Accepted and Inactive.
				type = 'RETIRED';
			ELSE
				-- Accepted and Active.
				type = 'ACCEPTED';
			END IF;
		ELSE
			-- Falls in when performing Record_status 1,2 or 3 with status 1 (Active)
			RAISE EXCEPTION '[log_business_audit] ELSE. ACTIVE RECORDS CANNOT BE WITHDRAWN/REJECTED/REPLACED';
			RETURN NULL;
		END IF;
	ELSEIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
		audit_id = NEW.id;
		h_old = NULL;
		h_new = hstore(NEW.*) - excluded_cols;
		type = 'NEW';
	ELSE 
		RAISE EXCEPTION '[log_business_audit] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
        RETURN NULL;
	END IF;
	
	INSERT INTO audit.business_audit_log VALUES (
		nextval('audit.business_audit_log_id_seq'),
		audit_id,
		current_timestamp,
		action,
		type,
		h_old,
		h_new,
		by_id,
		by_name,
		comment
	);
	
	IF(type = 'REJECTED' OR type = 'WITHDRAWN' OR type = 'RETIRED') THEN
		-- Set the end date
		UPDATE business_edit
		SET ended_at = current_timestamp
		WHERE id = OLD.id;
	END IF;
	
	IF (type = 'ACCEPTED') THEN
		-- Change old accepted record to REPLACED
		IF EXISTS
		( 
			SELECT 1
			FROM public.business_edit as b
			WHERE b.record_status = 4
			AND b.status = 1
			AND b.business_id = OLD.business_id
			AND b.id != OLD.id
			FOR UPDATE
		)
        THEN
            UPDATE public.business_edit
            SET status = 0,
				record_status = 3,
				ended_at = current_timestamp
            WHERE business_id = OLD.business_id
			AND id != OLD.id
			AND record_status = 4
			AND status = 1;
        END IF;
		
		-- Set the start date
		UPDATE business_edit
		SET started_at = current_timestamp
		WHERE id = OLD.id;
		
		-- Change businesses table
		-- Building update string to update businesses table.
		update_vec := '{}';
		IF (exist(OLD.changed_fields, 'LATITUDE_1') AND exist(OLD.changed_fields, 'LONGITUDE_1')) THEN
			OLD.changed_fields = OLD.changed_fields || ('"geom"=>"' || OLD.geom::text ||'"')::hstore;
		END IF;
		RAISE INFO 'changed_fields %', OLD.changed_fields;
		FOREACH pair SLICE 1 IN ARRAY hstore_to_matrix(OLD.changed_fields)
		LOOP
		  update_vec := update_vec || format('%I = %L', pair[1], pair[2]);
		END LOOP; 
		EXECUTE  
		  format('UPDATE public.businesses SET %s WHERE id = $1',
			 array_to_string(update_vec, ', '))
		USING OLD.business_id;
	END IF;
	RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

//*************************************************************
//*************************************************************
// Basic Queries example
// Select changes on business with id = 1
SELECT changed_fields::hstore, hstore_to_matrix(row_data), hstore_to_array(row_data)
FROM audit.logged_actions
WHERE CAST(row_data::hstore->'id' as INT) = 1
AND table_name = 'businesses'

// Get record of user 417
SELECT 
  a.type,
  e."id" as id,
  "business_id" as bus_id,
  e.changed_fields,
  (SELECT COUNT(*) FROM business_edit WHERE record_status = 0) as recordsTotal
FROM business_edit as e
JOIN (
		SELECT DISTINCT ON (business_edit_id) *
		FROM audit.business_audit_log
		ORDER BY business_edit_id, timestamp desc
	) as a 
ON (e.id = a.business_edit_id)
WHERE e.by_id = 417