Need help with "Upsert" trigger in Oracle

I have an Oracle table that contains 30 million records of a document archive that is built similar to an EAV table. It contains in each row an id, a keyword, and a value. There may be as a many as 10 records that make up one "document" entry.

 Queries to determine how many "documents" a person has in their archive take well over 30 seconds if they have a lot of documents, some take almost a minute.

I would like a trigger to insert a new record in the LIC_DOCCCOUNT table when a record with the keyword of CLRID is inserted and to decrement the DOUCCOUNT in the LIC_DOCCCOUNT with the same CLRID value when deleting, and I only want it to happen for certain CLRID values.

I'm not sure exactly how to create this trigger. Not a lot but here is what I have so far.

CREATE OR REPLACE TRIGGER trLIC_DOCCOUNT
    AFTER
        INSERT OR
        DELETE
    FOR EACH ROW
    ON fkp_attib
BEGIN
    -- Structure of LIC_DOCCOUNT Table:
    --  CLRID    NUMBER(10)  NOT NULL,
    --  DOC_COUNT  INTEGER
    -- FKP_VALUE has the value that we need to check for
    IF :New.fkp_keyword = 'CLRID' AND :New.fkp_value in (12345, 67890, 654123, 987456) THEN
        CASE
        WHEN INSERTING THEN
            -- this was an insert so insert a new record in LIC_DOCCOUNT 
            -- if a record with the same CLRID doesn't exist, otherwise, 
            -- update the record with the CLRID
        WHEN DELETING THEN
            -- decrement the DOC_COUNT for the record with the CLRID
        END CASE;
    END IF;
END;
/

Select all Open in new window

Here's code we use to obtain the count:

select count(*) as DocCount from (
    select distinct doc.FKP_FD_RID 
    from fkp_attib doc, fkp_attib cli
    where doc.FKP_FD_RID=cli.FKP_FD_RID 
      and cli.FKP_KEYWORD='CLRID' and cli.FKP_VALUE = :PID group by doc.FKP_FD_RID
    having sum(decode(doc.fkp_keyword||doc.fkp_value,'DISTRIBUTIONINTERNAL',1,0))=0
    )

Select all Open in new window