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

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

And this is the trigger body:

DECLARE
    declaration statements
BEGIN
    executable statements
EXCEPTION
    exception_handling statements
END;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

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

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

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

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

The following clause:

AFTER UPDATE OR DELETE ON customersCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

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

SELECT * FROM audits;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

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

And view the data of the audits table:

SELECT * FROM audits;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output showed that a new row had 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.