PL/SQL Triggers

Benefits of Triggers

There are many benefits of triggers which include:

1. They automate the creation of derived column values, reducing manual efforts.
2. They ensure referential integrity, maintaining consistency between the tables.
3. Triggers also enable event logging, providing valuable insights into usage patterns.
4. Triggers support synchronous replication of tables, ensuring real-time data consistency across different parts of database.
5. They also contribute to enforcing security measures and safeguarding sensitive information by preventing the execution of invalid transactions.

Creating Triggers

To create a trigger, we can use the below syntax:

1. CREATE [OR REPLACE] TRIGGER name_of_trigger:

• CREATE: This starts the creation of a trigger.
• OR REPLACE: This is an optional clause that allows updating an existing trigger with a similar name.
• TRIGGER trigger_name: This mentions the name of the trigger being created or updated.

2. {BEFORE | AFTER | INSTEAD OF}:

• BEFORE: This means that the trigger gets executed before triggering event
• AFTER: This means that the trigger gets executed after triggering event.
• INSTEAD OF: This is used for triggers on views, allowing us to replace default action with the trigger's defined custom action.

3. {INSERT [OR] | UPDATE [OR] | DELETE}:

This clause mentions the type of DML operation that the trigger will respond to. These are the available options:

• INSERT: The trigger executes in response to an INSERT statement.
• UPDATE: The trigger executes in response to an UPDATE statement.
• DELETE: The trigger executes in response to a DELETE statement.

4. [OF col_name]:

This clause is an optional clause mentioning the name of column affected by the trigger.

5. [ON table_name]:

This clause is an optional clause that associates trigger with a specific table.

6. [REFERENCING OLD AS o NEW AS n]:

This clause is an optional clause allowing the references to old and new values in the trigger body.

7. [FOR EACH ROW]:

This is an optional clause indicating a row-level trigger. This gets executed once for each affected row and without this clause, the trigger is considered a statement-level trigger, executing once independent of number of affected rows.

8. WHEN (condition):

This is an optional clause specifying a condition that, triggers the action defined in the trigger body when true.

Example of PL/SQL Triggers

Let's understand PL/SQL Triggers with the help of an example. We will create the "employees" table having three columns:

Now, let's create a trigger that computes and shows the salary difference whenever an update operation is performed on the "employees" table:

Triggering a Trigger

Let's insert a few of the entries in the "employees" table:

When a record is inserted in the "employee" table, the above-created trigger will be fired and it will display the following output −

In the above output, the old salary is not available and thus the above result comes as null. Now let's update the salary of an employee:

Now, the trigger again will be fired for the update operation on "employees" table:

This is a simple example showing how a trigger can be used to calculate and show changes in a specific column during an insert and update operation on a table.

Types of Triggers in PL/SQL

The triggers can be categorized based on the parameters they take. The different types of triggers are described below:

1) Categorization based on Trigger Level:

ROW Level Trigger: This trigger gets executed for each record affected by a DML statement, such as UPDATE, INSERT, or DELETE.

STATEMENT Level Trigger: This trigger gets executed only once per triggering event statement, it is independent of the number of records affected.

2. Categorization based on Trigger Timing:**

BEFORE Trigger: This trigger gets executed before the occurrence of a specific event, providing an opportunity to update the data before an event takes place.

AFTER Trigger: This trigger gets executed after the particular event, allowing actions to be taken post the event.

INSTEAD OF Trigger: This trigger gets executed for each record affected by a DML statement but replaces the default action of the statement.

3) Categorization based on Trigger Event:

DML Trigger: This trigger gets executed for DML events such as UPDATE, INSERT, or DELETE.

DDL Trigger: This trigger gets executed for Data Definition Language events like DROP, ALTER, or CREATE.

DATABASE Trigger: This trigger gets executed for database-level events such as SHUTDOWN, STARTUP, LOGOFF, and LOGON.