Back to: Oracle Tutorials for Beginners and Professionals
DML Triggers in Oracle with Examples
In this article, I am going to discuss DML Triggers in Oracle with Examples. Please read our previous article where we discussed Triggers in Oracle with Examples.
DML Triggers in Oracle
The DML Triggers are the Data Manipulation triggers that make changes to the table inside the database. There are two types of DML triggers.
- Statement Level DML Triggers
- Row Level DML Triggers
Statement Level Triggers
- This is the default trigger while creating a trigger.
- Executes only once for the triggering event.
- Executes only once even if no rows are affected.
- Example: Security check on the user, time, etc.
Row Level Triggers
- Use the FOR EACH ROW clause when creating a trigger.
- Fires once for each row affected by the triggering event.
- Does not fire if the triggering event does not affect any rows.
- Example: Log the transactions.
Once we understood statement-level triggers and row-level triggers we need to identify when we need to use statement-level triggers and when we have to use the row-level triggers.
Example to Understand Statement Level DML Triggers in Oracle:
This is the default trigger while creating a trigger that executes only once in the entire statement, even if a statement contains an update of 100 rows, then also the statement-level trigger executes only once. Let us go ahead and learn an example about the statement level triggers.
In the statement level triggers, we will be creating a table and adding a trigger to that table, and testing the trigger. We will be performing these operations step-wise to verify each and every step.
Step 1: Create a table
Let us go ahead and create a table test. We are creating the table as test and there will be two columns with emp_id where the data type is number and ename where the data type is varchar.
SQL Script to Create the test table:
Create table test (emp_id number, ename varchar2(100) );
So, the table is created. Let us go ahead and insert two records in the table. We will be inserting two rows with emp_id as 1 with ename as abc and emp_id as 2 with ename as def.
SQL Script for INSERT records into test table:
Insert into test values (1,'abc'); Insert into test values (2,'def');
So, the table consists of two columns emp_id and ename with two rows.
Step 2: Creating DML Trigger in Oracle
So, here we are going to create a DML trigger where this trigger will execute before any update statement performs on the table test. Let us go ahead and check the statement.
SQL Script to Create a Trigger:
Create or replace trigger test_before_update Before update On test Begin DBMS_OUTPUT.PUT_LINE ('TRIGGER EXECUTED SUCCESSFULLY'); End;
So, from this query, we will be creating a trigger with the name test_before_update on the table test. So, if any update statement runs on the table test, then the output will be “TRIGGER EXECUTED SUCCESSFULLY”. We have mentioned this in the DBMS_OUTPUT.PUT_LINE. Let us go ahead and run this statement and check if the trigger is created or not.
So, the trigger is created. If we see the create trigger statement, we can see a clause mentioned before the update this is referred to as an event. Before is referred to as timing and update is referred to as an event. Any code that is present between begin and end, that code is executed if the trigger is fired. Let us check the table details and check if the trigger is linked with the table test. Click on tables and select the table test.
So, the table is present. We have a few options on the top where we can see triggers. Let’s click on triggers and see if the trigger is linked or not.
We have trigger_name as BEFORE_TEST_UPDATE and the trigger_type as BEFORE STATEMENT and the triggering_event is UPDATE. The status of the trigger is enabled. We can see the table name mentioned above is TEST. So, this is how we can view the trigger information in SQL Developer. The trigger is created. We have to test the trigger and see how it works.
Step 3: Testing Trigger
Let us go ahead and test the trigger. As we have mentioned the event as before update in the trigger, let us try to update the table TEST and see if the trigger is active. We are going to update the ename column with concatenating s with ename.
SQL Script to fire the Trigger: Update test set ename=ename||’s’;
We can see the output as two rows updated. Let us check the DBMS output and see if the trigger is executed or not. To check the DBMS output click on view and we can see the DBMS output option.
We can see the trigger executed successfully printed on once. So, this trigger is executed only once. Let us try to check with another method. We are going to update ename for a specific emp_id and see if the trigger works or not.
SQL Script to Test the Trigger: Update test set ename=ename||’s’ Where emp_id=20;
We can see the output is mentioned as 0 rows updated. As we have discussed that statement-level triggers execute only once. Let us try to check the DBMS output and see if the trigger is executed or not.
We can see the DBMS output executed only once. So, the trigger is fired only once. So, this indicates that even if no rows are updated the trigger will be executed in statement-level triggers. Let us try to learn about Row-level triggers.
Example to Understand Row Level DML Triggers in Oracle:
The trigger executes for each row. There is a clause introduced for this trigger as “for each row”. If there is no change in any rows the trigger does not execute. Let us try to use the same trigger and make some changes to the trigger and work on each row. We will be using the “for each row” clause to differentiate the row-level triggers.
SQL Script to Create Row Level Trigger:
Create or replace trigger test_before_update Before update On test For each row Begin DBMS_OUTPUT.PUT_LINE ('TRIGGER EXECUTED SUCCESSFULLY'); End;
So, this statement describes creating a trigger with the name test_before_update where the event is update and the timing is before. There is a clause mentioned “which each row” which describes that the trigger will be executed for each row. Let us run this statement and check whether the trigger is compiled successfully or not.
So, the trigger is compiled successfully. Let us go ahead and test the trigger by running the two update statements that we have used for the statement-level triggers. Let us go ahead and test the trigger. As we have mentioned the event as before update for each in the trigger, let us try to update each row in the table TEST. We are going to update the ename column with concatenating s with ename.
SQL Script to test Row Level Trigger: Update test set ename=ename||’s’;
We can see the output are 2 rows updated. Let us try to check the DBMS output and see how many times the trigger is executed.
We can see the trigger is executed successfully as there are only two rows in the table. Let us try to check the row level trigger for a specific row.
SQL Script to Test Trigger: Update test set ename=ename||’s’ Where emp_id=20;
We can see 0 rows updated. As there is no emp_id as 20. There will be no triggers executed. Let’s try to check the DBMS output as well.
From the DBMS output, we can see that the trigger is not executed and has not printed any output. So, this describes that the trigger is executed once for each row affected by the triggering event. The trigger does not fire if the triggering event does not affect any rows. There are a lot of advantages to row-level triggers. We will come to know about those advantages in our upcoming articles.
Trigger Common Cases
So, until now we understood about triggers. We have seen the types of triggers as statement-level triggers and row-level triggers. We have understood the meaning of the statement and row-level triggers and got an example of those triggers. Now, we will learn what are the common scenarios for the statement-level triggers and the row-level triggers.
Statement Level Trigger Common Cases:
The first case is in the form of security. When we want to check the security before running any DML statement. When we want to check the date and time before running any DML statement. For example, inserting or updating a specific table on Saturday and Sunday is not allowed. This rule can be created by using the statement level trigger.
If we want to check the user profile or customized table containing a profile. So, if we need to check something before running any statement. So, the trigger will be executed only once. Even if the table contains thousands of rows or only two rows the trigger will be executed only once.
Example to Understand Statement Level Trigger:
Update emp Set sal=sal+10 where emp_id=1; Update emp Set sal=sal+10;
From the first update statement, we have only one row to update. The trigger will execute only once. From the second update statement, we have all the rows but the trigger will be executed only once.
Row Level Trigger Common Cases:
When we require old and new values for the DML operations we use the row-level triggers. Let’s try to understand the meaning of old values and new values.
Example to Understand Row Level Trigger Common Cases:
Update emp Set sal=sal+10;
In this case, each row will be incremented by 10.
So, the employee ‘ABC’ old salary is 5000 and the new salary is 5010. We use the row-level triggers for updating each and every row. This is mainly used for auditing purposes.
In the next article, I am going to discuss Statement Level Trigger Examples in Oracle. Here, in this article, I try to explain DML Triggers in Oracle with Examples. I hope you enjoy this DML Triggers in Oracle with Examples article.