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.

USER_DETAILS

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)
);
USER_REMINDERS

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

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;
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

select * from USER_REMINDERS;
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

References

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

About the Author

author image
Dhaval Dadhaniya
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Leave a Reply

avatar