Statement-Level INSTEAD OF Triggers to Cut Per-Row Overhead
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies due to the high processor time and resource consumption associated with per-row firing of INSTEAD OF triggers, particularly when operations affect large numbers of rows, impacting speed and system resources.
Innovation Solution
Implementation of a statement-level INSTEAD OF trigger that generates reference tables without altering the stored database version, allowing execution per-statement instead of per-row, thereby reducing processor time and resource usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If per-row INSTEAD OF triggers are implemented, then database operations on views can be executed, but processor time and resource consumption increase significantly
Solution Approach 1:
The patent segments the trigger execution from row-level processing to statement-level processing. Instead of executing the trigger for each individual row modified by a DML statement, the trigger executes once at the statement level, receiving consolidated information about all affected rows through reference tables (OLD_TABLE, NEW_TABLE, DELTA_TABLE). This segmentation dramatically reduces processor time and resource consumption while maintaining the ability to execute operations on database views.
2Manufacturing precision
If per-row INSTEAD OF triggers are used, then detailed row-level control is achieved, but system performance deteriorates
Solution Approach 1:
The patent introduces reference tables (OLD_TABLE, NEW_TABLE, DELTA_TABLE) as intermediaries between the DML statement and the INSTEAD OF trigger. These tables consolidate information about all affected rows, allowing the trigger to access detailed row-level data without executing once per row. The intermediary mechanism preserves row-level control precision while improving system performance by reducing trigger execution overhead.
3Reliability
If traditional INSTEAD OF triggers execute per-row, then comprehensive data validation is possible, but execution time increases
Solution Approach 1:
The patent merges multiple row-level trigger executions into a single statement-level execution. By consolidating the validation logic to execute once at the statement level with access to all affected rows through reference tables, the system maintains comprehensive data validation while significantly reducing execution time. The merging eliminates redundant validation operations that would occur if the trigger executed separately for each row.
Data Source
AI summary
Techniques are disclosed relating to implementing a statement-level INSTEAD OF trigger. In one embodiment a computer system stores trigger information associated with a statement-level database trigger executable to initiate execution of at least one trigger instruction for a database instead of performing a particular database operation, on a database view, specified by a database operation statement. The computer system receives a first database operation statement specifying performance of the particular database operation on the database view and identifies a set of target rows, within the database view, targeted by the first database operation statement. In addition, the computer system generates a reference table associated with the database view, where the reference table includes rows corresponding to the target rows. The computer system executes the statement-level database trigger instead of executing the database operation statement, where executing the statement-level database trigger includes accessing the reference table.