Interesting trigger I worked on that uses exception handling to not insert records until all the required tables have populated data.
CREATE OR REPLACE TRIGGER CONTENTTEXTTOKEN_TRIGGER_1
AFTER INSERT OR UPDATE ON CONTENTTEXT
FOR EACH ROW
DECLARE
content_contentid VARCHAR2(64);
token_from_xml VARCHAR2(100);
token_recordid VARCHAR2(64);
xml_counter NUMBER := 1;
xml_clob CLOB;
BEGIN
/* Check if there is a clob in CONTENTDATA that corresponds with this update */
GET_XML_CLOB_PROCEDURE(:NEW.RECORDID, xml_clob);
IF DBMS_LOB.GETLENGTH( xml_clob ) > 0 THEN
/* Check for first token in clob */
xml_counter := REGEXP_INSTR(xml_clob, '\{(\s*?.*?)*?\}', xml_counter+1);
WHILE xml_counter <> 0 LOOP
/* Found a token, now get the token from the clob */
token_from_xml := SUBSTR(xml_clob, xml_counter+1, INSTR(SUBSTR(xml_clob, xml_counter+1), '}', 1, 1)-1);
xml_counter := REGEXP_INSTR(xml_clob, '\{(\s*?.*?)*?\}', xml_counter+1);
BEGIN
GET_TOKEN_RECORDID_PROCEDURE(token_from_xml, token_recordid);
IF length(token_recordid) > 0 THEN
BEGIN
SELECT CONTENTTEXTID INTO content_contentid
FROM CONTENTTEXTTOKEN
WHERE CONTENTTEXTID = :NEW.CONTENTID
AND TOKENID = token_recordid
AND TOKENNAME = token_from_xml;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO CONTENTTEXTTOKEN (CONTENTTEXTID, TOKENID, TOKENNAME) VALUES (
:NEW.CONTENTID,
token_recordid,
token_from_xml
);
END;
BEGIN
SELECT CONTENTID INTO content_contentid
FROM CONTENTLINK
WHERE CONTENTID = :NEW.CONTENTID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO CONTENTLINK (CONTENTID, DOCID) VALUES (
:NEW.CONTENTID,
:NEW.DOCUMENTID
);
END;
END IF;
END;
content_contentid := NULL;
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
CREATE OR REPLACE PROCEDURE GET_XML_CLOB_PROCEDURE (
ct_recordid IN VARCHAR2,
xml_clob OUT CLOB ) IS
BEGIN
SELECT XML INTO xml_clob FROM CONTENTDATA WHERE RECORDID = ct_recordid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xml_clob := EMPTY_CLOB();
END;
/
CREATE OR REPLACE PROCEDURE GET_TOKEN_RECORDID_PROCEDURE (
token_from_xml IN VARCHAR2,
token_recordid OUT VARCHAR2 ) IS
BEGIN
SELECT recordid INTO token_recordid FROM replacementtokens WHERE tokenname = token_from_xml;
EXCEPTION
WHEN NO_DATA_FOUND THEN
token_recordid := '';
END;
/
