“G” Moment | Deep Dive into GBase 8s DML Trigger Mechanics and Practical Applications

Published on 2025-04-27

GBase 8s, an enterprise-grade transactional database product from General Data Technology Co., Ltd., offers robust DML trigger functionality that enables developers to automate tasks, audit data, enforce data integrity, and address multiple other application scenarios. This article provides a detailed overview of DML triggers in GBase 8s, covering syntax, features, usage examples, and best practices to help you understand and leverage this capability.

Introduction to DML Triggers

The DML trigger syntax in GBase 8s must be executed with SQLMODE set to ORACLE compatibility mode.

DML Trigger Definition

A DML trigger defines the actions the database should take automatically when certain data manipulation events occur. When a triggering event happens, the database implicitly invokes the defined set of statements. The triggering statement does not complete until all activated triggers have finished execution, and the failure of any trigger will cause the entire triggering statement to fail.

DML triggers are created on tables and are fired by the DML statements DELETE, INSERT, and UPDATE.

DML Trigger Syntax

  • create [or replace] trigger [databasename.]trigger_name — Trigger name

  • {before|after} — Trigger timing

  • { DELETE | INSERT | UPDATE [ OF column [, column ]... ] }

  • [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }... — Trigger event

  • on [databasename.]table_name — Trigger target

  • [REFERENCING { OLD [ AS ] old | NEW [ AS ] new}...][ FOR EACH ROW ] — Trigger level

  • [ ENABLE | DISABLE ] [ WHEN ( condition ) ] — Trigger mode and triggering condition

  • PLSQL block — Trigger body

As shown above, a DML trigger consists of trigger timing, trigger event, and other components. The relevant concepts are explained below:

Trigger timing: Specifies the execution order relative to the triggering event. BEFORE causes the trigger to fire before the triggering statement executes; AFTER causes it to fire after the triggering statement executes.

Trigger event: The DML statement event that fires the trigger, including INSERT, DELETE, and UPDATE.

Trigger target: The database object on which the trigger is defined. The current version supports only base tables.

Trigger action / Trigger body: The PL/SQL block executed when the trigger is fired.

Trigger level: Indicates statement-level or row-level triggers.

Statement-level trigger: Fires only once when the triggering event occurs.

Row-level trigger: Fires once for each row affected by the triggering event. Defined using FOR EACH ROW.

Trigger mode: Enables (ENABLE) or disables (DISABLE) the trigger.

Trigger condition: A logical expression specified by the WHEN clause. Must be used with a row-level trigger (FOR EACH ROW). The trigger only fires automatically if the expression evaluates to TRUE when the triggering event occurs.

Referencing Old and New Row Values in DML Triggers

Only in row-level triggers can you reference old and new row values to access the data of the row being processed.

The default reference names are OLD and NEW, which can be changed using the REFERENCING clause.

OLD represents the value before the row was processed; NEW represents the value after processing.

In the trigger body, reference a field of the old or new row using the following syntax:

reference_name.column_name

The meanings of OLD and NEW vary with the triggering event as follows:

Additional Notes on the Trigger Body in DML Triggers

The trigger body of a DML trigger is defined using PL/SQL block syntax. With SQLMODE set to ORACLE compatibility mode, the trigger body must support PL/SQL syntax definitions.

<trigger body> ::=[ DECLARE <declaration section>]BEGIN<executable section>END [trigger_name];

All PL/SQL syntax implemented in the current version is supported within the trigger body.

DML Trigger Usage Examples

Note: The database version used in the following examples is:GBase8sV8.8_TL_3.5.1_x86_64

Example 1: DML Trigger with Multiple Triggering Events

Example 2: Disabling/Enabling a Trigger

Example 3: Dropping a Trigger

Note: After dropping the target object of a trigger, all associated triggers are automatically dropped.

Example 4: Referencing New Values – INSERT (new value)

Example 5: Referencing Old Values – INSERT (old value)

Example 6: Referencing Old Values – DELETE (old value)

Example 7: Referencing New Values – DELETE (new value)

The above provides a brief introduction to using DML triggers. Using DML triggers appropriately can significantly improve database automation and data management efficiency. We hope this article helps you better understand and apply DML triggers. If you have any questions or need further assistance, feel free to ask in the community.