Triggers in Oracle (Delete Operation)

Triggers are very useful in real time applications as they reduce lot of code to be written by developer to write a logic after a DML operation is performed on one table.

If your requirement is like deleting a record from one table should also delete its relatedĀ  records from other tables thenĀ  your best choice is to create trigger on a table where the record is deleting first so that trigger handles the remaining stuff like deleting records from other tables. Triggers not just limited to deleting records it also handles update, insert operations as well.

Trigger Creation:

<code>

CREATE TRIGGER del_records
AFTER DELETE on customers
FOR EACH ROW
BEGIN
DELETE FROM cm_info WHERE USER_ID = (:old.USER_ID);
DELETE FROM cm_orders WHERE USER_ID = (:old.USER_ID);
DELETE FROM cm_history WHERE USER_ID = (:old.USER_ID);
END;

</code>

The above trigger handles deleting related records from 3 tables (cm_info, cm_orders, cm_history) after a record is getting deleted from customers table.