Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / Oracle Row-level Triggers

Oracle Row-level Triggers

Summary: in this tutorial, you will learn about Oracle row-level triggers and how to use the CREATE TRIGGER statement to create a new row-level trigger.

Introduction to Oracle row-level triggers

Row-level triggers fires once for each row affected by the triggering event such as INSERT, UPDATE, or DELETE.

Row-level triggers are useful for data-related activities such as data auditing and data validation.

To create a new row-level trigger, you use the CREATE TRIGGER statement with the FOR EACH ROW clause.

CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER INSERT OR DELETE OR UPDATE OF column1, column2, … ON table_name FOR EACH ROW REFERENCING OLD AS old_name NEW AS new_name WHEN (condition) DECLARE … BEGIN … EXCEPTION … END;

The features which are applicable to the statement-level triggers are also available the row-level trigger:

  • Use raise_application_error() to raise a user-defined exception
  • Check if the current event is INSERT, UPDATE, and DELETE using the INSERTING or DELETING or UPDATING.

On top of that, row-level triggers allow you to track the BEFORE and AFTER values.

The :OLD & :NEW column values

Because row-level triggers execute within the context of a single row, you can access the old and new column values using the following syntax:

:OLD.column_name :NEW.column_name

For example, this statement checks the value of the credit_limit column to see if the new credit is greater than the current credit:

IF :NEW.credit_limit > :OLD.credit_limit THEN -- carry an action END;

This table illustrates the availability of :NEW and :OLD variables by the triggering event:

Triggering Event:NEW:OLD
INSERTYesNo
UPDATEYesYes
DELETENoyes

Modifying :OLD & :NEW values

A BEFORE row-level trigger can modify the new column values, but an AFTER row-level trigger cannot.

Correlation names

OLD and NEW are the default correlation names. But you can override them using the REFERENCING clause.

When you reference OLD and NEW in the trigger body, you must precede them with a colon (:) because OLD and NEW are external variable references.

Performance consideration

A row-level trigger fires each time a row is affected by a triggering event. For example, if you update 1000 rows in a table, the trigger will fire 1000 times, which potentially cause a performance issue.

To specify a condition of when to fire the trigger, you can use the WHEN clause. For example, the following trigger only fires when you update credit for a customer with the new credit is greater than 10,000:

CREATE OR REPLACE TRIGGER BEFORE UPDATE OF credit_limit ON customers FOR EACH ROW WHEN NEW.credit_limit > 10000; ...

In some situations, using the condition in the WHEN can significantly improve the performance of the database.

Note that you can use both OLD and NEW in the WHEN clause. In addition, you don’t use a colon (:) as the prefix for these variables.

Oracle row-level trigger example

We’ll use the customers table from the sample database for demonstration:

customers table

The following example creates a row-level trigger that prevents users from updating credit for a customer if the new credit increases to more than double:

CREATE OR REPLACE TRIGGER customers_update_credit_trg BEFORE UPDATE OF credit_limit ON customers FOR EACH ROW WHEN (NEW.credit_limit > 0) BEGIN -- check the credit limit IF :NEW.credit_limit >= 2 * :OLD.credit_limit THEN raise_application_error(-20101,'The new credit ' || :NEW.credit_limit || ' cannot increase to more than double, the current credit ' || :OLD.credit_limit); END IF; END;

First, specify the name of the trigger:

CREATE OR REPLACE TRIGGER customers_update_credit_trg

Next, set the triggering event is BEFORE UPDATE of the credit_limit column on the customers table.

BEFORE UPDATE OF credit_limit ON customers

Then, use the FOR EACH ROW to specify that the trigger is a row-level trigger.

FOR EACH ROW

After that, specify the condition that fires trigger only when the new credit is greater than zero in the WHEN clause:

WHEN (NEW.credit_limit > 0)

Finally, check the new credit from the :NEW variable with the current credit from the :OLD variable, call the procedure raise_application_error to raise an error if it is.

IF :NEW.credit_limit >= 2 * :OLD.credit_limit THEN raise_application_error(-20101,'The new credit ' || :NEW.credit_limit || ' cannot increase to more than double, the current credit ' || :OLD.credit_limit); END IF;

Testing the trigger

First, find the credit limit of the customer id 10:

SELECT credit_limit FROM customers WHERE customer_id = 10;

Here is the output:

CREDIT_LIMIT ------------ 2000

Second, update the credit of the customer 10 to 5000:

UPDATE customers SET credit_limit = 5000 WHERE customer_id = 10;

The output is as follows:

ORA-20101: The new credit 5000 cannot increase more than double, the current credit 2000 ORA-06512: at "OT.CUSTOMERS_UPDATE_CREDIT_TRG", line 4 ORA-04088: error during execution of trigger 'OT.CUSTOMERS_UPDATE_CREDIT_TRG'

It means that the trigger customers_update_credit_trg has been fired as expected.

In this tutorial, you have learned about Oracle row-level triggers and how to use the CREATE TRIGGER statement to create a new row-level trigger.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Statement-level Triggers
Next Oracle INSTEAD OF Triggers

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.