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.
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
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: