1.3 - Trigger Applications

Triggers provide a robust mechanism for implementing data management capabilities directly within the database. They allow for the encapsulation of business rules and management logic, reducing the need for such logic to be spread across multiple applications. However, managing the interactions between triggers can be complex. Many DBMS utilize triggers for various internal services, including:

Data Replication
Integrity Constraint Management (when not supported by declarative SQL)
Materialized View Maintenance

Materialized Views

A view in a database is a virtual table based on a query defined in the database catalog. It can be used in queries as if it were a physical table.

Here’s a simple example of a view that calculates the total personnel cost for each department:

CREATE VIEW deptcost AS
SELECT d.DeptNum AS dept, COALESCE(SUM(e.salary), 0) AS totCost
FROM dept d LEFT JOIN emp e ON e.dept = d.DeptNum
GROUP BY d.DeptNum;

This view summarizes the total salary cost for each department:

Dept		emp			deptcost
deptNum	Name	RegNum	Salary	Dept	dept	totCost
1	DEIB	1	100	1	1	264
2	DICA	2	80	2	2	168
		3	74	1
		4	90	1
		5	88	2

When a view is used in a SELECT statement, the query processor rewrites the query based on the view definition, executing the underlying base table queries. If queries to a view are significantly more frequent than updates to the base tables, materializing the view can enhance performance. Materialized views store the results of the view definition in a physical table. Some DBMS support a CREATE MATERIALIZED VIEW command to automate this, but triggers can also implement materialization.

Not all modifications to the base table (e.g., EMP) affect the materialized view (deptcost). The impact of various operations on the view is as follows:

Updating an employee ID (Idemp): No effect on the view.
Inserting or deleting an employee or updating a salary: Affects only the relevant tuple in deptcost.
Updating the department of an employee: Affects two tuples in deptcost.
Inserting or deleting a department: Affects only one tuple in deptcost.

When the cost of maintaining the view incrementally is lower than recomputing it from scratch, incremental maintenance is preferable.

Here’s how triggers can manage updates to the materialized view:

Trigger 1: Insertion on EMP
- Event: After an employee is added.
- Action: Increase totCost for the corresponding department.

CREATE TRIGGER Incremental_InsEmp
AFTER INSERT ON EMP
FOR EACH ROW
UPDATE deptCost
SET totCost = totCost + NEW.salary
WHERE dept = NEW.dept;

Trigger 2: Deletion on EMP
- Event: After an employee is deleted.
- Action: Decrease totCost for the corresponding department.

CREATE TRIGGER Incremental_DelEmp
AFTER DELETE ON EMP
FOR EACH ROW
UPDATE deptCost
SET totCost = totCost - OLD.salary
WHERE dept = OLD.dept;

Row-level triggers will affect only one department in the materialized view if multiple employees are inserted or deleted in a single SQL command.

Trigger 3: Salary Update on EMP
- Event: After an employee’s salary is updated.
- Action: Update totCost for the relevant department if the department remains unchanged.

CREATE TRIGGER Incremental_SalaryUpdate
AFTER UPDATE OF salary ON EMP
FOR EACH ROW
WHEN OLD.dept = NEW.dept
UPDATE deptCost
SET totCost = totCost - OLD.salary + NEW.salary
WHERE dept = NEW.dept;

With this trigger, the salary variation is applied only to the department to which the updated employee is affiliated (using old.dept would be equally correct as is not changed)

Trigger 4: Update of dept on EMP
- Event: After an employee’s department is updated.
- Action: Adjust totCost for both the new and old departments.

CREATE TRIGGER Incremental_DeptUpdate
AFTER UPDATE OF dept ON EMP
FOR EACH ROW
BEGIN
   UPDATE deptCost
   SET totCost = totCost + NEW.salary
   WHERE dept = NEW.dept;
   
   UPDATE deptCost
   SET totCost = totCost - OLD.salary
   WHERE dept = OLD.dept;
END;

Trigger 5: Insertion on DEPT
- Event: After a department is added.
- Action: Insert a new tuple with totCost set to 0.

CREATE TRIGGER Incremental_InsDept
AFTER INSERT ON DEPT
FOR EACH ROW
BEGIN
   INSERT INTO deptCost (dept, totCost)
   VALUES (NEW.DeptNum, 0);
END;

Trigger 6: Deletion on DEPT
- Event: After a department is deleted.
- Action: Remove the corresponding tuple from deptCost.

