Authority Tracking Triggers

create table auditor.audit_authority (
        id              SERIAL,
        audit_id        BIGINT      NOT NULL,
        record          BIGINT          NOT NULL,
        tag             CHAR(3)         NOT NULL,
        ind1            TEXT,
        ind2            TEXT,
        subfield        TEXT,
        value           TEXT            NOT NULL,
        index_vector    tsvector        NOT NULL
);


-- create table, check
-- #####################################################################

CREATE OR REPLACE FUNCTION auditor.audit_authority_backup() RETURNS trigger as $body$
BEGIN

INSERT INTO auditor.audit_authority (audit_id, record, tag, ind1, ind2, subfield, value, index_vector)
 values (old.id, old.record, old.tag, old.ind1, old.ind2, old.subfield, old.value, old.index_vector);
RETURN NULL;

END;
$body$ LANGUAGE plpgsql;

-- create function check
-- #####################################################################


create trigger audit_authority
before delete on metabib.real_full_rec
for each ROW
    when ( old.tag similar to '(1|4|6|7)%' )
execute procedure auditor.audit_authority_backup();

/* this similar to evaluation is very slap dash but it works as a proof of concept */

-- create trigger check
-- #####################################################################


/*
if we have a tag in metabib that doesn't match anything in auditor then it means
it's a new or edited field and it needs to go for evaluation
need distinct record though
*/

select distinct rfr.record
from auditor.audit_authority aaa
join metabib.real_full_rec rfr on rfr.record = aaa.record
where aaa.value != rfr.value and aaa.tag != rfr.tag and aaa.subfield != rfr.subfield
;


-- create sql check
-- #####################################################################