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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

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;
...
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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:

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

First, specify the name of the trigger:

CREATE OR REPLACE TRIGGER customers_update_credit_trg 
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

BEFORE UPDATE OF credit_limit
ON customers
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

FOR EACH ROW
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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)
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Testing the trigger

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

SELECT credit_limit 
FROM customers 
WHERE customer_id = 10;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

CREDIT_LIMIT
------------
       2000
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

UPDATE customers
SET credit_limit = 5000
WHERE customer_id = 10;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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'
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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.