PL/SQL Triggers – Oracle PL/SQL Tutorial

Create Trigger

The Oracle PL/SQL triggers are procedures that are associated with tables, and are called whenever an event occurs. The event statements may include INSERT, UPDATE or DELETE.

When an event occurs on a table, a trigger is fired and an action is performed. Oracle PL/SQL triggers are commonly used to enforce business rules, maintain data integrity or perform auditing.

There are two types of PL/SQL triggers: DML triggers and System triggers.
DML triggers execute when a DML statement, such as INSERT, UPDATE, or DELETE, is issued against a table or view.
System triggers execute in response to system events, such as database STARTUP or SHUTDOWN.

You can create a trigger on a table, schema, or database.
PL/SQL Triggers on tables can fire for INSERT, UPDATE, and DELETE statements.
Triggers on schemas fire for CREATE, ALTER, or DROP statements issued against any object in the schema.
Triggers on databases fire in response to database events, such as STARTUP or SHUTDOWN.

Benefit of Oracle PL/SQL Triggers

Oracle PL/SQL Triggers are useful for:
– Enforcing complex business rules that cannot be enforced using integrity constraints
– Generating derived column values
– Logging changes to table data
– Enforcing referential integrity across nodes in a distributed database
– Performing sophisticated security authorizations
– Preventing undesired actions such as deleting rows from a base table

Syntax of Oracle PL/SQL Triggers

CREATE OR REPLACE TRIGGER my_trigger_name 

  {BEFORE | AFTER | INSTEAD OF} 

  {INSERT | UPDATE | DELETE} 

    [OF my_column_name] 

ON my_table_name 

 [REFERENCING OLD AS old NEW AS new] 

[FOR EACH ROW] 

WHEN condition

DECLARE

   -- declare statements 

BEGIN 

   -- sql statements 
   -- pl/sql statements  

EXCEPTION
  
   -- exception statements 

END;
/

Explanation of the syntax:

– The CREATE TRIGGER statement is used to create a new trigger.
– The OR REPLACE clause allows you to create a new trigger without first dropping the old one.
– The BEFORE|AFTER clause specifies when the trigger is fired. It can be either before or after the triggering event.
– The INSERT|UPDATE|DELETE clause specifies which DML operation will fire the trigger.
– The ON clause specifies the table or view to which the trigger applies.
– The REFERENCING clause declares variables that represent the old and new values of the columns being updated by the triggering event. These variables can be used in the trigger body.
– The FOR EACH ROW clause specifies that the trigger is fired once for each row that is inserted, updated, or deleted by the triggering event.
– The WHEN clause specifies a condition that must be true for the trigger to fire.
– The DECLARE section is used to declare local variables.
– The BEGIN…END section contains the PL/SQL code that is executed when the trigger fires.
– The / character is used to end the SQL*Plus command.

Alter Trigger

You can enable and disable triggers. Disabling a trigger prevents it from firing. Enabling a trigger allows it to fire.
You can also alter the firing time of a trigger or change its definition without dropping and recreating it.

ALTER TRIGGER trigger_name COMPILE;

ALTER TRIGGER trigger_name RENAME TO new_trigger_name; 

ALTER TRIGGER trigger_name ENABLE; 

ALTER TABLE table_name ENABLE ALL TRIGGERS;

ALTER TRIGGER trigger_name DISABLE;  

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Types of Oracle PL/SQL Triggers

Here are the types of triggers you can use:
– Statement level triggers
– Row level triggers
– Instead of triggers
– Before triggers
– After triggers

Statement Level Triggers:

Statement level triggers fire once for each SQL statement, no matter how many rows are affected by that statement.
For example, if an UPDATE statement updates 100 rows, a statement level trigger on that table would fire just once.

Row Level Triggers:

Row level triggers fire once for each row affected by a SQL statement.
For example, if an UPDATE statement updates 100 rows, a row level trigger on that table would fire 100 times-once for each row.

Before Triggers:

Before triggers are fired before the triggering event occurs.
They can be used to set values, perform calculations, or validate data.
For example, you could use a before trigger to calculate an employee’s salary based on the number of hours worked.

After Triggers:

After triggers are fired after the triggering event occurs.
They can be used to set values or perform calculations on data that has been modified by the triggering event.
For example, you could use an after trigger to calculate an employee’s salary based on the number of hours worked.

Drop Trigger

The syntax to DROP TRIGGER is:

DROP trigger trigger_name;

View Trigger

SELECT TRIGGER_NAME FROM USER_TRIGGERS;

Disable Triggers

To temporarily disable triggers, you can use the DISABLE command. This prevents the triggers from executing when their associated events occur. The syntax for disabling a trigger is as follows:

ALTER TRIGGER trigger_name DISABLE;

Enable Triggers

After disabling a trigger, you may want to re-enable it when needed. You can use the ENABLE command to enable triggers. The syntax is similar:

ALTER TRIGGER trigger_name ENABLE;

Mutating Table Error

The mutating table error is a common issue encountered in Oracle PL/SQL. It occurs when a trigger attempts to reference the same table that the trigger is associated with, resulting in an inconsistency or conflict in the data.