Before and After Insert Triggers

Before and After Insert Triggers -Learn how to use Before and After Insert Triggers with Examples.

Before and After Insert Triggers

BEFORE INSERT TRIGGER

The Before Insert Trigger is fired before insert operation performed on the table. Before Keyword access the new values before they go into the table

Syntax

CREATE [ OR REPLACE ] TRIGGER trigger_name
BEFORE INSERT
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Sample EMP table to perform Trigger actions.

CREATE TABLE
 EMP(EMPLOYEE_ID NUMBER,FIRST_NAME VARCHAR2(64 BYTE),
LAST_NAME VARCHAR2(64 BYTE),
EMAIL VARCHAR2(64 BYTE));

Example Program – to display the data before inserting data into table.

create or replace
TRIGGER DISP_NEW_EMP_DATA
BEFORE INSERT ON EMP 
FOR EACH ROW

BEGIN
dbms_output.PUT_LINE('EMPLOYEE_ID : '|| :NEW.EMPLOYEE_ID);
  dbms_output.PUT_LINE('FIRST_NAME : '|| :NEW.FIRST_NAME);
dbms_output.PUT_LINE('LAST_NAME : '|| :NEW.LAST_NAME);
  dbms_output.PUT_LINE('EMAIL : '|| :NEW.EMAIL);
END;

Sample Insertion Query

INSERT INTO EMP(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL)
VALUES(10,'SWETHA KUMAR','ALUBOYINA','[email protected]');

Output

EMPLOYEE_ID : 10
FIRST_NAME : SWETHA KUMAR
LAST_NAME : ALUBOYINA
EMAIL : [email protected]

Example program to change the data to uppercase before insertion

create or replace
TRIGGER CHANGE_CASE_EMP_DATA
BEFORE INSERT ON EMP 
FOR EACH ROW

BEGIN
:NEW.FIRST_NAME:=UPPER(:NEW.FIRST_NAME);
:NEW.LAST_NAME :=UPPER(:NEW.LAST_NAME);
:NEW.EMAIL :=UPPER(:NEW.EMAIL);
dbms_output.PUT_LINE('EMPLOYEE_ID : '|| :NEW.EMPLOYEE_ID);
  dbms_output.PUT_LINE('FIRST_NAME : '|| :NEW.FIRST_NAME);
dbms_output.PUT_LINE('LAST_NAME : '|| :NEW.LAST_NAME);
  dbms_output.PUT_LINE('EMAIL : '|| :NEW.EMAIL);
END;

In the above example all the characters in FIRST_NAME,LAST_NAME,EMAIL columns are converting into UPPER Characters before inserting into EMP table

here is the example insertion query with Mixed characters(both lower and upper characters).

Sample Insertion query

INSERT INTO EMP(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL)
VALUES(12,'radhika KUMAR','radson','radhika [email protected]');

Output

EMPLOYEE_ID : 12
FIRST_NAME : RADHIKA KUMAR
LAST_NAME : RADSON
EMAIL : RADHIKA [email protected]

SELECT * FROM EMP;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL
10	SWETHA KUMAR	ALUBOYINA	[email protected]
12	RADHIKA KUMAR	RADSON	RADHIKA [email protected]

AFTER INSERT TRIGGER

The AFTER Insert Trigger is fired AFTER insert operation performed on the table.
AFTER Keyword access the new values after the initial changes applied on the table and the table is back in a consistent state.

In this example, if a new employee is created in emp table, but fields like First or dri_name ,Last_name or Email is missing, a new record will be inserted into EMP_REMINDERS via ‘after insert’ trigger on emp table

CREATE TABLE EMP_REMINDERS
(
	EMP_ID number(10),
	REMINDER_TEXT varchar2(200),
	REMINDER_DATE date,
	STATUS varchar2(10)
);

Example Program – to insert the data into EMP_REMINDERS table if values with fields are missing when new records are inserting

CREATE OR REPLACE TRIGGER REMAINDERS_EMP_DATA
 AFTER
  INSERT ON EMP 
FOR EACH ROW 
DECLARE counter NUMBER(2);
  reminder_text VARCHAR2(200);
  BEGIN
    counter            := 0;
    reminder_text      := '';
    IF(:NEW.FIRST_NAME IS NULL OR :NEW.FIRST_NAME='') THEN
      reminder_text    :='Please Enter First Name into system';
      counter          := counter+1;
    END IF;
    IF(:NEW.LAST_NAME IS NULL OR :NEW.LAST_NAME='') THEN
      reminder_text   :=reminder_text||'Please Enter Last Name into System';
      counter         := counter+1;
    END IF;
    IF(:NEW.EMAIL   IS NULL OR :NEW.EMAIL='') THEN
      reminder_text :=reminder_text||'Please Enter Email into System';
      counter       := counter+1;
    END IF;
-- If First or dri_name ,Last_name or Email is missing 
  -- then counter will be >0 and below code will insert into EMP_REMINDERS  table.
    IF COUNTER > 0 THEN
      INSERT
      INTO EMP_REMINDERS VALUES
        (
          :NEW.EMPLOYEE_ID,
          reminder_text,
          sysdate,
          'PENDING'
        );
    END IF;
  END;

Sample Insertion query to test the trigger.

INSERT INTO EMP1(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL)
VALUES(13,'','PREMSON','radhika [email protected]');

Output

SELECT * FROM EMP;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL
10	SWETHA KUMAR	ALUBOYINA	[email protected]
12	RADHIKA KUMAR	RADSON	RADHIKA [email protected]
13		PREMSON	RADHIKA [email protected]

SELECT * FROM EMP_REMINDERS;

EMP_ID	REMINDER_TEXT	REMINDER_DATE	STATUS
13	Please Enter First Name into system	07-NOV-18	PENDING

Before and After Insert Triggers