Issues with ON Delete SET NULL Foreign Key constraint and Trigger on Child Table
Deleting a record from the parent table is causing the trigger on child table to get invoked.
Resulting in ORA-03113 end of file on communication channel.
When I remove " ON DELETE SET NULL" from the foreign key, it works fine.
FK on Table -
ALTER TABLE APP.BC_CUSTOM_SI_PROPERTIES ADD <br/>( CONSTRAINT BC_SI_PROPS_CUSTOM_ATTR_FK <br/>FOREIGN KEY (CUSTOM_ATTRIBUTE_MASTER_ID) <br/>REFERENCES APP.BC_CUSTOM_ATTRIBUTE_MASTERS (CUSTOM_ATTRIBUTE_MASTER_ID) <br/>ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE ENABLE VALIDATE);
Trigger on BC_CUSTOM_SI_PROPERTIES -
CREATE OR REPLACE TRIGGER APP.BC_ACS_SI_CUST_PROPERTY <br/>AFTER DELETE OR INSERT OR UPDATE OF SI_PROPERTY_ID,PROPERTY_NAME ON app.bc_custom_si_properties <br/>REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW <br/>WHEN (NEW.INPUT_TYPE_CC=3 OR OLD.INPUT_TYPE_CC=3) <br/><br/>DECLARE <br/>l_object_id app.bc_acs_obj_change_tracking.object_id%TYPE; <br/>l_object_type app.bc_acs_obj_change_tracking.object_type%TYPE ; <br/>l_owner app.bc_acs_obj_change_tracking.object_id2%TYPE; <br/><br/>BEGIN <br/><br/>IF NOT app.utils_Pkg.workspace_dup_del_running_p = 1 THEN <br/> IF SYS_CONTEXT('clus_ctx','import_in_progress') IS NULL THEN <br/> IF INSERTING OR UPDATING THEN <br/> l_object_id := :new.si_property_id; <br/> l_owner := :new.owner; <br/> ELSIF DELETING THEN <br/> l_object_id := :old.si_property_id; <br/> l_owner := :old.owner; <br/> END IF; <br/><br/>l_object_type := 'SI_PROPERTY_ID'; <br/><br/>IF app.bc_acs_pkg.ws_access_policies_enabled_p (l_owner) = 1 THEN <br/> app.bc_acs_pkg.populate_obj_change_tracking <br/> (p_object_type => l_object_type, p_object_id => l_object_id, p_object_id2 => l_owner,
Tagged:
0