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','[email protected]',9999999999,'PASSNUM123','DRIVLIC999');
-- fire after insert trigger, password is null, insert new record into USER_REMINDERS
INSERT INTO USER_DETAILS VALUES (2,'USERNM22','[email protected]',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','[email protected]',3333333333,null,null);
-- fire after insert trigger, driving no is null, insert new record into USER_REMINDERS
INSERT INTO USER_DETAILS VALUES (4,'USERNM44','[email protected]',4444444444,'ONLYPASS11',null);
1.3 Display the data.
select * from USER_DETAILS;
USER_ID | USER_NAME | PHONE | PASSPORT_NO | DRIVING_LICENSE_NO | |
---|---|---|---|---|---|
1 | USERNM1 | [email protected] | 9999999999 | PASSNUM123 | DRIVLIC999 |
2 | USERNM22 | [email protected] | 1111111111 | null | LICNC12345 |
3 | USERNM33 | [email protected] | 3333333333 | null | null |
4 | USERNM44 | [email protected] | 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
About Author
Comments
Subscribe
0 Comments