1

There is a common column pl_id in 6 Oracle tables. But there is no foreign key concept implemented. The requirement is-- if multiple rows corresponding to 'n' pl_id(s) are getting deleted from one table, records pertaining to same 'n' pl_id(s) in other 5 tables must also delete automatically. Checked below URLs: Optimal way to DELETE specified rows from Oracle Delete all rows in a table based on another table

Checked many other sites as well, but couldn't find help that I am seeking for, possibly I am using wrong key word while searching.

Community
  • 1
  • 1
Anshu
  • 51
  • 1
  • 8
  • Sorry for the miss, the column pl_id contains unique values. Also, the 'n' values should not be entered manually to delete records from rest tables. It should automatically detect the deletion from one table and delete corresponding records form rest tables. – Anshu Mar 16 '16 at 12:48
  • Why not introduce foreign key concept in other tables? – Jacob Mar 16 '16 at 12:55
  • 1
    Yes I understand foreign key can resolve the issue at ease by CASCADE DELETE, but is out of scope. – Anshu Mar 16 '16 at 12:58
  • See if [this](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164) helps. – Jacob Mar 16 '16 at 13:08
  • I am afraid I could not find any solution in the above link. – Anshu Mar 16 '16 at 13:17
  • What about triggers? – Nitish Mar 16 '16 at 13:36
  • @Nitish, since I am new to triggers, can you please provide a sample trigger based upon the scenario that's provided? – Anshu Mar 16 '16 at 13:48
  • @Anshu: This [simple tutorial](http://www.techonthenet.com/oracle/triggers/before_delete.php) should help you. – Nitish Mar 16 '16 at 13:52
  • @Nitish, I checked the AFTER DELETE and BEFORE DELETE triggers and understood the purpose. But here, I am sorry if I couldn't make it clear, the sole purpose is to delete the records from rest tables having 'n' pl_id(s) which would be deleted from table 1 first. Can you please help in linking FOR EACH ROW and BEGIN block in trigger with my objective? – Anshu Mar 16 '16 at 14:19
  • `FOR EACH ROW` is used to determine whether a trigger must fire when each row is deleted. After `BEGIN`, you can write `DELETE` statements for the remaining 5 tables using `:old.pl_id`. – Nitish Mar 16 '16 at 14:29
  • @Nitish, Thank you. Can you please see if this is correct: CREATE OR REPLACE TRIGGER DUMMY_TEST_TRIGGER1 AFTER DELETE ON TABLE1 FOR EACH ROW BEGIN DELETE FROM TABLE2 WHERE JOB_STAGE_ID := :OLD.JOB_STAGE_ID END; / – Anshu Mar 16 '16 at 15:01
  • While deleting record from table 1, this error is displayed-- Error: ORA-04098: trigger 'I60_SCH04.DUMMY_TEST_TRIGGER1' is invalid and failed re-validation – Anshu Mar 16 '16 at 16:00
  • Hi all, solution found: CREATE OR REPLACE TRIGGER TRIGGER_NAME AFTER DELETE ON TABLE1 FOR EACH ROW BEGIN DELETE FROM TABLE2 WHERE PL_ID = :OLD.PL_ID; DELETE FROM TABLE3 WHERE PL_ID = :OLD.PL_ID; DELETE FROM TABLE4 WHERE PL_ID = :OLD.PL_ID;DELETE FROM TABLE5 WHERE PL_ID = :OLD.PL_ID; END; / – Anshu Mar 16 '16 at 17:05
  • @Anshu: You are welcome. Hope you have found the solution – Nitish Mar 17 '16 at 06:31

0 Answers0