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:
- Data Validation: Triggers can be used to verify that entered data meet certain conditions or constraints, ensuring consistency of data in the database.
- 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.
- 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.
- 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.
- 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:
- Trigger Name: Each trigger must have a unique name within the database schema in order to properly identify and manage it.
- 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.
- 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.
- Table mutant: The trigger is associated with a specific table or view and applies only to that.
- 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).
- 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.
- Triggers of the before type at the instruction level are executed
- For each tuple in the target table (TargetTable) affected by the trigger instruction
- Triggers of type before at the tuple level are executed
- The activation instruction is executed simultaneously with the integrity constraint check defined at the tuple level
- Triggers of type after at the tuple level are executed
- Integrity constraints on tables are checked
- 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.
