Trigger in PL/SQL – PL/SQL Tutorials

In this tutorial, we will discuss the Database Triggers in PL/SQL briefly. Triggers are stored programs of PL/SQL Database, which are automatically executed or fired when some (DML statements) events occur.

Also Must Read: Cursor in PL/SQL

What’s inside:
What is the trigger in PL/SQL?
What are the uses of Triggers?
Database Triggers VS Procedures
Database Triggers VS Declarative Integrity Constraints
How To Apply Database Triggers
Types of TRIGGER in PL/SQL
How To delete Database Triggers
FAQs

What is the trigger in PL/SQL?

The Oracle engine allows the definition of procedures that are implicitly excecated, when an insert, update or delete is issued against a table from SQL* plus or through an application. These procedures are called database triggers.

The trigger in PL/SQL is not standalone; they are always called by different DML statements, or we can state that it is always executed or fired when some events will occur.

What are the uses of Triggers?

As we know, The Oracle engine supports the database Triggers it provides a highly customize database management system.

  1. A trigger can permit DML statements against a table only if they are issued, during regular business hours.
  2. A trigger can also be used to keep an audit trail along with the operation performed and the time on which the operation was performed.
  3. It is used to prevent invalid transactions.


Database Triggers VS Procedures

There are very few differences between database triggers and procedures. Triggers do not accept parameters, whereas procedures can. A trigger is executed implicitly by the Oracle engine itself upon modification of an
associated table or its data. To run a procedure, it has to be explicitly called by a user.

Database Triggers VS Declarative Integrity Constraints

Triggers, as well as declarative integrity constraints, can be used to constrain data input. However, both have
significant differences, as mentioned below:

  • A declarative integrity constraint is a statement about a database that is always true. A constraint applies to exist data in the table and any statement that manipulates the table
  • a Triggers constrain what a transaction can do. A trigger does not apply to data loaded before the trigger was created, so it does not guarantee all data in a table conforms to the rules established by an associated trigger
  • A trigger enforces a transitional constraint, which cannot be enforced by a declarative integrity constraint
trigger in pl/sql


How To Apply Database Trigger in PL/SQL

A trigger has three basic parts:

  1. A triggering event or statement
  2. A trigger restriction
  3. A trigger action

Each part of the trigger is explained below:
Triggering Event or Statement: It is a SQL statement that causes a trigger to be fired. It can be INSERT, UPDATE, or DELETE statement for a specific table. (i.e. Table write operations)
Trigger Restriction: A trigger restriction specifies a Boolean (logical) expression that must be TRUE for the trigger to fire. It is
an option available for triggers that are fired for each row. Its function is to conditionally control the
execution of a trigger. A trigger restriction is specified using a WHEN clause.
Trigger Action: A trigger action is the PL/SQL code to be executed when a triggering statement is encountered, and any
trigger restriction evaluates to TRUE. The PL/SQL block can contain SQL and PL/SQL statements can
define PL/SQL language constructs and can call stored procedures. Additionally, for row triggers, the
statements the PL/SQL block have access to column values (: new and gold) of the current row being
processed.

Types of TRIGGER in PL/SQL

While defining a trigger, the number of times the trigger action is to be executed can be specified. This can be once for every row affected by the triggering statement (such as might be fired by an UPDATE a statement that updates many rows), or once for the triggering statement, no matter how many rows it affects.

trigger in pl/sql

Row Triggers

A row trigger is fired each time a row in the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by
the UPDATE statement. If the triggering statement affects no rows, the trigger is not executed at all. Row triggers should be used when some processing is required whenever a triggering statement affects a single
row in a table.

Statement Triggers

A statement trigger is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects (even if no rows are affected). Statement triggers should be used when a triggering statement affects rows in a table, but the processing required is completely independent of the number of rows affected.

Before VS After Triggers

When defining a trigger, it is necessary to specify the trigger timing, i.e. specifying when the triggering action is to be executed in relation to the triggering statement. BEFORE and AFTER apply to both row and the statement triggers.

Before Triggers


BEFORE triggers execute the trigger action before the triggering statement. These types of triggers are commonly used in the following situations:

  • BEFORE triggers are used when the trigger action should determine whether or not the statement’s triggering should be allowed to complete. By using a BEFORE trigger, the user can eliminate unnecessarily processing of the triggering statement
  • BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement

After Triggers

AFTER trigger executes the trigger action after the triggering statement is executed.

These types of triggers are commonly used in the following situations:

  • AFTER triggers are used when the triggering statement should complete before executing the trigger action
  • If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations Triggers

Using the options explained above, four types of triggers could be created.

BEFORE statement trigger

Before executing the triggering statement, the trigger action is executed.

BEFORE row trigger

Before modifying each row affected by the triggering statement and applying appropriate integrity constraints, the trigger is executed.

AFTER Statement Trigger

After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.

AFTER Row Trigger

After modifying each row affected by the triggering statement and applying appropriate integrity constraints, the trigger action is executed for the current row. Unlike BEFORE row triggers, AFTER row triggers have rows locked.

Syntax For Creating A Trigger in PL/SQL:

CREATE OR REPLACE  TRIGGER <trigger_name> 
{BEFORE, AFTER, INSTEAD OF }  
{DELETE, INSERT, UPDATE [OF Column_name, …]}
 
  
ON <table_name>  
[REFERENCING OLD AS old, NEW AS new]  
[FOR EACH ROW  [ WHEN (Condition) ]]  
   
DECLARE 
  <variable Declaration-statements> 
BEGIN  
   <PL/SQL block /Executable-statements> 
EXCEPTION 
   <Exception-handling PL/SQL block> 
END; 

Syntax explanation:

  • CREATE OR REPLACE TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
  • {BEFORE, AFTER, INSTEAD OF} − This defines when the trigger will be executed. The INSTEAD OF clause uses for creating the trigger on a view.
  • {DELETE, INSERT, UPDATE } − This defines the DML operation.
  • [OF Column_name, …] − This refers to the column name that will be updated in the program.
  • [ON <table_name>] − This refers to the table’s name associated with the trigger.
  • [REFERENCING OLD AS old, NEW AS new] − This allows us to refer new and old values for the various DML statements, for instance, INSERT, UPDATE, and DELETE.
  • [FOR EACH ROW] − It specifies a row-level trigger.
  • WHEN (condition) − This condition provides a condition for rows for which the trigger would be fire. This clause is valid only for row-level triggers.

A simple example of a trigger in PL/SQL:

CREATE OR REPLACE TRIGGER display_salary 
BEFORE DELETE OR INSERT OR UPDATE ON customertable
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   vsal_diff number; 
BEGIN 
   vsal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || vsal_diff); 
END; 
/ 

How to delete trigger in PL/SQL database?

We can delete or drop a trigger in PL/SQL database by using the following syntax.
Syntax:

DROP TRIGGER < Trigger_Name>

where Trigger_Name is the name of the trigger to be dropped.


FAQs:

What are cascading triggers?
When a trigger is fired, an SQL statement inside the trigger’s PL/SQL code block can also fire the same or some other trigger. This whole procedure is called cascading triggers.
A package can include ……….. and ……………..
Function and Procedure
A ………… trigger is fired each time a row in the table is affected by the triggering statement.
A row. trigger is fired each time a row in the table is affected by the triggering statement.
Triggers are friendly implicitly? True or false.
True, Triggers are friendly implicitly

Here we have discussed the trigger in PL/SQL, their types, how to create them, etc. In the coming tutorial, we will present the package in PL/SQL. So, please be with us.

Thank you 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *