I have a trigger on a child table that calls a function when a row is to be deleted. The relationship is one to many. The procedure checks if the row in the child table is the last remaining reference to the parent. If it is, it sends a value back to the trigger so an exception is raised and the delete fails.
It works fine if I delete rows from the child table one at a time. The problem is when I try to delete multiple child rows for the same parent. It does not error as it should and instead allows all the rows to be deleted. I'm kind of stumped on this one. So any help or suggestions would be appreciated.
Thanks,
MJ
Here's a copy of the trigger (trimmed down to save space)
CREATE OR REPLACE TRIGGER RSEQ_SEQ_SET_SEQUENCE
BEFORE INSERT OR UPDATE OR DELETE ON TSEQ_SEQ_SET_SEQUENCE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
v_tbl_name VARCHAR2(30);
v_action VARCHAR2(6);
v_mod_user VARCHAR2(30);
v_pk_cols VARCHAR2(4000);
v_pk_vals VARCHAR2(4000);
v_non_pk_cols VARCHAR2(4000);
v_non_pk_vals_old VARCHAR2(32000);
v_non_pk_vals_new VARCHAR2(32000);
v_status_str VARCHAR2(500);
BEGIN
v_tbl_name := 'TSEQ_SEQ_SET_SEQUENCE';
v_mod_user := nvl(sys_context('userenv','client_identifier'),user);
v_pk_cols := 'SEQUENCE_SET_ID|SEQUENCE_DATASET_NAME|SEQUENCE_ORDER_NUM|HW_MODULE_NAME';
v_non_pk_cols := 'SEQUENCE_ID|SEQUENCE_DELAY|ROW_CREATE_DATE|ROW_CREATE_USER|ROW_MODIFY_DATE|ROW_MODIFY_USER';
IF INSERTING THEN
...
END IF;
IF UPDATING THEN
...
END IF;
IF DELETING THEN
...
v_status_str := KSEQ_BUSINESS_RULES.CHECK_LAST_SEQ_TRIGGER_COMBO(:old.sequence_set_id,
:old.sequence_dataset_name,
:old.hw_module_name);
IF substr(v_status_str,1,4) = 'FAIL' THEN
Raise_application_error(-20000, 'Trigger error(RSEQ_SEQ_SET_SEQUENCE): '||v_status_str);
END IF;
END IF;
END;
And here's the function
FUNCTION CHECK_LAST_SEQ_TRIGGER_COMBO (p_seq_set_id IN tseq_sequence_set.sequence_set_id%TYPE,
p_seq_ds_name IN tseq_sequence_set.sequence_dataset_name%TYPE,
p_hw_mdl_name IN tseq_sequence_set.hw_module_name%TYPE)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION; --had to add this to avoid the mutating table error
v_count_seq_set NUMBER;
v_count_trgr_evnt NUMBER;
v_count_trgr_segment NUMBER;
v_output_msg_str VARCHAR2(500);
v_msg_src_str VARCHAR2(150) := '::KSEQ_BUSINESS_RULES::check_last_seq_trigger_combo ::';
v_status VARCHAR2(200) := 'PASS::SEQ-000100';
BEGIN
BEGIN
--check if associated with a trigger
SELECT COUNT(*) INTO v_count_trgr_evnt
FROM tseq_trigger_event
WHERE sequence_set_id = p_seq_set_id
AND sequence_dataset_name = p_seq_ds_name
AND hw_module_name = p_hw_mdl_name;
SELECT COUNT(*) INTO v_count_trgr_segment
FROM tseq_trigger_segment
WHERE sequence_set_id = p_seq_set_id
AND sequence_dataset_name = p_seq_ds_name
AND hw_module_name = p_hw_mdl_name;
-- If related trigger event exists then Check if it's the last sequence in the set
IF v_count_trgr_evnt > 0 OR v_count_trgr_segment > 0 THEN
SELECT COUNT(*) INTO v_count_seq_set
FROM tseq_seq_set_sequence
WHERE sequence_set_id = p_seq_set_id
AND sequence_dataset_name = p_seq_ds_name
AND hw_module_name = p_hw_mdl_name;
-- If last or no sequences then error send back the error
IF v_count_seq_set <= 1 THEN
v_output_msg_str := 'FAIL::SEQ-000128'||v_msg_src_str||'::Sequence Set "'||
p_seq_set_id||'/'||p_seq_ds_name||'/'||p_hw_mdl_name||'" is the last set for this trigger and should not be deleted.';
v_status := 'FAIL::SEQ-000128'||v_msg_src_str||KCEV_PLSQL_INSTRUMENTS.FCEV_WRITE_PLSQL_OUTPUT_MSG(v_output_msg_str);
END IF;
END IF;
EXCEPTION
-- blanket exception to catch unexpected Oracle errors
WHEN OTHERS THEN
v_output_msg_str := 'FAIL::SEQ-000111'||v_msg_src_str||'Unexpected Oracle error::'||SQLERRM;
v_status := 'FAIL::SEQ-000111'||v_msg_src_str||KCEV_PLSQL_INSTRUMENTS.FCEV_WRITE_PLSQL_OUTPUT_MSG(v_output_msg_str);
END;
RETURN v_status;
END CHECK_LAST_SEQ_TRIGGER_COMBO;