CREATE TRIGGER Incremental_DelDept
AFTER DELETE ON DEPT
FOR EACH ROW
BEGIN
   DELETE FROM deptCost
   WHERE dept = OLD.DeptNum;
END;

No cascading: events on base tables trigger actions on the materialized view table.

Triggers can also play a role in maintaining data integrity. The previous incremental triggers assume referential integrity between dept and emp. For example, the emp table might have a foreign key constraint that ensures every employee is assigned to a valid department:

CREATE TABLE emp (
    idemp INT NOT NULL AUTO_INCREMENT,
    salary INT DEFAULT NULL,
    dept INT NOT NULL,
    PRIMARY KEY (idemp),
    CONSTRAINT empdept FOREIGN KEY (dept)
    REFERENCES dept (DeptNum) ON DELETE CASCADE
);

This ensures that if a department is deleted, all affiliated employees are also removed, and assigning an employee to a non-existing department will result in a constraint violation.

Trigger Design Principles

When designing database triggers, adherence to established best practices is crucial for optimizing performance, simplifying maintenance, and bolstering the overall reliability of the system. A primary principle in trigger design involves guaranteeing related actions, wherein triggers are employed to ensure that the execution of a specific operation automatically initiates all associated actions. This approach is fundamental for upholding data consistency and maintaining integrity throughout the database system. For instance, in an order management system, a trigger could automatically update inventory levels whenever a new order is inserted, thus ensuring that stock figures accurately reflect sales.

Furthermore, it is imperative to avoid duplicating functionalities inherently provided by the Database Management System (DBMS). Triggers should not be implemented to replicate features already available through declarative integrity constraints, such as NOT NULL, UNIQUE, or FOREIGN KEY constraints. These built-in DBMS functionalities are optimized for performance and reliability, rendering trigger-based alternatives redundant and potentially less efficient. The design philosophy here emphasizes leveraging the native capabilities of the DBMS before resorting to custom trigger logic.

Another critical consideration is to limit the size of triggers. Triggers should be kept concise, ideally under 60 lines of code. If the required logic exceeds this guideline, it is advisable to encapsulate the more complex operations within a stored procedure and then invoke that procedure from the trigger. This modular approach enhances code readability, simplifies debugging, and improves the overall maintainability of the database schema by separating complex business logic from the trigger’s immediate responsibilities.

Triggers are particularly well-suited for centralized operations that demand uniform execution irrespective of the user or application initiating the triggering statement. This characteristic makes them ideal for enforcing global business rules consistently across all connected applications. For example, a trigger could automatically log all data modifications to an audit table, ensuring a comprehensive record of changes regardless of the client application used.

However, designers must exercise caution to avoid recursive triggers, as these can lead to uncontrolled infinite loops, ultimately depleting system resources and potentially causing database outages. If a recursive pattern is absolutely necessary, stringent controls and termination conditions must be meticulously implemented to prevent runaway executions and safeguard system stability.

Finally, the judicious use of triggers is paramount. Given that triggers execute for every user and every occurrence of the defined event, their indiscriminate application can introduce significant performance overhead. Therefore, a thorough cost-benefit analysis should always precede trigger implementation, ensuring that the performance gains and data integrity benefits derived from using triggers demonstrably outweigh any potential performance degradation.

Evolution of Active Databases

The evolution of active databases has introduced several advanced features and functionalities that enhance the capabilities of triggers and event handling:

Execution Modes: Different execution modes, such as immediate, deferred, and detached, allow for greater flexibility in how and when triggers execute. Immediate triggers act right away, while deferred triggers can wait until the end of a transaction.
New Event Types: The introduction of system-defined, temporal, and user-defined events broadens the scope of what can trigger actions within the database, allowing for more complex and responsive applications.
Complex Events and Event Calculus: Active databases support complex event processing, where multiple events can be combined or sequenced to trigger actions. Event calculus provides a framework for reasoning about events in a more expressive manner.
Instead-Of Clause: This allows triggers to replace standard actions. For instance, instead of deleting a row, a trigger can be defined to perform an alternative action when a delete is attempted, providing greater control over data manipulation.
Rule Administration: Features such as priorities, grouping, and dynamic activation/deactivation of rules enable fine-tuning of how triggers and actions respond to events, improving the efficiency of event handling.
Vendor-Specific Variations: Different database vendors, such as Oracle, introduce unique features and variations to triggers and event handling. Understanding these differences is crucial for effective database design and management.

Proprietary Limitations and Extensions

