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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s