Oracle BEFORE UPDATE trigger.

Preamble

In this tutorial you will learn how to create a BEFORE UPDATE trigger (before updating) in Oracle with syntax and examples.

Oracle executes (excites) the BEFORE UPDATE trigger before executing the UPDATE operator.

Syntax of BEFORE UPDATE trigger

CREATE [ OR REPLACE ] TRIGGER name_trigger
BEFORE UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
- variable declaration
BEGIN
- trigger code
EXCEPTION
WHEN ...
- emergency handling
END;

Parameters or arguments

• Trigger_name – The name of the trigger to be created.
• BEFORE UPDATE – indicates that the trigger is triggered before the UPDATE operator is executed.
• table_name – The name of the table for which the trigger was created.

Restrictions

• You cannot create a trigger in views.
• You can refresh :NEW (new) values.
• You cannot refresh :OLD values.

Example

Let’s consider an example of how to create a BEFORE UPDATE trigger using the CREATE TRIGGER construction.

If you have created a table of the following structure:

CREATE TABLE orders
( order_id number(5),
number(4),
cost_per_item number(6,2),
total_cost number(8,2),
updated_date date,
updated_by varchar2(10)
);

Then we will create a BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN
-- Find the person username who performs UPDATE in the table
SELECT user INTO v_username
FROM dual;
-- Update the updated_date field to the current system date
:new.updated_date := sysdate;
-- Update the updated_by field on the username that performed the UPDATE
:new.updated_by := v_username;
END;

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​