how to create Before insert trigger in oracle plsql

Oracle PLSQL BEFORE INSERT TRIGGER means that the trigger will get executed or triggered, just before an INSERT operation is performed on the table for which the trigger is written.

Oracle PLSQL syntax to create a BEFORE INSERT TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
[FOR EACH ROW]
DECLARE
–variable declaration section of code
BEGIN
–code to be triggered
EXCEPTION
WHEN..
–exception handling code
END;

Here in the above Oracle PLSQL trigger syntax the trigger_name is the name given to trigger.

Some points to remember about PLSQL BEFORE INSERT TRIGGER are:

We cannot create a BEFORE INSERT TRIGGER on an Oracle PLSQL VIEW.

We can update :NEW values with BEFORE INSERT TRIGGER.

We cannot update the :OLD values using BEFORE INSERT TRIGGER.

---

Example of Oracle PLSQL BEFORE INSERT TRIGGER

Suppose we create a table named ‘new_employee’ using the script below:

CREATE TABLE new_employee
(
  employee_id    NUMBER
  ,employee_name VARCHAR2(1000)
  ,creation_date DATE
  ,created_by    VARCHAR2(1000)
);

If we query the ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID

EMPLOYEE_NAME

CREATION_DATE

CREATED_BY

At this moment we don’t have any data in the ‘new_employee’ table

---

Creating an Oracle PLSQL BEFORE INSERT TRIGGER on ‘new_employee’ table

Now let’s create a trigger on ‘new_employee’ table so that whenever we enter any employee record, the CREATION_DATE and CREATED_BY columns for that record gets filled automatically.

For this purpose we can create a PLSQL BEFORE INSERT TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE INSERT
  ON new_employee
FOR EACH ROW
DECLARE
  v_creator_name VARCHAR2(1000);
BEGIN
--Getting the name of the current logged in User
  SELECT USER INTO v_creator_name
  FROM dual;
--setting system date in created_date column
  :new.creation_date := sysdate;
--setting the user name to created_by column
  :new.created_by    := v_creator_name;
END;

Here we have created a PLSQL BEFORE INSERT TRIGGER named ‘new_employee_trigger’ which will update the CREATED_BY and CREATION_DATE columns in the ‘new_employee’ table.

---

Let’s see PLSQL BEFORE INSERT TRIGGER in action.

Let’s add a row in ‘new_employee’ table as:

INSERT INTO new_employee
VALUES
(1, 'Emp ABCD',NULL,NULL);

Once we run the above INSERT statement we can query the ‘new_employee’ as:

SELECT *
FROM new_employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/12/2012 5:34:49 PM	SCOTT

Here although we have passed NULL values for the CREATION_DATE and CREATED_BY columns, the PLSQL BEFORE INSERT TRIGGER (‘new_employee_trigger’) inserts the CREATED_BY and CREATION_DATE columns in the ‘new_employe’ table.

Please note that the values in CREATTION_DATE and CREATED_BY columns may be different for you as they depend on system date and logged in user.

---