Back to: Oracle Tutorials for Beginners and Professionals
Row Level Trigger Examples in Oracle
In this article, I am going to discuss Row Level Trigger Examples in Oracle. Please read our previous article where we discussed Statement Level Triggers Examples in Oracle.
Row Level Trigger Examples in Oracle
Now, we will learn about the row-level trigger example and see how the row-level trigger actually works. We will be creating a table and then we will be inserting the data into the table and then we will be creating the trigger and checking the functionality of trigger.
Step 1: Create a table
In order not to perform the insert operation again, we are creating a table from the copy of the old table present in the database. We will be creating the exact copy of the table employees.
Statement: Create table COPY_EMP as select * from employees;
So, we have created the table COPY_EMP. Let us check the data in the table. In general, if we need to make any changes or learn new topics it is better to create a copy of old tables in order not to make changes to the default tables.
Query: select * from COPY_EMP;
So, all the rows and columns that are present in the table employees are present in the table COPY_EMP.
Step 2: Create a Row Level Trigger.
We will be creating a row-level trigger. The login behind this trigger is to check all the employee’s salaries in the table COPY_EMP and if any salary is less than 500 then we should get an error saying that the minimum salary is 500.
SQL Script to Create Row Level Trigger in Oracle:
Create or replace trigger check_salary Before Insert or update of salary On COPY_EMP For each row Begin If :new.salary<500 then Raise_application_error(-20030,'minimum salary is 500'); End if; End;
From the above create statement we can understand that we have created a trigger with the name check_salary where the timing of the trigger is before and the event is insert or update of the column salary in the table COPY_EMP. We have mentioned “for each row” which indicates the row level trigger.
The login inside the trigger is if the salary is less than 500 then invoke the application error number 20030 where the error shows the minimum salary is 500. Let’s go ahead and create the trigger.
So, now the trigger is created.
Step 3: Test the Trigger
Let us try to check the trigger and see if the trigger is working or not. We will do an update to an employee and change the salary of the employee to 200.
Statement: Update COPY_EMP Set salary=200 Where employee_id=100;
So, we have executed the update statement and we got the exact output as mentioned in the trigger. The minimum salary is 500. We have tried this update for a specific employee. Let’s try to update the entire salary column. Let us first check how many rows the table COPY_EMP contains.
Query: select count(1) from copy_emp;
We have 107 rows. So, in this case, the trigger needs to fire 107 times. No, it does not run all 107 times because the compiler goes and tries to update the salary of emp_id 1 and we will get an error and the compiler stops there because we have mentioned “raise_application_error”. Let us try to check that.
Statement: Update COPY_EMP Set salary=200;
Step 4: Row Level Triggers using Old and New Qualifiers
So, let us try to work with old and new qualifiers and see how the database responds to these new qualifiers.
SQL Script to Create Trigger:
Create or replace trigger check_salary
Before
Insert or update of salary
On COPY_EMP
REFERENCING NEW AS NEW OLD AS OLD
For each row
Begin
If :new.salary<500 then
Dbms_output.put_line('TRIGGER EXECUTED SUCCESSFULLY');
End if;
End;
So, from the above statement we can understand that we have created a trigger with the name check_salary where the timing of the trigger is before and the event is insert or update of the column salary in the table COPY_EMP. We have mentioned “for each row” which indicates the row level trigger. We have mentioned “REFERENCING NEW AS NEW OLD AS OLD” which indicates the new value as new.salary and old value as old.salary. In the begin statement, we have removed the raise_applicaiton_error and if the salary is less than 500 then the output will be displayed as “TRIGGER”. Let’s go ahead and run the trigger.
So, the trigger is created. Let us try to check this method by updating the employee_id 100 salaries to 200.
Statement: Update COPY_EMP Set salary=200 Where employee_id=100;
We can see that 1 row is updated. So, the value of the salary is updated to 200 for the employee_id=100. In the if condition we have mentioned that if the salary is less than 500 then print “TRIGGER”. So, let’s check the DBMS output and see if the trigger is printed or not.
So, the value is updated and the trigger is running successfully. Let’s check the trigger for the entire salary column by updating the entire column.
Statement: Update copy_emp Set salary=200;
This will update the entire column. In the last case, this update statement failed because of raise_application_error. Now we do not have the raise_application_error clause. So, this will execute for each column.
We can see 107 rows are updated. Let us check the DBMS output and see the output. We should get the output as “trigger” printing 107 times.
We can see the trigger is printed many times.
Step 5: Change the qualifier names.
So, let us try to change the qualifier names and see how these qualifiers behave with the trigger.
SQL Statement to Create Trigger:
Create or replace trigger check_salary
Before
Insert or update of salary
On COPY_EMP
REFERENCING NEW AS N OLD AS O
For each row
Begin
If :n.salary<500 then
Dbms_output.put_line('TRIGGER EXECUTED SUCCESSFULLY');
End if;
End;
We have changed the qualifier name from new to n and old to o. We have changed the new.salary to n.salary. The remaining is the exact code. Let us try to compile this trigger.
We can see the popup that is showing. It is referred to as a bind variable. In this case, we have an option in SQL developer to ignore these bind variables and run the code.
We have to select the mentioned option and the code will run successfully. Now the trigger is created successfully. Let us test the trigger by updating the salary of employee_id 100.
Statement: Update COPY_EMP Set salary=200 Where employee_id=100;
We can see one row is updated. Let’s check the DBMS output and see if the trigger is executed or not.
The word trigger is executed only once. So, the trigger is executed and working successfully. This is how we play with the variables and learn about the row-level triggers.
In the next article, I am going to discuss How to Audit a Table using Triggers in Oracle with Examples. Here, in this article, I try to explain Row Level Trigger Examples in Oracle. I hope you enjoy the Row Level Trigger Examples article.