Oracle PL/SQL After INSERT Trigger Example

Overview

This Article will help you to understand “AFTER INSERT TRIGGER statement” with examples and it’s detailed description.

This statement specifies that Oracle will fire this trigger AFTER the INSERT operation is executed.

In real life scenarios, AFTER INSERT Trigger mostly used for Data insertion into other table.

Restrictions on AFTER INSERT Trigger
  1. You cannot specify an AFTER trigger on a view or an object view.
  2. You cannot write either the :OLD or the :NEW value.

Examples

Data insertion into other table

(1) Check inserted data and insert into other table.

Description

In this example we have two tables user_details and user_reminders. When ever new user created into system, user details like username, email, phone etc stored into user_details.

passport_no,driving_license_no etc fields are not mandatory at the time of user creation, but required as per business requirement so we will give reminders after three days to user to submit these details.

So user_reminders have reminder_text and user_id to which we want to remind about providing information.

First we will create tables user_details and user_reminders.

Then we will create AFTER INSERT TRIGGER “trg_after_insert_user” on user_details table.

After insertion into user_details if passport_no and/or driving_license_no are not provided, then trigger will insert data into user_reminders.

We will insert few values in user_details only and observe data from both user_details and user_reminders tables.

Code
Output
table USER_DETAILS created.
Output
table USER_REMINDERS created.
Output
TRIGGER TRG_AFTER_INSERT_USER compiled
USER_ID USER_NAME EMAIL PHONE PASSPORT_NO DRIVING_LICENSE_NO
1 USERNM1 abcdxyz@abc.com 9999999999 PASSNUM123 DRIVLIC999
2 USERNM22 xyzabcd@abc.com 1111111111 null LICNC12345
3 USERNM33 xyztttt@abc.com 3333333333 null null
4 USERNM44 ghijkl@abc.com 4444444444 ONLYPASS11 null
USER_ID REMINDER_TEXT REMINDER_DATE STATUS
2 Please insert your passport details into system. 21-JUN-2017 PENDING
3 Please insert your passport details into system. Please insert your Driving license details into system. 21-JUN-2017 PENDING
4 Please insert your Driving license details into system. 21-JUN-2017 PENDING
In above table of user_reminders entry of user_id 1 is not there as we provided both passport_no and driving_license_no in insert of user_id 1.
References
  1. Syntax :- Oracle official docs
  2. PL/SQL Triggers :- Oracle official docs
It's good to share...Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Google+Email this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *