"G" Moment: A Guide to Using the REFERENCING Clause in GBase 8s Triggers

In database development, triggers are commonly used tools that automatically respond to data operations on a table (such as INSERT, UPDATE, or DELETE) and execute predefined logic. The GBase 8s database supports using the REFERENCING NEW AS NEW OLD AS OLD clause when creating triggers, providing developers with a more flexible way to reference new and old data within triggers. This article provides a detailed explanation of this syntax and demonstrates its application in database development through practical examples.

Introduction to Triggers

Before diving into the REFERENCING clause, let’s first understand the basic concepts of triggers. In the GBase 8s database, a trigger is a database object associated with a table. When a specific operation (such as INSERT, UPDATE, DELETE) is performed on the table, the trigger automatically executes predefined SQL statements. Triggers are commonly used to implement business rules, validate data integrity, record operation logs, and perform other functions.

The Role of the REFERENCING Clause

When creating a trigger, the REFERENCING clause allows us to reference the modified data within the trigger using the NEW and OLD keywords. Specifically:

NEW: used to reference new data affected by INSERT and UPDATE operations in the trigger.

OLD: used to reference old data affected by UPDATE and DELETE operations in the trigger.

By using REFERENCING NEW AS NEW and REFERENCING OLD AS OLD, we can assign aliases to the new and old data, allowing for clearer referencing within the trigger logic.

Usage Examples

1. Referencing New Data (REFERENCING NEW AS NEW)

Suppose we have a table named t_sale with columns f_saleid, f_productname, and f_qty. We want to automatically log relevant information into another log table t_log whenever a new record is inserted. Below are the steps to implement this functionality:

1. Data Preparation

2. Create Trigger

3. Insert Data and View Results

Results:

t_sale table:

t_log table:

2. Referencing Old Data (REFERENCING OLD AS OLD)

Next, using the same table structure, we create a trigger that automatically logs information to the t_log table whenever the f_qty column in the t_sale table is updated.

1. Create Trigger

2. Update Data and View Results

Results:

t_log table:

From GBASE

In the examples above, we used the REFERENCING OLD AS OLD syntax, and old_item.f_qty represents the corresponding field of the old data affected by the update operation.

By using the REFERENCING NEW AS NEW OLD AS OLD clause of the GBase 8s database, we can more flexibly reference new and old data in triggers to implement complex business logic. Whether recording operation logs, validating data integrity, or implementing other business rules, this syntax provides solid support for developers. We hope the examples in this article help you better understand and apply this feature.