EDUCBA

Home Software Development Software Development Tutorials PL/SQL Tutorial Triggers in PL/SQL

Triggers in PL/SQL

Priya Pedamkar
Article byPriya Pedamkar

Updated March 18, 2023

Triggers in PL/SQL

 

 

Introduction to Triggers in PL/SQL

Database triggers are the PL/SQL code that executes based on an event in the database such as an INSERT, UPDATE, Alter, Drop, Login, Logoff, etc. Database triggers help us in keeping the SQL codes simple and short. Database triggers are useful in managing all changes and keeping track of all those changes using Update, Delete, Alter, login, Logoff, etc. in Database, Database schemas, and its tables.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

We have 3 Types of DB Triggers:

1. DML (Data Manipulation Language): DML triggers execute on INSERT, UPDATE & DELETE in the tables.

2. DDL (Data Definition Language): This trigger executes on Create, Alter, Drop, Analyse, Audit, Grant, etc.

3. Database Event: Execution of DB event triggers based on LOGON, LOGOFF, Suspend, Database startup, Database Shut down and other DB errors.

Here is one more characteristic of DB Triggers:

A DB Trigger: It is PL/SQL code linked to a table view or DB event.
Executes based on DB activity

  • Before/After, INSERT, UPDATE, DELETE.
  • Executed once per triggering events or once per row affected by the trigger.

Execute based on startup/shutdown/ system error/ data load shutdown.

How to Create Triggers in PL/SQL?

Here are the following steps to create triggers in PL/SQL

1. DML Triggers

This trigger is further divided into two types:

  • After triggers/For Triggers
  • Instead of triggers

AFTER trigger fires after triggering action. DELETE, UPDATE and INSERT statements are an example of After triggers.
INSTEAD Of triggers acts instead of the action. DELETE, UPDATE and INSERT statements are also part of it.

Example:

We will use the Employee and EmployeeAudit tables for our examples.

SQL Script to create an Employee table:

CREATE TABLE Employee
(
Id int Primary Key,
Name nvarchar(30),
Salary int,
Gender nvarchar(10),
DepartmentId int
)
Insert data into Employee table
Insert into Employee values (1,'Prakash', 5000, 'Male', 3)
Insert into Employee values (2,'Robert', 3400, 'Male', 2)
Insert into Employee values (3,'Anji', 6000, 'Female', 1)

Employee Table

Employee Table

SQL Script to create EmployeeAudit table:

CREATE TABLE EmployeeAudit
(
Id int identity(1,1) primary key,
AuditData nvarchar(1000)
)

we will capture the ID and the date and time Whenever a new Employee is added in EmployeeAudit table. This is the easiest program to define AFTER TRIGGER for the INSERT event.

2. DDL Triggers

It is mainly used to prevent particular changes to your database schema

Syntax

CREATE TRIGGER trigger_name
ON {DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}

trigger_name is to Specify the defined name of the trigger after the CREATE TRIGGER keywords. also, you don’t need to specify a Database schema because it is not related to an actual database table or view.

Database | All Server: If the trigger fires on the server-scoped events than we can use ALL SERVER. Use DATABASE if the trigger fires on database-scoped events

ddl_trigger_option: It specifies ENCRYPTION and/or EXECUTE AS clause.

event_type | event_group: The event_group is a group of event_type event such as DDL_TABLE_EVENTS & The event_type indicates a DDL event that causes the trigger to fire such as, ALTER_TABLE, CREATE_TABLE, etc.

Example:

Trigger execution in response to the CREATE_TABLE DDL event.

CREATE TRIGGER MyFirstTrigger
ON Database
FOR CREATE_TABLE
AS
BEGIN
Print 'New table created'
END

Check if the trigger has been created under the Database trigger folder and if you do not get it please refresh the folder (Database Trigger).

3. Database Event

It can be used for any DB events such as LOGON, LOGOFF, Suspend, Database startup, Database Shut down

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {database_event} ON {DATABASE | SCHEMA}
DECLARE
...some code...
END;

trigger_name is to Specify the defined name of the trigger after the CREATE TRIGGER keywords.

database_event basically event occurs in Databases such as logoff, log in, Shutdown, etc. We can select which Database or schema this trigger will work.

Example: Logoff trigger

Here we can demonstrate the LOGOFF Database Event Trigger. This trigger will create one record in a table (logoff) when anyone disconnects.
This trigger will record the name of the user and the time of disconnection.

Creating a logoff table:

create table logoff_table (
who varchar2(30),
when date
);

Creating a Logoff Trigger:

create trigger trg_logoff
before logoff on database
begin
insert into logoff_table values(sys_context('userenv','session_user'), sysdate);
end;
/

Here in the above trigger, we have created Tigger to monitor the Logoff of any user. This trigger will get executed post logging off of Database users. Logoff table will store some details User Name and it’s logoff time {You can decide details as per requirement}.

Same way, We can create another trigger that will monitor and store Login Details for any particular Database. Database event triggers are helpful in troubleshooting.
All the table names are requirement specific, we can change their names as per requirements.

Advantage of Triggers in PL/SQL

Below are mentioned the advantages:

  • Good for audit trails.
  • Good for manipulating data.
  • Enforce complex security schemes.
  • Enforce business rules.
  • Prevent changes in Database and schema.
  • PL/SQL code is shorter using triggers.
  • Keep track of the changes in Database, Schema and DB Tables.
  • Managing action records in Database is helpful in troubleshooting.
  • No need to check the changes in the Database manually, the trigger always takes action itself on a particular event occurrence.

Conclusion

Here is this chapter, we have learned about types of trigger such as DDL, DML, and Database event triggers. We have also learned while a DDL trigger can be used for INSERT, UPDATE, DELETE, etc. Statements and DML triggers are designed for Create, Alter, Drop, etc… within Database tables but Database event triggers works when there is any Logon Logoff, Suspend, Database Startup action performed in Database.

Syntax of each trigger is easy to understand and it can be converted into PL/SQL code easily. PL/SQL Program such as Logoff triggered has given an overview of how the Database trigger will work in case any user logoff from its Database. In DML Trigger, we have demonstrated how AFTER Trigger will work once there is a new entry of any new employee. DDL Triggers are useful in preventing any particular changes in Database and schema.

Recommended Articles

This is a guide to Triggers in PL/SQL. Here we discuss How to Create Triggers in PL/SQL with Examples and Advantages. You may also look at the following article.

  1. PL/SQL Commands
  2. Data Manipulation Language
  3. Data Definition Language
  4. Triggers in SQL