EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Triggers
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Oracle Triggers

By Priya PedamkarPriya Pedamkar

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.

Syntax of Trigger

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
Popular Course in this category
Oracle Training (17 Courses, 8+ Projects)
  17 Online Courses |  8 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
Financial Analyst Masters Training Program4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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 Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more