Statement Level Trigger Examples in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Statement Level Trigger Examples in Oracle

In this article, I am going to discuss Statement Level Trigger Examples in Oracle. Please read our previous article where we discussed DML Triggers in Oracle with Examples. This is the default trigger while creating a trigger.

Statement Level Trigger Examples in Oracle

As we have a table named Departments in the hr schema. The Departments table is a master table. Departments table is a default table that is included while creating a database in oracle. If we try to delete any row in the table or try to delete the table Departments it does not work. We will be getting an error as below.

Statement: delete from departments;

We are getting the error integrity constraint violated. So, we will try to create a trigger linked to the table Departments. So, the trigger will be if we run any update or insert or delete statement on the table departments between 7 am to 3 pm then the output should be displayed as “DML Operations not allowed now”.

SQL Script to Create Statement Level Trigger in Oracle:

Create or replace trigger dept_check_time
Before
Insert or update or delete
On departments
Begin
 If to_number(to_char(sysdate,'hh224')) not between 7 and 15 then
 Raise_application_error(-0010,'DML Operations not allowed now');
 End if;
End;

So, we are creating a trigger name dept_check_time with the timing before and event as insert or update or delete on the table departments. The logic if any DML operations are performed between 3 pm to 7 am then we will be getting an error “DML Operations not allowed now”. So, let’s try to create this trigger. In the if statement we are using to_number and to_char to convert the time to 24-hour format. The raise_application_error will invoke the error number 20010 and the output will be displayed as “DML Operations not allowed now”. So, this indicates that DML operations on the table Departments are allowed between 8 am and 5 pm.

The trigger is now created and compiled successfully. Let us test the trigger now. Currently while performing this task the time is 6 pm. So, let us try to delete the table departments and see if it works or not.

Statement: Delete from departments;

We can see we are getting the same error number and the output as “DML Operations are not allowed now”. So, the trigger is working. The first priority of the trigger is a table. If the table contains any constraints, then the trigger will first check the table and then go to the constraints.

Let us try to test the trigger with another fake example. Let us assume that there is a department with department_id as -3. Even though this department_id is not present. We will try to execute a delete statement with that id and see if the trigger works or not.

Statement: Delete from departments where department_id = -3;

We are getting the same error as we have mentioned in the trigger. So, the statement level trigger is working fine. We can check the trigger details with the data dictionary as user_objects. We can use this data dictionary user_objects and create a query to check the details of the trigger.

Query: select * from user_objects where object_name=’DEPT_CHECK_TIME’;

We can see the object name is DEPT_CHECK_TIME and the object_id is 73307 and the object_type is mentioned as a trigger. The time when the trigger was created and the last DDL operation that had run on the trigger. The status of the trigger is valid. These trigger details will be saved as object details in the data dictionary user_objects. We can even check the trigger details with the trigger data dictionary as well. The data dictionary is user_triggers. This displays the information about the triggers.

Query: select * from user_triggers where trigger_name=’DEPT_CHECK_TIME’;

We can see the information about the trigger. Trigger name DEPT_CHECK_TIME and the created time and last DDL time of the trigger. The status is valid as the trigger is running.

Until now we have created the trigger in a hard-coded way. we have created the trigger name as dept_check_time with the timing before and event as insert or update or delete on the table departments. The logic that was mentioned was if any DML operations that are performed between 3 pm to 7 am then we will be getting an error “DML Operations not allowed now”. In the if statement we are using to_number and to_char to convert the time to 24-hour format. The raise_application_error invoked the error number 20010 and the output is displayed as “DML Operations not allowed now”. So, even updating or inserting, or deleting we are getting the same output.

We will be modifying the trigger in such a way that the if the DML statement has an insert statement then a different output, if the update statement then different output, and the delete statement then different output. Let us try to change the trigger creation statement as required.

SQL Statement to Create Trigger:

Create or replace trigger dept_check_time
Before
Insert or update or delete
On departments
Begin
 If to_number(to_char(sysdate,'hh224')) not between 7 and 15 then
 If inserting then
 Raise_application_error(-20010,'Insert Operations not allowed now');
 elseif deleting then
 Raise_application_error(-20010,'Delete Operations not allowed now');
 elseif updating then
 Raise_application_error(-20010,'Update Operations not allowed now');
 End if;
End if
End;

So, we have written as if inserting then the error will be displayed as “Insert Operations are not allowed now” and if the deleting operation occurs then the error will be displayed as “Delete Operations are not allowed now” and if the updating operation occurs then the error will be displayed as “Update operations are not allowed now”. Here we are using more specific error messages. We call this Conditional predicates. So, we can have more benefits from these predicates. Let us go ahead and compile this trigger statement and see if it works or not.

We can see the trigger is created. Let us try to check this error by deleting the table.

Statement: Delete from departments;

We can see the delete operations are not allowed now. Let us try to run the update statement and see if we are getting the update error or not.

So, we are getting the update operations are now allowed now. This is how we utilize the statement-level triggers to define the operations based on the actions of the developer.

Old and New Qualifiers in Oracle

We have learned about statement-level triggers. Now, we will be learning about row-level triggers. In case we need new and old qualifiers, we will be using the row-level triggers. We will be learning a lot about row-level triggers. If we have an insert or update or delete statements. Let us try to check with the insert statement.

Case 1:

Statement: Insert into dept(deptno, dname) values(1,’IT’);

So, the deptno will be 1 and the dname will be ‘IT’. We don’t have any old values because the values are new values as we are inserting them into the table dept. So, the deptno value will be 1 and dname is IT. So, the qualifier :new.deptno=1 and :new.dname=’IT’.

So, these are the new values that we have inserted into the table dept. These are referred to as new qualifiers.

Case 2:

Let us check and try to learn about old qualifiers with the update statement.

Statement: Update dept Set dname=’IT Dept’ Where deptno=1;

Here the dname has a new value “IT Dept”. The value is now referred as new qualifiers(:new.dname) and the value “IT” is referred as old qualifiers(:old.dname)

Case 3:

In the delete case, here all the columns are old values. There is no concept of new qualifiers or old qualifiers, as anyway, we will be deleting those columns. This is the information that we need to know before getting deep into the row-level triggers. We can refer to the new qualifiers with :new and old qualifiers with :old.

In the next article, I am going to discuss Row Level Trigger Examples in Oracle. Here, in this article, I try to explain Statement Level Trigger Examples in Oracle. I hope you enjoy this Statement Level Trigger Examples in Oracle article.