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;
USER_ID | USER_NAME | 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
- Creating a DML Trigger: Examples
- PL/SQL Triggers :- Oracle official docs
- Oracle PL/SQL – Before INSERT Trigger example
下一篇: Oracle PL/SQL – Display source code for a Trigger