Oracle PL/SQL – After INSERT Trigger example

This article shows you how to use AFTER INSERT TRIGGER, it will fire after the insert operation is executed.

1. After INSERT Trigger

In this example, if a new user is created in user_details, but fields like passport_no or driving_license_no is missing, a new record will be inserted into user_reminders via ‘after insert’ trigger on user_details

1.1 Create tables and trigger.

CREATE TABLE USER_DETAILS
	USER_ID number(10) primary key,
	USER_NAME varchar2(15),
	EMAIL varchar2(20),
	PHONE number(12),
	PASSPORT_NO varchar2(10),
	DRIVING_LICENSE_NO varchar2(10)
);

CREATE TABLE USER_REMINDERS
	USER_ID number(10),
	REMINDER_TEXT varchar2(200),
	REMINDER_DATE date,
	STATUS varchar2(10)
);

CREATE OR REPLACE TRIGGER trg_after_insert_user
AFTER INSERT
  on USER_DETAILS
  FOR EACH ROW 
DECLARE
counter number(2);
reminder_text varchar2(200);
BEGIN
counter := 0;
reminder_text := '';
  IF(:NEW.PASSPORT_NO = '' OR :NEW.PASSPORT_NO is null) THEN
    reminder_text := 'Please insert your passport details into system. ';
    counter := counter+1;
  END IF;  
  IF(:NEW.DRIVING_LICENSE_NO = '' OR :NEW.DRIVING_LICENSE_NO is null) THEN
    reminder_text := reminder_text || 'Please insert your Driving license details into system.';
    counter := counter+1;
  END IF;  
  -- If passport_no and/or driving_license_no is missing 
  -- then counter will be >0 and below code will insert into user_reminders table.
  IF(counter>0) THEN
    INSERT INTO USER_REMINDERS VALUES (:NEW.USER_ID,reminder_text,sysdate+3,'PENDING');
  END IF;
END;

1.2 Insert data to test the trigger.

-- fire after insert trigger, no action.
INSERT INTO USER_DETAILS VALUES (1,'USERNM1','abcdxyz@abc.com',9999999999,'PASSNUM123','DRIVLIC999');
-- fire after insert trigger, password is null, insert new record into USER_REMINDERS  
INSERT INTO USER_DETAILS VALUES (2,'USERNM22','xyzabcd@abc.com',1111111111,null,'LICNC12345');
-- fire after insert trigger, password and driving no are null, insert new record into USER_REMINDERS 
INSERT INTO USER_DETAILS VALUES (3,'USERNM33','xyztttt@abc.com',3333333333,null,null);
-- fire after insert trigger, driving no is null, insert new record into USER_REMINDERS 
INSERT INTO USER_DETAILS VALUES (4,'USERNM44','ghijkl@abc.com',4444444444,'ONLYPASS11',null);

1.3 Display the data.

select * from USER_DETAILS;

select * from USER_REMINDERS;

References

1. Creating a DML Trigger: Examples
2. PL/SQL Triggers :- Oracle official docs
3. Oracle PL/SQL – Before INSERT Trigger example