CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
DECLARE v_old_data json; v_new_data json;
BEGIN /* If this actually for real auditing (where you need to log EVERY action), then you would need to use something like dblink or plperl that could log outside the transaction, regardless of whether the transaction committed or rolled back. */
/* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */
IF (TG_OP = 'UPDATE') THEN v_old_data := ROW_TO_JSON(OLD.*); v_new_data := ROW_TO_JSON(NEW.*); INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data); RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN v_old_data := ROW_TO_JSON(OLD.*); INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data); RETURN OLD; ELSIF (TG_OP = 'INSERT') THEN v_new_data := ROW_TO_JSON(NEW.*); INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,new_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data); RETURN NEW; ELSE RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now(); RETURN NULL; END IF;
EXCEPTION WHEN data_exception THEN RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM; RETURN NULL; WHEN unique_violation THEN RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM; RETURN NULL; WHEN OTHERS THEN RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM; RETURN NULL;
END;
$body$
LANGUAGE plpgsql
CREATE TRIGGER t_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON temp_tblFOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();