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.

CREATE TABLE table1
(
    col1    NUMBER
);

CREATE TABLE table2
(
    col1    DATE,
    col2    VARCHAR2 (2000)
);

Step 2: Create Trigger script.

CREATE TRIGGER tab1_trig
    AFTER INSERT
    ON table1
BEGIN
    INSERT INTO table2
         VALUES (SYSDATE, 'Insert into table1');
EXCEPTION WHEN OTHERS 
THEN 
   DBMS_OUTPUT.PUT_LINE ('Error During Trigger creation: )'||SQLERRM);
END;

Step 3: Generate the Triggering event.

INSERT INTO table1
     VALUES (1);

Step 4: Check the data in the Table 2.

SELECT * FROM table2;

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: