Databases can play an active role in validating and implementing business rules. Through triggers it is possible, in fact. to define rules to ensure data integrity and automation of critical operations. We will analyze their definition and the different types of triggers, focusing our attention on what is provided in Oracle. Through some practical examples, we will understand how to validate data and implement business rules without having to leave these aspects to applications.

Share

Reading time: 6 minutes

In the various articles related to SQL, we have explored all the potentialities of this language. Indeed, we have been able to create a database ( SQL: creating a database ), write data into it ( SQL: transactions and data manipulation ), and query it in as many different ways as possible to extract the information of interest. In all these scenarios the database behaved passively, that is, we instructed it what to do. In many contexts this approach is the standard. Queries and updates are explicitly requested by users through the application, which incorporates knowledge of the processes operating on the data. This, however, can bring problems. In fact, devolving data verification solely to applications can introduce inconsistencies due to software bugs or rules implemented differently in the various software that interface with the database. How can this be remedied? By making the database an active component in the overall architecture. This can be achieved by using triggers.

Active databases

As mentioned earlier, in order to make a database responsive, it is necessary to incorporate within it the logic that verifies consistency among the data. The responsiveness of the database is ensured by the automatic execution of active rules, called ECAs. The name comes from the components of the rules, namely:

  • Event: database modification operation
  • Condition: predicate on the state of the database if the condition is true, the action is executed
  • Action: sequence of SQL statements or application procedure

Triggers are the tool in the SQL language for implementing active rules. They consist of a set of PL/SQL statements that are triggered in response to specific events involving database tables or views. These events can include inserting, updating or deleting records, thus allowing custom business rules or logic to be implemented.

To make triggers work properly, all databases have a component called the Rule Engine. This is responsible for tracking all events that occur in the database and executing the rules that have been defined when appropriate. The execution of rules, i.e., triggers, is alternated with the execution of traditional transactions and depends on the execution strategy defined by the DBMS.

Unfortunately, the syntax and execution logic of triggers is not standard, although their usefulness is now recognized. For historical reasons, each database has implemented its own syntax for both defining the triggers themselves, the rules of activation, and the functions that can be used within them. This implies that, depending on the database you would like to use, you will have to study the official documentation to implement the triggers correctly. Also, if you change technology you will not be able to migrate the implemented triggers, but will have to rewrite them.

For simplicity of presentation and syntax, we will use Oracle syntax in this article. Compared to other databases, Oracle has a cleaner syntax in our opinion.

Using Triggers

Triggers are extremely versatile and can be used in a variety of scenarios, including:

  1. Data Validation: Triggers can be used to verify that entered data meet certain conditions or constraints, ensuring consistency of data in the database.
  2. Audit of Changes: Can be used to track changes made to tables, recording who performed the operation and when, providing an important tracking and security tool.
  3. Automatic Updating of Fields: Triggers can be used to automatically update other fields or tables in response to certain events, thus reducing the need for manual intervention.
  4. Implementation of Business Rules: Can be leveraged to apply customized business logic during insert, update or delete operations, ensuring that changes comply with company policies.
  5. Access Control: Triggers can be used to restrict or control access to certain data or operations, thus helping to ensure database security.

Structure of a Trigger in Oracle

An Oracle trigger consists of the following main elements:

  1. Trigger Name: Each trigger must have a unique name within the database schema in order to properly identify and manage it.
  2. Event that Activates Trigger: This can include INSERT, UPDATE or DELETE operations on a specific table or view. The event is what triggers the execution of the trigger.
  3. Modes of Execution: Triggers can be divided into two categories: BEFORE and AFTER. BEFORE triggers are executed before the action that triggered them, while AFTER triggers are executed after the action.
  4. Table mutant: The trigger is associated with a specific table or view and applies only to that.
  5. Granularity: you can specify whether the trigger will act on all rows of the mutant table or only on a specific row (using the `FOR EACH ROW` reference).
  6. Trigger Body: This is the area where you define the PL/SQL statements to be executed when the trigger is triggered. Complex logic or specific business rules can be implemented here.

The syntax is as follows:

				
					CREATE TRIGGER TriggerName
Mode Event {OR Event }
ON TargetTable
[[ REFERENCING ReferenceName]
FOR EACH ROW
[WHEN Predicate]]
PL/SQL Block

				
			

In addition to the main elements defined above, as can be seen there is also the WHEN predicate. This can only be inserted for single-record execution semantics (i.e., FOR EACH ROW) and optionally specifies a trigger condition. Within this predicate the old and new state variables can be accessed.

The state variables, available only when triggering with a tuple granularity, are two:

  • NEW: contains updated values from the record event. This variable is only available for updates and insertions.
  • OLD: contains the pre-event values of change. This variable is only available for updates and deletions.

Peculiarities of Oracle triggers

Before moving on to a couple of practical examples, we need to dwell on some special features of Oracle.

First, the execution algorithm by which Oracle intersperses the execution of triggers with the trigger instruction.

  1. Triggers of the before type at the instruction level are executed
  2. For each tuple in the target table (TargetTable) affected by the trigger instruction
    1. Triggers of type before at the tuple level are executed
    2. The activation instruction is executed simultaneously with the integrity constraint check defined at the tuple level
    3. Triggers of type after at the tuple level are executed
  3. Integrity constraints on tables are checked
  4. Triggers of type after at the instruction level are executed

