Populating Default Value using Triggers in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

How to Populate Default Value using Triggers in Oracle

In this article, I am going to discuss How to Populate Default Values into the table using Triggers in Oracle with Examples. Please read our previous article where we discussed Trigger Options in Oracle with Examples.

Populating Default Value using Triggers in Oracle

Let us understand How to populate a Default Value into a table using Triggers with Examples. We will be creating a table called customers. So, let’s go ahead and create a table named customers with the columns as cust_id where the datatype is number, the name with datatype as varchar2(100), and the status with datatype as char(1) as follows.

Create table customers
( 
  cust_id number,
  Name varchar2(100),
  Status char(1)
);

So, the customers table is created. From the customers table, we can see a column called status. We are going to use that column in triggers and whenever a “BEFORE UPDATE” event occurs, then the trigger will populate a default value to the status column. For example, if a student scores above 35 then a trigger will populate the status column with value A in the table. So, let us go ahead and create the trigger by executing the following statement.

Create or replace trigger customer_default_status
Before insert
On customers
For each row
Begin
:new.status=’A’;
End;

So, the trigger is created and compiled. From the trigger create a statement, and you can see any value inserted to the table customers, the new value will be updated to the column status. Let us go ahead and test the trigger by inserting a record into the table customers table by executing the below INSERT SQL Statement.

Insert into customers (cust_id, name) values (1,’abc’);

So, the row is inserted into the customers table. Let us check the status column and see if the trigger is executed or not by executing the following SELECT Statement

select * from customers;

We can see the status is filled with the value ‘A’. So, the trigger is working fine. We may find this example simple but in real life, this type of trigger will be helpful.

In the next article, I am going to discuss Instead of Triggers in Oracle with Examples. Here, in this article, I try to explain Populating a Default Value using Triggers in Oracle with Examples. I hope you enjoy this Populating Default Value using Triggers in Oracle article.