Oracle Statement-level Triggers

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

Introduction to Oracle Statement-level triggers

A statement-level trigger is fired whenever a trigger event occurs on a table regardless of how many rows are affected. In other words, a statement-level trigger executes once for each transaction.

For example, if you update 1000 rows in a table, then a statement-level trigger on that table would only be executed once.

Due to its features, a statement-level trigger is not often used for data-related activities like auditing the changes of the data in the associated table. It’s typically used to enforce extra security measures on the kind of transaction that may be performed on a table.

By default, the statement CREATE TRIGGER creates a statement-level trigger when you omit the FOR EACH ROW clause.

Here is the basic syntax of creating a statement-level trigger:

CREATE [OR REPLACE] TRIGGER trigger_name
    {BEFORE | AFTER } triggering_event 
    ON table_name
    [FOLLOWS | PRECEDES another_trigger]
    [ENABLE / DISABLE ]
    [WHEN condition]
DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;
Code language: SQL (Structured Query Language) (sql)

Note that the meanings of each clause are already explained in the trigger tutorial:

Oracle Statement-level Trigger example

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

Suppose, you want to restrict users to update credit of customers from 28th to 31st of every month so that you can close the financial month.

To enforce this rule, you can use this statement-level trigger:

CREATE OR REPLACE TRIGGER customers_credit_trg
    BEFORE UPDATE OF credit_limit  
    ON customers
DECLARE
    l_day_of_month NUMBER;
BEGIN
    -- determine the transaction type
    l_day_of_month := EXTRACT(DAY FROM sysdate);

    IF l_day_of_month BETWEEN 28 AND 31 THEN
        raise_application_error(-20100,'Cannot update customer credit from 28th to 31st');
    END IF;
END;
Code language: SQL (Structured Query Language) (sql)

Let’s examine the trigger.

First, create a new trigger customers_credit_trg. The OR REPLACE modifies the trigger if it already exists:

CREATE OR REPLACE TRIGGER customers_credit_trg
Code language: SQL (Structured Query Language) (sql)

Next, instruct the Oracle to fire the trigger only before update event for the credit_limit column of the customers table. If you update values in other columns rather than the credit_limit column, the trigger will not execute.

BEFORE UPDATE OF credit_limit  
    ON customers
Code language: SQL (Structured Query Language) (sql)

Then, declare a variable to hold the current day of the month:

DECLARE
    l_day_of_month NUMBER;
Code language: SQL (Structured Query Language) (sql)

After that, get the current day of the month using the EXTRACT() function:

l_day_of_month := EXTRACT(DAY FROM sysdate);
Code language: SQL (Structured Query Language) (sql)

Finally, check if the current day of the month is between 28th and 31st, use the procedure raise_application_error to raise a user-defined error:

IF l_day_of_month BETWEEN 28 AND 31 THEN
    raise_application_error(-20100,'Cannot update customer credit from 28th to 31st');
END IF;
Code language: SQL (Structured Query Language) (sql)

Testing the Oracle statement-level trigger

The following statement uses the UPDATE statement to increase the credit limit of all customer 10%:

UPDATE 
    customers 
SET 
    credit_limit = credit_limit * 110;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error:

ORA-20100: Cannot update customer credit from 28th to 31st
ORA-06512: at "OT.CUSTOMERS_CREDIT_TRG", line 8
ORA-04088: error during execution of trigger 'OT.CUSTOMERS_CREDIT_TRG'
Code language: SQL (Structured Query Language) (sql)

Note that Oracle automatically rollbacks the update because we call the raise_application_error procedure inside the trigger.

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