what is Before update trigger in oracle plsql

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

Oracle PLSQL syntax to create a BEFORE UPDATE TRIGGER is:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE UPDATE
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 UPDATE TRIGGER are:

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

We can update :NEW values with BEFORE UPDATE TRIGGER.

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

---

Example of Oracle PLSQL BEFORE UPDATE 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 UPDATE TRIGGER

Now let’s create a plsql Before Update trigger on ‘new_employee’ table so that whenever we are updating 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 UPDATE TRIGGER as:

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE UPDATE
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 UPDATE TRIGGER named ‘new_employee_trigger’ which will update the CREATED_BY and CREATION_DATE in the ‘new_employee’ table.

---

Let’s see PLSQL BEFORE UPDATE 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

Notice that the CREATION_DATE and CREATED_BY Columns have NULL values.

Now, let’s update the employee record in the ‘new_employee’ table.

UPDATE new_employee
SET employee_name = 'Emp EFGH';

Now, if we query ‘new_employee’ table as:

SELECT *
FROM new_employee;

We will get the following output:

EMPLOYEE_ID	EMPLOYEE_NAME	CREATION_DATE	CREATED_BY
1	Emp ABCD	12/13/2012 3:46:33 PM	SCOTT

Here we can see that after performing insert operation on ‘new_employee’ table the CREATION_DATE and CREATED_BY columns were having NULL values, but as soon as we performed an UPDATE operation, the BEFORE UPDATE TRIGGER got fired and updated the record for CREATION_DATE and CREATED_BY columns.

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

---