Since the execution of one trigger can trigger other triggers in cascade, this can lead to non-termination of the trigger execution. For this reason, the maximum length for cascading trigger execution is 32 by default. It can be changed, but is strongly discouraged.

Finally, the mutant table (or Target table) is not accessible in row-level triggers, but only in triggers with statement-level granularity. In other databases, however, this is possible.

Practical Examples of Triggers

To better understand the use of triggers let us look at two very simple examples.

Example 1: Reordering products

Suppose we have the following tables representing an inventory and a list of orders to be sent.

INVENTORY (Part#, QtyOnHand, ThresholdQty, ReorderQty)
PENDING_ORDERS(Part#, OrderDate, OrderedQty)

We want to automatically manage the reorder of a product when its quantity in stock falls below a certain threshold. Therefore, the events involved in this operation will be both the update of the quantity available for a product and the insertion of a new product. As soon as the change occurs, for each row in the Inventory table, we will insert a new tuple in the PENDING_ORDERS table with the necessary information. The insertion will occur if and only if there is not already an order for that product.

All of this description is translated into SQL as shown below:

				
					CREATE TRIGGER Reorder
AFTER UPDATE OF QtyOnHand OR INSERT ON Inventory
FOR EACH ROW
WHEN (NEW.QtyOnHand < NEW. ThresholdQty)
DECLARE
 N number;
BEGIN
 select count(*) into N
 from PendingOrders
 where Part# = :NEW.Part#;
 If (N==0) then
    insert into PENDING_ORDERS(Part#,OrderedQty,OrderDate)
    values (:NEW.Part#, :NEW.ReorderQty, SYSDATE);
 end if;
END;

				
			

You may notice that to save a value we used the DECLARE clause to define a variable. Also, it is very common practice to check that there are not already records with certain characteristics (in our case orders of the same product) we use a count query.

Example 2: Minimum salary

Suppose now that we want to implement an audit of the minimum wage of a company’s employees. By company policy, each employee cannot receive a salary less than 1000 €. The trigger events will be inserting and updating the salary field on the employees table, while the event granularity will be tuple (FOR EACH ROW). Within the trigger body we will verify that the salary field meets the defined threshold and if not we will issue an error. To do this we will use Oracle’s RAISE_APPLICATION_ERROR function. The error will cause the trigger to abort and rollback all operations done by the trigger itself and the transaction that triggered it. The resulting SQL will look as follows:

				
					CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 1000 THEN
    RAISE_APPLICATION_ERROR(-20001, The salary should be at least 1000.');
  END IF;
END;

				
			

Example 3: Maximum number of events

Suppose now that we want to manage an organization’s calendar of events. For logistical and organizational reasons, no more than 10 events can be scheduled on any given date. The database consists of the following tables.

EVENT(CodE, Name, Category, Cost, Duration)
CALENDAR (CodE, Date, StartTime)

The trigger will need to operate when inserting a new record into the CALENDAR table or updating the event date. Since we will need to count the number of calendar events for each date, we will need to access the mutant table. The granularity of execution will therefore be at the instruction level. As in the previous example we will use the RAISE_APPLICATION_ERROR function to issue an error if there is at least one date on which there are more than 10 events. The resulting trigger will be as follows.

				
					CREATE TRIGGER check_n_events
AFTER INSERT OR UPDATE of Data ON CALENDARIO_EVENTI
DECLARE
	X Number;
BEGIN

SELECT COUNT(*) INTO X
FROM CALENDAR
WHERE Date IN 
		     (SELECT Date
		      FROM CALENDAR
		      GROUP BY Date
		      HAVING COUNT(*) > 10);
IF (X<>0) THEN
	raise_application_error (XXX, “Too much events”);
END IF;
END;

				
			

Conclusions

Triggers in Oracle are essential tools for automating operations and implementing custom logic within a database. However, it is critical to use them carefully and fully understand their impact on database performance and operations. With judicious design and implementation, triggers can greatly improve the efficiency and reliability of the data management system. Exploited appropriately, triggers can be a key element in the success of applications and systems that rely on them.

More To Explore

Python language

Pandas: data analysis with Python [part 1].

Data scientists continually need to read, manipulate, and analyze data. In many cases they use specific tools, but sometimes they need to develop their own code. To do this, the Pandas library comes to our aid. Let’s learn about its data structures, how we can read data from different sources and manipulate it for our purposes.

Artificial intelligence

Gradio: web applications in Python for AI [Part 3]

With Gradio, it is possible to create web applications for our machine learning and AI models in just a few lines of code. Through some examples, we will see the advanced features available, such as authentication, caching, and input file processing. We will also build a chatbot and an image classifier from pre-trained models. Finally we will discuss how to deploy our project in a few simple steps.

Leave a Reply

Your email address will not be published. Required fields are marked *

Progetta con MongoDB!!!

Acquista il nuovo libro che ti aiuterà a usare correttamente MongoDB per le tue applicazioni. Disponibile ora su Amazon!

Design with MongoDB

Design with MongoDB!!!

Buy the new book that will help you to use MongoDB correctly for your applications. Available now on Amazon!