Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.
Here's code we use to obtain the count:
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;
/
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
)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
With the distinct and having sum in the query, I can see where it might be inefficient.
I'm not fully understanding what you are trying to do.
Can you provide some sample raw data, some insert and deletes and expected results?
I'm not fully understanding what you are trying to do.
Can you provide some sample raw data, some insert and deletes and expected results?
Not sure how many records you need but here's 10 documents:
expected results would be just insert the number of documents for each CLRID in the list into the LIC_DOCCOUNT table.
FKP_FD_RID FKP_KEYWORD FKP_VALUE
1621438 FILENAME \1016713\770\Letters\CLDL20100302
1621438 CLRID 1016713
1621438 DOC_NAME CLDL
1621438 PRINT_DATE 20100303
1621438 DESIGNATION CISR
1621438 DISTRIBUTION INTERNAL
1621439 FILENAME \965699\603\Letters\CLDL20100302
1621439 CLI_RID 965699
1621439 DOC_NAME CLDL
1621439 PRINT_DATE 20100303
1621439 DESIGNATION CISR
1621439 DISTRIBUTION INTERNAL
1621440 FILENAME \1009723\759\Letters\CLDL20100302
1621440 CLI_RID 1009723
1621440 DOC_NAME CLDL
1621440 PRINT_DATE 20100303
1621440 DESIGNATION CISR
1621440 DISTRIBUTION INTERNAL
1621441 FILENAME \953310\178\Letters\CLDL20100302
1621441 CLI_RID 953310
1621441 DOC_NAME CLDL
1621441 PRINT_DATE 20100303
1621441 DESIGNATION CISR
1621441 DISTRIBUTION INTERNAL
1621442 FILENAME \1098748\54\Letters\CLDL20100302
1621442 CLI_RID 1098748
1621442 DOC_NAME CLDL
1621442 PRINT_DATE 20100303
1621442 DESIGNATION CISR
1621442 DISTRIBUTION INTERNAL
1621443 FILENAME \1088006\279\Letters\CLDL20100302
1621443 CLI_RID 1088006
1621443 DOC_NAME CLDL
1621443 PRINT_DATE 20100303
1621443 DESIGNATION CISR
1621443 DISTRIBUTION INTERNAL
1621444 FILENAME \996175\172\Letters\CLDL20100302
1621444 CLI_RID 996175
1621444 DOC_NAME CLDL
1621444 PRINT_DATE 20100303
1621444 DESIGNATION CISR
1621444 DISTRIBUTION INTERNAL
1621445 FILENAME \1092295\580\Letters\CLDL20100302
1621445 CLI_RID 1092295
1621445 DOC_NAME CLDL
1621445 PRINT_DATE 20100303
1621445 DESIGNATION CISR
1621445 DISTRIBUTION INTERNAL
1621446 FILENAME \900796\505\Letters\CLDL20100302
1621446 CLI_RID 900796
1621446 DOC_NAME CLDL
1621446 PRINT_DATE 20100303
1621446 DESIGNATION CISR
1621446 DISTRIBUTION INTERNAL
1621447 FILENAME \1091439\721\Letters\CLDL20100302
1621447 CLI_RID 1091439
1621447 DOC_NAME CLDL
1621447 PRINT_DATE 20100303
1621447 DESIGNATION CISR
1621447 DISTRIBUTION INTERNAL
1621448 FILENAME \921784\556\Letters\CLDL20100302
1621448 CLI_RID 921784
1621448 DOC_NAME CLDL
1621448 PRINT_DATE 20100303
1621448 DESIGNATION CISR
1621448 DISTRIBUTION INTERNAL
1621449 FILENAME \1113354\702\Letters\CLDL20100302
1621449 CLI_RID 1113354
1621449 DOC_NAME CLDL
1621449 PRINT_DATE 20100303
1621449 DESIGNATION CISR
1621449 DISTRIBUTION INTERNAL
expected results would be just insert the number of documents for each CLRID in the list into the LIC_DOCCOUNT table.
Given your sample data, what would the LIC_DOCCOUNT table look like at that stage? Provide some data for, say, two inserts and select maybe three existing records for deletion, then show what the LIC_DOCCOUNT should look like after the trigger.
It is easy enough to write a insert and delete queries here, I don't need to provide those. The doc_ccount is based on the count of the CLRID value.
The contents of the LIC_DOCCOUNT table should look like this if my limiting CLRIDs are: 953310, 1092295, 1113354, 1016713:
The other records that aren't in my limiting list do not get entries in the table. The trigger only should work on the limiting list. However, the data provided includes the DISTRIBUTION keyword with a value of INTERNAL and those would be omitted from the count. Please use this revised data:
The contents of the LIC_DOCCOUNT table should look like this if my limiting CLRIDs are: 953310, 1092295, 1113354, 1016713:
CLRID DOC_COUNT
1016713 1
953310 1
1092295 1
1113354 1
The other records that aren't in my limiting list do not get entries in the table. The trigger only should work on the limiting list. However, the data provided includes the DISTRIBUTION keyword with a value of INTERNAL and those would be omitted from the count. Please use this revised data:
FKP_FD_RID FKP_KEYWORD FKP_VALUE
1621438 FILENAME \1016713\770\Letters\CLDL20100302
1621438 CLRID 1016713
1621438 DOC_NAME CLDL
1621438 PRINT_DATE 20100303
1621438 DESIGNATION CISR
1621439 FILENAME \965699\603\Letters\CLDL20100302
1621439 CLRID 965699
1621439 DOC_NAME CLDL
1621439 PRINT_DATE 20100303
1621439 DESIGNATION CISR
1621440 FILENAME \1009723\759\Letters\CLDL20100302
1621440 CLRID 1009723
1621440 DOC_NAME CLDL
1621440 PRINT_DATE 20100303
1621440 DESIGNATION CISR
1621441 FILENAME \953310\178\Letters\CLDL20100302
1621441 CLRID 953310
1621441 DOC_NAME CLDL
1621441 PRINT_DATE 20100303
1621441 DESIGNATION CISR
1621442 FILENAME \1098748\54\Letters\CLDL20100302
1621442 CLRID 1098748
1621442 DOC_NAME CLDL
1621442 PRINT_DATE 20100303
1621442 DESIGNATION CISR
1621443 FILENAME \1088006\279\Letters\CLDL20100302
1621443 CLRID 1088006
1621443 DOC_NAME CLDL
1621443 PRINT_DATE 20100303
1621443 DESIGNATION CISR
1621444 FILENAME \996175\172\Letters\CLDL20100302
1621444 CLRID 996175
1621444 DOC_NAME CLDL
1621444 PRINT_DATE 20100303
1621444 DESIGNATION CISR
1621445 FILENAME \1092295\580\Letters\CLDL20100302
1621445 CLRID 1092295
1621445 DOC_NAME CLDL
1621445 PRINT_DATE 20100303
1621445 DESIGNATION CISR
1621446 FILENAME \900796\505\Letters\CLDL20100302
1621446 CLRID 900796
1621446 DOC_NAME CLDL
1621446 PRINT_DATE 20100303
1621446 DESIGNATION CISR
1621447 FILENAME \1091439\721\Letters\CLDL20100302
1621447 CLRID 1091439
1621447 DOC_NAME CLDL
1621447 PRINT_DATE 20100303
1621447 DESIGNATION CISR
1621448 FILENAME \921784\556\Letters\CLDL20100302
1621448 CLRID 921784
1621448 DOC_NAME CLDL
1621448 PRINT_DATE 20100303
1621448 DESIGNATION CISR
1621449 FILENAME \1113354\702\Letters\CLDL20100302
1621449 CLRID 1113354
1621449 DOC_NAME CLDL
1621449 PRINT_DATE 20100303
1621449 DESIGNATION CISR
In your sample data I only see one CLRID record. The fkp_value for that one isn't in the pseudocode for the trigger you posted so I don't see how that row ends up in the LIC_DOCCOUNT table.
But, I sort of get the idea. Instead of incrementing and decrementing in the trigger, can you not just compute the count every time?
The insert code should go something like (untested, just typed in):
-- 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
update LIC_DOCCOUNT set doc_count=doc_count+1 where clrid=:New.fkp_value
if sql%rowcount=0 then
insert into LIC_DOCCOUNT(clrid,doc_cou
end if;
end;
delete code:
update LIC_DOCCOUNT set doc_count=doc_count-1 where clrid=:New.fkp_value
But, I sort of get the idea. Instead of incrementing and decrementing in the trigger, can you not just compute the count every time?
The insert code should go something like (untested, just typed in):
-- 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
update LIC_DOCCOUNT set doc_count=doc_count+1 where clrid=:New.fkp_value
if sql%rowcount=0 then
insert into LIC_DOCCOUNT(clrid,doc_cou
end if;
end;
delete code:
update LIC_DOCCOUNT set doc_count=doc_count-1 where clrid=:New.fkp_value
Premium Content
You need an Expert Office subscription to comment.Start Free Trial