Most major DBMS vendors provide support for triggers, although the extent of this support can vary significantly. While a majority of these products implement a subset of the SQL:1999 trigger standard, it is notable that many do not strictly adhere to some of the more nuanced aspects of its execution model. This divergence often leads to inconsistencies in how triggers behave across different platforms. Furthermore, the reliance on proprietary programming languages for trigger implementation by certain DBMS vendors poses considerable challenges to database portability. Consequently, migrating trigger logic from one DBMS to another can be a complex and resource-intensive endeavor, often requiring substantial re-engineering.

Despite these challenges, triggers offer distinct advantages, particularly in facilitating the central management of semantic rules directly within the database, under the direct control of the DBMS. This approach prevents the replication of business logic across multiple applications, thereby ensuring uniformity and reducing the potential for inconsistencies. Moreover, triggers are invaluable for guaranteeing data properties and enforcing complex business rules that cannot be adequately specified through standard declarative integrity constraints alone. For instance, a trigger might be used to enforce inter-table dependencies or validate data based on complex algorithmic logic that goes beyond simple column-level constraints.

Oracle

Oracle’s trigger syntax allows for multiple events and lacks support for table variables. Additionally, the WHEN clause can only be used with row-level triggers. The syntax for creating a trigger in Oracle is as follows:

CREATE TRIGGER TriggerName
{ BEFORE | AFTER } <event> [, <event> [, <event> ]]
[ [ referencing [old [row] [as] OldTupleName ]
                [ new [row] [as] NewTupleName ]]
FOR EACH ROW
[WHEN SQLPredicate]]
    PL/SQLStatements
 
<event> ::= { insert | delete | update [of Column] } on Table

Multiple Events: Triggers can be defined for multiple events in a single statement.
Row-Level Context: referencing can give specific names to old and new tuples, providing clarity in the trigger’s logic.
Conflict Semantics: Oracle has its own conflict resolution rules, especially concerning concurrent modifications.
Action Power: Before triggers in Oracle can perform a wide range of actions, as there are no limitations on the expressive power of the action.

PostgreSQL

PostgreSQL has a more versatile trigger syntax, allowing for a wide range of events and conditions. The syntax for creating a trigger is as follows:

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name
    { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ]
    [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ]
                    transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

Event Flexibility: Supports INSERT, UPDATE, DELETE, and TRUNCATE as events that can trigger actions.
Deferrable Constraints: Offers options for deferring trigger execution until the end of a transaction.
Referencing: The REFERENCING clause allows naming for transition relations (old and new), providing additional flexibility.
Execution Functions: Triggers can invoke functions or procedures, allowing complex logic to be encapsulated separately.

MySQL

MySQL has a simpler syntax for defining triggers but includes options for ordering multiple triggers on the same event. The syntax for creating a trigger is as follows:

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body
 
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Trigger Timing: Supports BEFORE and AFTER for when the trigger should be executed relative to the triggering event.
Event Types: Allows INSERT, UPDATE, and DELETE as triggering events.
Order Control: MySQL allows the specification of trigger order (using FOLLOWS or PRECEDES), enabling developers to control which trigger executes first when multiple triggers respond to the same event.

Summary of Differences

Feature Oracle PostgreSQL MySQL
Event Support Supports multiple events in a single trigger. Offers the widest range of events, including TRUNCATE. More straightforward with only INSERT, UPDATE, and DELETE.
Referencing and Naming Allows for named tuples for clarity. Provides transition relation naming and additional flexibility. Does not offer naming options for old/new tuples.
Complex Actions Allows extensive action logic without expressive power limitations. Executes functions or procedures, enabling separation of logic. Simpler trigger bodies without the ability to invoke external functions directly.

Polimi CS - Notes

Explorer

1.3 - Trigger Applications

Table of Contents

Materialized Views

Trigger Design Principles

Evolution of Active Databases

Proprietary Limitations and Extensions

Oracle

PostgreSQL

MySQL

Backlinks

Graph View

Table of Contents

Feature	Oracle	PostgreSQL	MySQL
Event Support	Supports multiple events in a single trigger.	Offers the widest range of events, including `TRUNCATE`.	More straightforward with only `INSERT`, `UPDATE`, and `DELETE`.
Referencing and Naming	Allows for named tuples for clarity.	Provides transition relation naming and additional flexibility.	Does not offer naming options for old/new tuples.
Complex Actions	Allows extensive action logic without expressive power limitations.	Executes functions or procedures, enabling separation of logic.	Simpler trigger bodies without the ability to invoke external functions directly.