Oracle Triggers

What are Triggers in Oracle? 

Oracle Triggers are similar to PL/SQL Stored procedures that can be stored in the database and are explicitly fired whenever a table or a view is changed by a user action or a database system actions occur.

A trigger can be fired when one of the following happens on a table:

DML (Data Manipulation Language) statements like  ‘Insert“, “Update” or “Delete” are run against the table.

DDL (Data Definition Language) statements like “Create“, “Alter” or “Drop” are issued either by a particular schema / user in the database.

Note: Using Triggers is effective to modify objects in the database, but they have to be used carefully. Excessive use of triggers may cause a SQL statement inside that trigger to fire other triggers – calling an effect called Cascading Triggers.

Parts of a Trigger: 

  • Triggering statement: A triggering statement is the SQL Statement or database event that causes a trigger to fire.
  • Triggering Restriction: A trigger restriction results in a Boolean expression i.e. either a true or a false that specifies whether the trigger should fire or not. A trigger fires if the Triggering restriction results in “True“. A “False” or an “Unknown” result doesn’t fire a trigger.
  • Trigger Action: A trigger action is the pl/sql statement (or anonymous block of code) that runs when the Triggering restriction results as True.

Example:

The following code is an example of a trigger that insert an Audit record into Table2 whenever an Insert event occurs into Table1.

Step 1: Create Table1 and Table2 for this test case.

Step 2: Create Trigger script.

Step 3: Generate the Triggering event.

Step 4: Check the data in the Table 2.

Step 5: Result

Explanation: 

In the example above:

  • The Triggering statement is: AFTER INSERT ON table1.

The trigger fires immediately after an insert operation occurs on the Table1.

  • The Triggering Action is to Insert values in the Table2 when an Insert happens on Table1.

Different Types of Triggers

  • Row Triggers:
  • Statement Triggers:
  • BEFORE and AFTER Triggers:
  • INSTEAD OF Triggers:

 

Leave a Reply

  • (will not be published)