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 Statement-level Triggers

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;

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:

customers table

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;

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

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

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

DECLARE l_day_of_month NUMBER;

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

l_day_of_month := EXTRACT(DAY FROM sysdate);

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;

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;

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'

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Trigger
Next Oracle Row-level 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.