EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Triggers
 

Oracle Triggers

Priya Pedamkar
Article byPriya Pedamkar

Updated March 23, 2023

Oracle Triggers

 

 

Introduction to Oracle Triggers

Oracle Triggers are nothing but chunk of code in a PL/SQL, which is saved in the Oracle database also can be reused and repurposed whenever the user needs it. This can be used in both DDL (Data Definition Language) and DML (Data Manipulation Language) codes. The Parameters allowed in triggers can be classified into four types, namely ‘trigger_name’ for created a trigger with certain name, ‘trigger_time’ for mentioning the time to trigger the event, ‘trigger_event’ for defining the type of event, and ‘tbl_name’ to add the table name for which the trigger is being created and used for.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of Trigger

So, after a little introduction to Trigger. Let us now get to know the syntax of the trigger.

Below we can see the syntax of how to create a trigger:

CREATE  [OR REPLACE] TRIGGER  trigger_name
trigger_time trigger_event
ON tbl_name [ FOR EACH ROW] Declare
----- Variable declarations
BEGIN
----trigger body
EXCEPTION
----exception handling code
END;

Parameters

  • trigger_name: It is the name of the trigger we want to give while creating a trigger.
  • trigger_time: It states whether the trigger will be triggered BEFORE or AFTER the event.
  • trigger_event: It states the event whether it is INSERT event or DELETE event or UPDATE event.
  • tbl_name: The table name that the trigger is created on.

So, after getting to know the syntax of a trigger. Let us now understand how a trigger works.

How does a trigger work in oracle?

Triggers are like procedures that are implicitly executed when an INSERT, DELETE, UPDATE statement is fired on a table whether by a SQL statement or by a user-defined procedure. There is but a fundamental difference between a procedure and trigger which is in case of procedure it is explicitly executed explicitly by a user, application or trigger whereas a trigger is executed implicitly when an UPDATE, DELETE or INSERT statement are executed. To understand let us, for example, there is an AFTER INSERT trigger which is implicitly triggered after an insert statement is executed on a particular table. So whatever statements are present in the body of the trigger get executed after a row is inserted in that particular table. So, whenever a row is inserted in that table the trigger is fired implicitly.

One important note that we should know is that we cannot create a BEFORE trigger on a view.

So, now we are going to understand the trigger execution a bit more with the help of some practical examples.

Examples

The first example that we are going to witness:

Oracle Triggers

1. Before Insert

This means that Oracle will fire the trigger before there is an actual insert operation executed on the table.

In this example, we are going to look into a trigger that will be triggered before we are doing an insert operation on the employees’ table. Let us look at the code below for a better understanding.

Code:

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
INSERT INTO VEHICLE VALUES('VH010','Honda','100','Kanpur');
END;

If we look at the above example we can see that this trigger is applied on the employee table and it will be fired whenever a BEFORE INSERT operation happens on the employee table. So, whenever an insert command is executed by any user. This trigger will fire just before the insert operation is executed and it will first insert the given data into the Vehicle table before data is inserted in the employee table.

2. After Insert

In this type of trigger, the trigger will be fired after the execution of the INSERT statement.

In this example, we are going to look into a trigger that will be triggered after an insert operation on the employee table. Let us look at the code below for better understanding.

Code:

CREATE OR REPLACE TRIGGER after_insert_employee
AFTER INSERT
ON employee
FOR EACH ROW
BEGIN
INSERT INTO VEHICLE VALUES(:new.vehicle_id, :new.vehicle_name, :new.sale, :new.city);
END;

In the above example, the trigger is applied to the employee table and it will be fired after an insert operation is executed in the employee table. If you see the code whenever an insert operation is successfully executed on an employee table, this trigger will be implicitly fired to insert the new values into the vehicle table.

3. After Delete

In this type of trigger, the trigger will be fired after a delete operation is executed on the concerned table.

In this example below, we are going to look at the trigger execution of a trigger which is fired after we delete a row in the employee table. This trigger will get fired for each row deletion in the employee table. Let us look at the example below.

Code:

CREATE OR REPLACE TRIGGER after_delete_employee
AFTER DELETE
ON employee
FOR EACH ROW
BEGIN
delete from vehicle where vehicle_id = :old.vehicle_id;
END;

If we see the code of the above example, we can see that the trigger is applied to the employee table. Hence it will get fired after every delete operation on the employee table. In our case, after a delete operation is executed on the employee table, this trigger will delete a record from the vehicle table based on the vehicle_id.

4. Drop A Trigger

As the name suggests in this example we are going to see with the help of an example of how to drop a trigger that has been created and stored in the database. In the below example we are going to drop the after delete trigger. Since we did not discuss the syntax of the drop trigger. Let us look at the syntax first.

Code:

DROP TRIGGER trigger_name;
Now below is an example for the same
DROP TRIGGER after_delete_employee;

As we can see we just append the trigger name with the DROP statement to drop a trigger from the database.

Conclusion

In this article, we learned about oracle triggers with their syntaxes. We got to know the working of triggers and we also discussed a few examples to broaden our knowledge on different types of triggers that can be used/are used in the industry to solve business problems.

Recommended Articles

This is a guide to Oracle Triggers. Here we discuss how does a trigger work in oracle? long with respective examples.  You can also go through our other related articles to learn more–

  1. Oracle DBA Certification
  2. Triggers in PL/SQL
  3. Oracle String Functions
  4. Cursor in MySQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW