What are Oracle triggers with Examples? | Oracle triggers example

In my previous article I have given the details about the Oracle procedures and functions in detail. In this article I would like to give heads up on Oracle triggers and examples of oracle triggers in detail. I would like to give you the details on the usages of triggers and real life industry use of trigger. There are two types of triggers one is Row level trigger and other is statement level triggers. I would like to explain you the example and concept of triggers in detail.

What you will find in this article?

1.Concept of Oracle Triggers with example

2.Types of triggers with examples

3.Business uses of triggers

What is Oracle triggers with examples?

In this section i would like to give you the oracle triggers concept in detail and will explain you simple example of where it is used.

Things to remember :

Definition : Trigger is nothing but a PL/SQL block which is used to handle the specified events or conditions related to table insertion ,updating or deletion. It is fired in a response to specific event.

Execution of trigger : The oracle trigger will be executed automatically whenever the specified condition will occur.

Example :

Lets take very common example of the trigger. If we want to open the bank account and the limit of the balance will be 500. Then we require to write a trigger before inserting the data in the specified table. The trigger will check the condition before inserting the data. So here the event is Insertion of data and condition is 500 balance. If the balance is 500 then it will insert data and after that we need to update logs table. So here event is updating log tables and the condition is Bank account data has to be inserted.

In the above situation we require to write two triggers – Before insert and after insert.

You can define the triggers on tables ,views ,database and schema level.

What are different types events in trigger?

User can able to write a triggers in following events of databases :

1.Data Manipulation statements : You can write the triggers on data manipulation statements like update,insert and delete.

2.Data Definition statements : You can write the triggers on data definition statements like create, Alter and drop statements.

3.Database Operations : You can write the triggers on database operations like LOGON,LOGOFF,STARTUP,SHUTDOWN.

What are Types of Oracle Triggers with examples?

In this section we will see different types of triggers with small examples. There are following multiple types of triggers :

1. DML Triggers : The most commonly used triggers are DML triggers. You can write triggers on multiple events like insert,update and delete. There are three subtypes in it : BEFORE,AFTER and INSTEAD OF. Real Life Example : Before Insert,update,delete trigger : If you want to insert the record in Office timings only in departments table

create or replace trigger T_Check_Office_Time
before
insert or update or delete
on DEPARTMENTS
begin

if to_number (to_char(sysdate,’hh24′) ) not between 8 and 16 then
raise_application_error(-20010, ‘Insert,update,delete records in office timings 8 to 16’);
end if;

end;

You can test the trigger by inserting, updating and deleting the records in office time.

2.Non DML Triggers : The Non DML Triggers are categorized in to two types . DDL Event Triggers and database event triggers. I will explain details about it in next article with examples. The small example is if you want to audit some thing what is inserted and all then you can create the Audit table or view using trigger.

3. Subtypes of DML Triggers : Statement Level Triggers and Row Level Triggers. I will give detailed information in next sessions.

Business Usage of Triggers :

In this section I would like to give you the business use for the triggers and where exactly we require to use the triggers.

1.Security Purpose : Trigger is the best way to add more security on your PL/SQL code. We already took the example of the trigger used for security purpose where we are allowing the DML operations at Office hours only.

2.Auditing Purpose : In real life there are multiple operations which require to audit. To create audit tables on specific conditions we can use the triggers.

Example :

If you want to create the audit table for Accounts table and after creation of account we require to keep audit information then just write the audit trigger after insertion of record.

3.Data Integrity : We can add the integrity rules which are complex using triggers.

4.Table replication : Sometimes in real world scenarios we require to keep the backup of the table. So we need to use the trigger for table replication scenarios.

5.Event Logging : We need to keep track of the events for multiple applications. We can keep the track of it using event logging triggers.

These are few important usages of the triggers.

I hope this article finds you useful article. I will try to give you more detailed information about multiple types of triggers with examples in next article. If you like this article or if you have any issues or concerns with the same kindly comment in comments section.