Oracle Trigger

Summary: in this tutorial, you will learn about another named PL/SQL block called an Oracle trigger. You will also learn about different characters of triggers and their usage in the database.

What is an Oracle trigger

A trigger is a named PL/SQL block stored in the Oracle Database and executed automatically when a triggering event takes place. The event can be any of the following:

  • A data manipulation language  (DML) statement executed against a table e.g., INSERT, UPDATE, or DELETE. For example, if you define a trigger that fires before an INSERT statement on the customers table, the trigger will fire once before a new row is inserted into the customers table.
  • A data definition language (DDL) statement executes e.g., CREATE or ALTER statement. These triggers are often used for auditing purposes to record changes of the schema.
  • A system event such as startup or shutdown of the Oracle Database.
  • A user event such as login or logout.

The act of executing a trigger is also known as firing a trigger. We say that the trigger is fired.

Oracle trigger usages

Oracle triggers are useful in many cases such as the following:

  • Enforcing complex business rules that cannot be established using integrity constraint such as UNIQUE, NOT NULL, and CHECK.
  • Preventing invalid transactions.
  • Gathering statistical information on table accesses.
  • Generating value automatically for derived columns.
  • Auditing sensitive data.

How to create a trigger in Oracle

To create a new trigger in Oracle, you use the following CREATE TRIGGER statement:

CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } triggering_event ON table_name [FOR EACH ROW] [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)

Let’s examine the syntax of the CREATE TRIGGER statement in more detail.

A trigger has two main parts: header and body.

The following illustrates the trigger header:

CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER } triggering_event ON table_name [FOR EACH ROW] [FOLLOWS | PRECEDES another_trigger] [ENABLE / DISABLE ] [WHEN condition]
Code language: SQL (Structured Query Language) (sql)

And this is the trigger body:

DECLARE declaration statements BEGIN executable statements EXCEPTION exception_handling statements END;
Code language: SQL (Structured Query Language) (sql)

As you can see, the trigger body has the same structure as an anonymous PL/SQL block.

1) CREATE OR REPLACE

The CREATE keyword specifies that you are creating a new trigger. The OR REPLACE keywords are optional. They are used to modify an existing trigger.

Even though the OR REPLACE keywords are optional, they appear with the CREATE keyword in most cases.

For example, if today you define a new trigger named trigger_example:

CREATE TRIGGER trigger_example ...
Code language: SQL (Structured Query Language) (sql)

And on the next day, you decide to modify this trigger.

If you do not include the OR REPLACE keywords, you will receive an error message indicating that the name of your trigger is already used by another object:

CREATE TRIGGER trigger_example ...
Code language: SQL (Structured Query Language) (sql)

Therefore, the CREATE OR REPLACE keywords will replace an existing trigger if it already exists and create a new trigger if the trigger does not:

CREATE OR REPLACE trigger_example ....
Code language: SQL (Structured Query Language) (sql)

2) Trigger name

Specify the name of the trigger that you want to create after the CREATE OR REPLACE keywords.

3) BEFORE | AFTER

The BEFORE or AFTER option specifies when the trigger fires, either before or after a triggering event e.g., INSERT, UPDATE, or DELETE

4) ON table_name

The table_name is the name of the table associated with the trigger.

5) FOR EACH ROW

The clause FOR EACH ROW specifies that the trigger is a row-level trigger. A row-level trigger fires once for each row inserted, updated, or deleted.

Besides the row-level triggers, we have statement-level triggers. A statement-trigger fire once regardless of the number of rows affected by the triggering event. If you omit the FOR EACH ROW clause, the CREATE TRIGGER statement will create a statement-level trigger.

6) ENABLE / DISABLE

The ENABLE / DISABLE option specifies whether the trigger is created in the enabled or disabled state. Note that if a trigger is disabled, it is not fired when the triggering event occurs.

By default, if you don’t specify the clause ENABLE / DISABLE , the trigger is created with the enabled state.

7) FOLLOWS | PRECEDES another_trigger

For each triggering event e.g., INSERT, UPDATE, or DELETE, you can define multiple triggers to fire. In this case, you need to specify the firing sequence using the FOLLOWS or PRECEDES option.

Let’s create a trigger to see understand how it works.

Creating an Oracle trigger example

Suppose we want to record actions against the customers table whenever a customer is updated or deleted. In order to do this:

First, create a new table for recording the UPDATE and DELETE events:

CREATE TABLE audits ( audit_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR2(255), transaction_name VARCHAR2(10), by_user VARCHAR2(30), transaction_date DATE );
Code language: SQL (Structured Query Language) (sql)

Second, create a new trigger associated with the customers table:

CREATE OR REPLACE TRIGGER customers_audit_trg AFTER UPDATE OR DELETE ON customers FOR EACH ROW DECLARE l_transaction VARCHAR2(10); BEGIN -- determine the transaction type l_transaction := CASE WHEN UPDATING THEN 'UPDATE' WHEN DELETING THEN 'DELETE' END; -- insert a row into the audit table INSERT INTO audits (table_name, transaction_name, by_user, transaction_date) VALUES('CUSTOMERS', l_transaction, USER, SYSDATE); END; /
Code language: SQL (Structured Query Language) (sql)

The following clause:

AFTER UPDATE OR DELETE ON customers
Code language: SQL (Structured Query Language) (sql)

will fire the trigger after a row in the table  customers is updated or deleted.

Inside the trigger, we determine the current action whether it is UPDATE or DELETE and insert a row into the audits table.

The following statement updates the credit limit of the customer 10 to 2000.

UPDATE customers SET credit_limit = 2000 WHERE customer_id =10;
Code language: SQL (Structured Query Language) (sql)

Now, check the contents of the table audits to see if the trigger was fired:

SELECT * FROM audits;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Trigger - AFTER UPDATE example

As you can see clearly from the output, the trigger customers_audit_trg was fired so that we have a new row inserted into the audits table.

This DELETE statement deletes a row from the customers table.

DELETE FROM customers WHERE customer_id = 10;
Code language: SQL (Structured Query Language) (sql)

And view the data of the audits table:

SELECT * FROM audits;
Code language: SQL (Structured Query Language) (sql)
Oracle Trigger - AFTER DELETE example

The output showed that a new row has been inserted. It means that the DELETE action fired the trigger customer_audit_trg.

In this tutorial, you have learned about Oracle triggers and how to create new triggers using the CREATE TRIGGER statement.

Was this tutorial helpful?