Triggers in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Triggers in Oracle

In this article, I am going to discuss Triggers in Oracle. Trigger plays a very prominent role in the database. So, as a developer, you should and must know this concept.

What are Triggers in Oracle?

A Trigger is PL/SQL block that is stored in the database and fired(executed) in response to a specific event. The oracle database automatically executes a trigger when specified conditions occur. This is a schema object like procedures and functions. We can create a trigger and oracle executes the trigger based on the specific conditions.

Example to Understand the use of Triggers:

We have a user called hr in the database. We have a table called departments. The user hr tried to insert data into the table departments. Before inserting the data, we might have to do some prechecks. For example, the user should check that insert is only allowed on weekdays not allowed on holidays i.e. (Saturday & Sunday). In this case, we can create a trigger. Before is referred to as timing and insert is referred to as the trigger.

In some cases, we might do some actions once any statement is executed in the database. Once any data is inserted, we might need to log the table to the history audit.

Defining Triggers in Oracle:

Triggers can be defined on the table, view, schema, or database. We will be learning about triggers on tables and views.

Trigger Event Types:

We can create and execute triggers whenever one of the following operations occurs in the database.

1. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
2. A database definition (DDL) statement (CREATE, ALTER, DROP).
3. A database operation such as SERVER ERROR, LOG ON, LOG OFF, STARTUP, or SHUTDOWN.

Triggers can be distinguished into two types.

1. Database Triggers: The triggers will execute whenever a DML, DDL, or system event occurs on a schema or a database.
2. Application Triggers: The trigger will execute whenever an event occurs within a particular application.

Example to Understand Triggers:

Below is the login sample login page. Suppose, if a user enters the username and the password on the login page and clicks the login button. Whenever we click on the login button, the code inside the login button will execute as there is a trigger behind this login button. This is referred to as the application trigger.

So, we have differences between the application trigger and database triggers. We should understand the business situation of the client or user and make sure to understand when and why we are creating the triggers.

Uses of Security:

1. Security: We can create a trigger in such a way that no user cannot run an insert statement on holidays. This will help the management to increase security.
2. Auditing: We can create a trigger to log all the transactions that happened on a particular table. This is helpful for a DBA to track the transactions that occurred in the database.
3. Data Integrity: We can create a trigger to make sure to follow the standard rules.
4. Referential Integrity: we can make triggers to make sure the foreign key and primary key are in case the objects in the database do not have many relations. In some cases, we may find that there will be no relations on the tables in the database but there are relations at the application level. We can check the relations using triggers.
5. Table Replication: We can create a trigger in order to synchronize a table. We use triggers for computing derived data automatically and event logging.

Trigger Types in Oracle:

We have many triggers types

1. Simple DML Triggers: The DML triggers are commonly used triggers. Users can be created BEFORE, AFTER, INSTEAD OF. Before inserting, update a specific table. After updating a specific table. Instead of is used in views.
2. Compound Triggers: These triggers will be covered later.
3. Non- DML Triggers: The DDL event triggers occurs whenever the user creates a table or creates a view. Database event triggers occur whenever there is an event occurred in the database. Suppose, if a user loggedin to the database, then a trigger is created and executed to log all the information that the user executes in the database.

Trigger Event Types and Body:

A trigger event type determines which DML statement causes the trigger to execute. The possible events are:

1. Insert
2. Update(column)
3. Delete

DML statement event indicates whenever an insert statement is executed on a specific table or an update statement or delete statement is executed. The update is specified in the column as well as a single column is updated in a specific table. A trigger body determines what action needs to be performed and is a PL/SQL or a call to a procedure.

This is the basic information about the trigger and the operations that the trigger does. We will discuss triggers more in our upcoming articles.

In the next article, I am going to discuss DML Triggers in Oracle with examples. Here, in this article, I try to explain Triggers in Oracle with Examples. I hope you enjoy this Triggers in Oracle article.