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.

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

上一篇: Spring Boot + Spring Security + Thymeleaf example
下一篇: Oracle PL/SQL – Display source code for a Trigger
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章来源于互联网, 若有侵权, 联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布文章.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号