DEV Community: Michael

Triggered Stored Procedures in GBase 8s: Adding Complex Logic to Triggers

Michael — Mon, 30 Mar 2026 15:49:40 +0000

In GBase 8s, triggers alone do not support features like RAISE EXCEPTION or conditional logic — those belong to stored procedures. But when you need such logic inside a trigger, you can use a triggered stored procedure. This article walks through an INSERT trigger example that validates data and throws an exception if needed.

Create Sample Tables We need two tables: tab1 (the target for inserts) and tab2 (to store existing IDs).

CREATE TABLE tab1 (
    id INTEGER,
    name VARCHAR(40),
    PRIMARY KEY (id)
);

CREATE TABLE tab2 (
    id INTEGER,
    name VARCHAR(40),
    uptime DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND
);

Create the Stored Procedure The stored procedure checks whether the new ID already exists in tab2. If it does, it raises an exception.

CREATE PROCEDURE proc_tri_insert_tab1()
    REFERENCING NEW AS new FOR tab1;
    DEFINE pid INT;
    SELECT COUNT(id) INTO pid FROM tab2 WHERE id = new.id;
    IF pid = 1 THEN
        RAISE EXCEPTION -746, 0, 'ID exists!';
    END IF;
END PROCEDURE;

Create the Trigger Now attach the stored procedure to the INSERT event on tab1.

CREATE TRIGGER tri_insert_tab1 INSERT ON tab1
    FOR EACH ROW (
        EXECUTE PROCEDURE proc_tri_insert_tab1() WITH TRIGGER REFERENCES
    );

Test It Out
Insert a row into tab1 with an ID that already exists in tab2. The trigger fires, calls the stored procedure, and the exception is raised:
INSERT INTO tab1 VALUES (2, 'testssdadsfas');
Output:
746: ID exists!
Summary
With triggered stored procedures, the gbase database extends the power of triggers — you can now include complex checks, conditionals, and even custom exceptions. This approach is invaluable for enforcing strict business rules at the data‑entry level.

Have you used triggered stored procedures in your projects? Share your experience in the comments! 👇

Implementing split_part Function in GBase 8s: Two Practical Approaches

Michael — Mon, 30 Mar 2026 15:41:50 +0000

GBase 8s does not provide a built‑in split_part function like PostgreSQL. However, using the existing SUBSTRING_INDEX function or manual character parsing, we can easily achieve the same functionality. This article introduces two implementation methods with practical examples.

What Does split_part Do? The split_part function splits a string by a delimiter and returns the n‑th field.

Example:
split_part('aa,bb,cc,dd', ',', 3) → 'cc'

Method 1: Using SUBSTRING_INDEX GBase 8s provides SUBSTRING_INDEX, which extracts a substring up to a specified number of occurrences. This makes the implementation very straightforward.

DROP FUNCTION IF EXISTS split_part2;

CREATE FUNCTION split_part2(
    str_in lvarchar(2048),
    separator_in CHAR(1),
    field_in INT
) RETURNING VARCHAR(255);

    DEFINE str_len INT;
    DEFINE pos_curr INT;
    DEFINE count_field INT;
    DEFINE pos_char CHAR(1);

    IF field_in <= 0 THEN RETURN NULL; END IF;

    LET count_field = 1;
    LET str_len = LENGTH(str_in);

    -- Count delimiters to know total fields
    FOR pos_curr = 1 TO str_len
        LET pos_char = SUBSTR(str_in, pos_curr, 1);
        IF pos_char = separator_in THEN
            LET count_field = count_field + 1;
        END IF;
    END FOR;

    -- If requested field exceeds actual count, return NULL
    IF field_in > count_field THEN
        RETURN NULL;
    ELSE
        -- Take the first `field_in` segments, then the last one
        RETURN SUBSTRING_INDEX(
            SUBSTRING_INDEX(str_in, separator_in, field_in),
            separator_in,
            -1
        );
    END IF;

END FUNCTION;

Pros: Simple logic, uses built‑in functions, easy to read.
Cons: Requires one pass to count delimiters (negligible for most strings).

Method 2: Pure Character Parsing This version does not rely on SUBSTRING_INDEX — it manually scans the string to locate the desired field.

DROP FUNCTION IF EXISTS split_part;

CREATE FUNCTION split_part(
    str_in lvarchar(2048),
    separator_in CHAR(1),
    field_in INT
) RETURNING VARCHAR(255);

    DEFINE res VARCHAR(255);
    DEFINE str_len INT;
    DEFINE pos_curr INT;
    DEFINE substr_start INT;
    DEFINE substr_length INT;
    DEFINE pos_char CHAR(1);

    IF field_in <= 0 THEN RETURN ''; END IF;

    LET res = '';
    LET substr_start = 0;
    LET substr_length = 0;
    LET str_len = LENGTH(str_in);

    FOR pos_curr = 1 TO str_len
        LET pos_char = SUBSTR(str_in, pos_curr, 1);
        IF pos_char = separator_in THEN
            LET field_in = field_in - 1;
        END IF;

        -- Locate start of the target field
        IF field_in = 1 AND substr_start = 0 THEN
            LET substr_start = pos_curr + DECODE(pos_char, separator_in, 1, 0);
        END IF;

        -- Locate end of the target field
        IF field_in <= 0 THEN
            LET substr_length = pos_curr;
            EXIT FOR;
        END IF;
    END FOR;

    -- Handle edge cases (field is the last one or string ends)
    IF substr_length = 0 THEN
        LET substr_length = str_len + 1;
    END IF;
    IF substr_start = 0 THEN
        LET substr_start = str_len + 1;
    END IF;
    IF substr_length < substr_start THEN
        LET substr_length = 0;
    ELSE
        LET substr_length = substr_length - substr_start;
    END IF;

    RETURN NVL(SUBSTRING(str_in FROM substr_start FOR substr_length), '');

END FUNCTION;

Pros: No dependency on SUBSTRING_INDEX; fully portable logic.
Cons: More complex code; requires careful handling of positions.

Example Usage Take the string:'aa,bb,cc,dd' Call both functions to get the third field:

SELECT split_part2('aa,bb,cc,dd', ',', 3) FROM dual;
SELECT split_part('aa,bb,cc,dd', ',', 3) FROM dual;

Both return:
cc

Summary Although GBase 8s lacks a native split_part, we’ve built two reliable implementations.

Method 1 is shorter and cleaner — ideal for most cases.

Method 2 offers independence from any specific built‑in function.

Both functions have been tested and are ready to use in your GBase 8s environment. Have other string processing needs? Feel free to drop a comment below! 👇

GBase 8a Performance Troubleshooting: From Slow Query Detection to Primary‑Replica Consistency

Michael — Thu, 26 Mar 2026 10:41:27 +0000

In day‑to‑day operations with GBase 8a, the real headache often isn’t creating tables or databases — it’s the “production issues” that appear after the system goes live. A SQL query that used to run smoothly suddenly becomes slow. The cluster looks healthy, but business starts seeing result fluctuations, uneven node execution times, or local replica inconsistencies. These problems are rarely isolated. Drawing from the GBase community’s collective experience, this article lays out a structured troubleshooting path — from slow query detection all the way to primary‑replica consistency handling.

Don’t Rewrite SQL First — Figure Out Where It’s Slow The most common reflex when a query slows down is to immediately rewrite JOINs, break down subqueries, or add filters. That approach isn’t wrong, but it should come after you know whether the entire SQL is slow or just a few nodes are dragging it down.

GBase 8a provides the parameter gcluster_dql_statistic_threshold. When enabled, it records SQL execution information for queries exceeding the threshold into the system tables gclusterdb.sys_sqls and gclusterdb.sys_sql_elapsepernode. Meanwhile, the in‑memory table information_schema.SELECT_STATISTIC lets you observe currently running queries per node. The default value is 0 (disabled); when set to a positive value, only queries running longer than that threshold are recorded.

Practical approach: Set a reasonable threshold, then examine node‑level differences.
set global gcluster_dql_statistic_threshold = 3000;
Check recently recorded slow SQLs:

select *
from gclusterdb.sys_sqls
order by create_time desc
limit 20;

Then look at the execution time distribution for a specific SQL across nodes:

select *
from gclusterdb.sys_sql_elapsepernode
where sql_id = 'replace_with_actual_sql_id';

If most nodes are fast but a few are significantly slower, the problem is not “bad SQL” — it’s likely node resource imbalance, data skew, execution blocking, or even a node‑level issue. Conversely, if all nodes are slow, then the bottleneck is global: the SQL logic itself, large intermediate results, or configuration.

When Node Times Are Uneven, Suspect Data Skew First In a distributed analytical database like GBase 8a, many “slow queries” aren’t due to global performance deficiency — they’re caused by a few nodes doing most of the work.

Scenarios like distinct, group by, dynamic hash redistribution, or joining multiple small tables can easily trigger data skew. Relevant optimization parameters include gcluster_hash_redistribute_join_optimize and gcluster_hash_redistribute_groupby_optimize.

A classic example:

select region_code, count(distinct user_id)
from fact_order
group by region_code;

This looks simple, but if region_code is highly skewed — say, a few values dominate — a handful of nodes will stay busy while others finish quickly. Even if total cluster CPU isn’t maxed out, query time gets dragged down by the slowest nodes.

When node‑level time differences are large, a solid sequence is: confirm whether it’s skew; then determine if the skew comes from the original distribution key or from dynamic redistribution during execution; finally decide whether to adjust the data model, rewrite the SQL, or tune parameters.

Slow Queries Aren’t Always About Scanning — Intermediate Results Can Be Heavy In many analytical environments, the real bottleneck isn’t “reading data” — it’s oversized intermediate results, too many temporary objects, or high data exchange costs after redistribution.

For example, in a wide‑table scenario, this kind of query easily blows up:

select *
from fact_a a
join fact_b b
  on a.user_id = b.user_id
where a.stat_date >= '2026-01-01';

The problem isn’t necessarily the JOIN itself — it’s that select * makes the intermediate result set explode, and everything downstream (sorting, aggregation, data exchange) becomes heavier. For an analytical system like GBase 8a, a better practice is to select only necessary columns, push filters down as early as possible, and reduce the data volume before joining large tables.

When you suspect the bottleneck is not at the SQL text level but within the execution chain, logs become critical. express.log records important events inside the express engine, and gcluster_log_level controls log verbosity.

Look at Local Logs Alongside SQL Statistics System tables alone aren’t always enough. Some problems don’t show up in SQL statistics but appear first in service, recovery, loading, or daemon logs.

express.log: important events and exceptions from the execution engine.
system.log: stack traces when a service crashes.
gc_recover: event recovery processes.
gcware.log: node status, resource conditions, and multi‑replica operations.
Also loader_logs, loader_result, gcinstall.log, replace.log, etc.

These logs are invaluable during troubleshooting. For instance, if node‑level statistics show one node is clearly abnormal, check its logs for crashes, recovery, load conflicts, disk issues, or resource exhaustion.

Audit Information Isn’t Just for Compliance — It Helps Troubleshooting Too Many environments only look at audit tables when compliance demands it, but they’re also useful for root‑cause analysis. Combining “who did what when” with “which node was slow and how slow” often helps connect the dots faster.

If a statistical SQL suddenly slows down, node‑level stats tell you that it’s slow, but they don’t tell you what happened in the preceding hours. Pairing that with recent bulk loads, deletes, table changes, or parameter modifications makes the investigation much more efficient.

When Problems Start Looking Like Replica Inconsistency, Stop Purely Performance‑Focused Diagnosis Some issues that seem like query anomalies are actually primary‑replica consistency problems. Common causes include inconsistent local parameters, power outages, server crashes, RAID/driver issues, virtual machine failures, or manual misoperations.

GBase 8a uses direct I/O for writes; the database only considers a write successful after confirmation, but if the underlying environment fails, there’s still a risk of logical success without physical durability.

The parameter gcluster_suffix_consistency_resolve defaults to 0 (do not resolve shard consistency). When set to 1, it attempts resolution. This feature requires at least three host nodes; a two‑node cluster may not support all capabilities.

If you encounter any of these symptoms, stop treating it as a normal slow‑query problem:

The same table returns unstable results at different times.
After a node recovers, local queries start erroring.
DML operations appear successful but subsequent reads behave abnormally.
Recent power outages, node failures, manual event deletions, or local parameter changes.

At this point, first verify whether a consistency issue exists — otherwise, you’ll waste time on SQL tuning that can’t solve the real problem.

A Cleaner Troubleshooting Path Putting the above together gives a repeatable sequence:

Step 1: Determine whether the problem is “global slowdown” or “a few nodes are significantly slower.” Use gcluster_dql_statistic_threshold together with sys_sqls and sys_sql_elapsepernode.
Step 2: If it’s node‑specific, check for data skew, dynamic redistribution, or hot spots triggered by distinct/group by/JOINs. Look at execution logs to see if any stage is unusually long.
Step 3: If it’s not purely a performance issue but involves unstable results, post‑recovery anomalies, or inconsistent reads/writes, shift focus to primary‑replica consistency and underlying environment events. Don’t keep tweaking SQL.

This sequence prevents the two extremes: rewriting SQL without evidence, or rebuilding everything at the first sign of trouble. Narrow down the scope first, then decide whether to modify SQL, adjust parameters, examine logs, or handle consistency issues.

Things Often Overlooked in Production
Don’t rely on average time: In a distributed environment, averages hide node‑level drags. The slowest node often determines overall query time.
Parameters aren’t magic bullets: They’re optimization levers for specific scenarios, not universal fixes.
Don’t ignore the underlying environment: Power outages, crashes, drivers, and virtual machine issues are repeatedly mentioned in consistency‑related content — database behavior often reflects underlying conditions.
Combine logs with statistics: System tables tell you which SQL is slow and where; logs tell you why it’s slow and whether anomalies occurred alongside it.
Conclusion
The hardest part of GBase 8a performance and stability isn’t finding a “magic parameter” — it’s breaking the problem into the right layers. A slow query may not be poorly written SQL; it could be node skew. A healthy cluster status doesn’t guarantee a healthy execution path. And once a problem reaches the primary‑replica consistency layer, ordinary SQL tuning will only lead you in circles.

The GBase community’s resources on node‑level statistics, execution logs, parameter tuning, and consistency handling together offer a practical methodology: first observe node differences, then analyze execution phases, then decide whether it’s a distribution/intermediate‑result issue, and finally determine if you need to move into consistency troubleshooting.

Make Your Queries 10x Faster with GBase 8a Rough Index

Michael — Thu, 26 Mar 2026 10:10:18 +0000

When working with big data analytics in GBase 8a, you’ve probably noticed something strange: the same SQL query can be incredibly fast sometimes, yet painfully slow at others. The key difference often comes down to whether you’re leveraging Rough Index — a lightweight indexing mechanism built into the GBase 8a columnar storage engine.

In this article, I’ll walk you through what Rough Index is, how it works, and how to use it effectively to dramatically speed up your queries.

What Is Rough Index? Rough Index (RI) is a block‑level index that doesn’t store exact row positions like a B‑Tree. Instead, for each data block (typically 64KB or 128KB), it records the minimum and maximum values of a column within that block.

Example: Suppose the amount column in an orders table is split into 1,000 blocks. Rough Index might store:

Block 1: min=10, max=500
Block 2: min=501, max=1200
Block 3: min=1201, max=2500
…

Now when you run a query like WHERE amount > 2000, the GBase 8a optimizer scans the Rough Index first. Any block whose maximum value is ≤ 2000 is skipped entirely — no actual data is read. This is called block pruning.

When data is well‑ordered, Rough Index can prune 90% or more of I/O operations, making your queries 5 to 10 times faster with no extra hardware.

How to Check If Rough Index Is Working Use EXPLAIN to View the Execution Plan

EXPLAIN SELECT SUM(amount) FROM orders WHERE create_time >= '2024-01-01';

If you see “Rough Index Scan” in the output, or the number of blocks_scanned is much smaller than the total block count, Rough Index is active.

Query the System View

SELECT * FROM information_schema.GBASE_ROUGH_INDEX 
WHERE table_name = 'orders';

This view shows which columns have Rough Index enabled and their current status.

How to Maximize Rough Index Effectiveness Tip 1: Keep Data Ordered During Import Rough Index pruning works best when data is sorted by the column you frequently filter on. If values are randomly distributed across blocks, each block’s min/max range becomes wide, and pruning becomes less effective. Best practice: Sort your input file by the filter column before loading.

LOAD DATA INFILE '/data/orders_sorted.csv' INTO TABLE orders
FIELDS TERMINATED BY ',' (order_id, user_id, amount, create_time);
-- Make sure the file is sorted by create_time before loading

Tip 2: Rebuild Rough Index After Updates
If your table undergoes many updates or out‑of‑order inserts, you can manually rebuild the index:

ALTER TABLE orders REBUILD ROUGH INDEX;

Tip 3: Use Range Filters for Best Results
Rough Index performs best with range predicates (>, <, BETWEEN). It works less well with IN lists or !=.
✅ Good: Range query

SELECT * FROM orders 
WHERE create_time BETWEEN '2024-01-01' AND '2024-03-31';

❌ Not ideal: Discrete value list

SELECT * FROM orders 
WHERE status IN (1, 3, 7, 15);

Summary Rough Index is a powerful, built‑in performance feature in the gbase database. By understanding its block‑pruning mechanism and designing your data loading and queries accordingly, you can achieve massive speedups without adding hardware.

If you’re using GBase 8a for analytical workloads, take a moment to check your Rough Index usage — it might be the secret to making your queries run 10x faster!

Boost JDBC Performance with GBase 8s: Understanding IFX_AUTOFREE

Michael — Tue, 24 Mar 2026 12:45:40 +0000

When working with JDBC, repeatedly creating and closing ResultSet and Statement objects can generate unnecessary network round trips. The GBase 8s JDBC driver offers a handy parameter — IFX_AUTOFREE — that helps reduce this overhead and improves performance in high‑query scenarios.

What Does IFX_AUTOFREE Do?

IFX_AUTOFREE is a boolean parameter (0 = disabled, 1 = enabled). When enabled, the server automatically frees the cursor as soon as the client calls ResultSet.close(). Consequently, when Statement.close() is later invoked, the JDBC driver does not send an extra cursor‑release message to the server, saving one network round trip per statement.

How to Enable It

You can enable IFX_AUTOFREE in two ways:

1. Via the JDBC URL

jdbc:gbasedbt-sqli://:/:IFX_AUTOFREE=1;DB_LOCALE=en_US.819

text

2. Via a Properties Object

java
Properties props = new Properties();
props.put("IFX_AUTOFREE", "1");
Connection conn = DriverManager.getConnection(url, props);

When to Use It
This parameter shines in scenarios where you run many short queries, such as in loops that repeatedly execute SELECT statements and immediately close the result set. By eliminating one network round trip per query, it can significantly boost throughput.

Performance Test
The following examples compare execution times for 1000 queries under different configurations.

Without IFX_AUTOFREE (Baseline)

java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=0;DB_LOCALE=en_US.819";
// Inside loop: try (PreparedStatement ps = conn.prepareStatement("SELECT * FROM t1");
//                ResultSet rs = ps.executeQuery()) { rs.next(); }
// Output: time:2898 ms

With IFX_AUTOFREE=1

java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
// Same loop using PreparedStatement + ResultSet
// Output: time:2343 ms
Result: ~19% faster than baseline.

Using Statement Instead of PreparedStatement (with AUTOFREE=1)

java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;DB_LOCALE=en_US.819";
// Inside loop: try (Statement stmt = conn.createStatement();
//                ResultSet rs = stmt.executeQuery("SELECT * FROM t1")) { rs.next(); }
// Output: time:2376 ms
Performance is similar to the PreparedStatement case.

Combining with OPTOFC OPTOFC (Open‑Fetch‑Close) is another optimization parameter. When used together with IFX_AUTOFREE, it can further reduce network round trips:

java
String url = "jdbc:gbasedbt-sqli://192.168.226.180:12888/testdb:IFX_AUTOFREE=1;OPTOFC=1;DB_LOCALE=en_US.819";
// Loop as in example 2
// Output: time:1228 ms

The combination yields a dramatic improvement (1228 ms, about 58% faster than baseline).

Summary
IFX_AUTOFREE is a simple yet powerful JDBC parameter in the gbase database ecosystem. By enabling it, you can eliminate one network round trip per statement closure, resulting in noticeable performance gains for high‑volume query workloads. For even better results, consider pairing it with OPTOFC.

If you have any questions or want to share your own experiences, feel free to leave a comment! 🚀

Understanding the ISNULL() Function in GBase 8s

Michael — Tue, 24 Mar 2026 12:23:38 +0000

Handling NULL values is a common challenge in database queries. The gbase database provides a flexible ISNULL() function that works in two forms: with two parameters or with one. Let's dive into how it works and see some practical examples.

Two‑Parameter Form: `ISNULL(a, b)`

If a is not NULL, returns a.
If a is NULL, returns b.
If both are NULL, returns NULL.

This behaves like returning the first non‑NULL value from left to right.

One‑Parameter Form: `ISNULL(a)`

If a is NULL, returns the boolean t (true).
Otherwise, returns f (false).

This is useful for checking whether a column or expression is NULL.

Examples

Two‑Parameter Examples

SELECT ISNULL(12, NULL) FROM DUAL;   -- Result: 12
SELECT ISNULL(NULL, 'aa') FROM DUAL; -- Result: 'aa'
SELECT ISNULL(NULL, NULL) FROM DUAL; -- Result: NULL
SELECT ISNULL(ISNULL('abc', NULL), 'ef') FROM DUAL; -- Result: 'abc'
One‑Parameter Examples
sql
SELECT ISNULL('aa') FROM DUAL;   -- Result: 'f'
SELECT ISNULL(NULL) FROM DUAL;   -- Result: 't'
SELECT ISNULL('null') FROM DUAL; -- Result: 'f'
Real‑World Scenario
Let's create a table and use ISNULL() in a CTE to identify NULL values and compute aggregations.

sql
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT, col1 INT, col2 INT);
INSERT INTO t1 VALUES (1,1,1), (2,NULL,NULL), (3,4,4), (4,0,0);

WITH tmp1 AS (
  SELECT id, col1, ISNULL(col1) AS null_flag, SUM(col2) AS total_col2
  FROM t1
  GROUP BY id, col1
),
tmp2 AS (
  SELECT id, col1, ISNULL(tmp1.total_col2) AS null_col2
  FROM tmp1
  WHERE id > 1 AND ISNULL(col1) = 't'
)
SELECT id, col1, null_col2 FROM tmp2;
-- Output: id=2, col1=NULL, null_col2=1

Summary
The ISNULL() function in GBase 8s provides a clean way to handle NULL values. Use the two‑parameter form to return a fallback value, and the one‑parameter form to quickly test for NULL. Whether you're writing simple queries or building complex reports, this function is a handy tool in your SQL toolbox.

If you have any questions or want to share your own examples, feel free to leave a comment below! 🚀

Common Parameters for GBase 8a MPP Cluster Command-Line Clients: gccli and gncli

Michael — Mon, 23 Mar 2026 15:50:01 +0000

GBase 8a MPP Cluster provides two command-line client tools—gccli and gncli—for connecting to the database and executing SQL statements. Mastering their parameters can significantly improve daily operational efficiency. Below is a summary of commonly used options.

Connection and Basic Parameters

-h: Specifies the IP address(es) to connect to. Multiple IPs can be separated by commas to support high availability.
-u: Database username.
-p: Database password.
-P: Specifies the port number.
-D: Sets the default database to use.

Execution and Output Control

-e: Executes a given SQL statement directly without entering interactive mode.
-E: Displays results vertically, similar to appending \G to a query.
-N: Suppresses column names in the result set, returning only data.
-s: Enables silent mode, reducing the amount of output.
-vvv: Outputs detailed execution information, including execution time, which is useful for debugging and performance analysis.

Behavior Control

-c: Allows the use of hints. By default, content inside /* */ is treated as a comment and filtered out. With -c, such content is sent to the server as a hint.
-f: When executing multiple SQL statements, if an error occurs, the client skips the failing statement and continues with the rest.
-q: Enables unbuffered output. By default, gccli buffers the entire result set in memory before displaying it, reducing I/O operations. However, for large result sets, this can consume significant memory. Using -q outputs results directly, reducing memory pressure.

These parameters cover common needs ranging from connection management to execution behavior. Combining them flexibly allows you to adapt to various development and operational scenarios when working with the gbase database. For users of GBASE products, understanding these options helps make daily interactions with the database more efficient and tailored to specific workflows.

GBase 8s Trigger with Stored Procedure Example

Michael — Mon, 23 Mar 2026 15:39:14 +0000

In GBase 8s, triggers alone do not support conditional logic or exception handling. To implement such functionality, you can combine a trigger with a stored procedure—a pattern often called a triggered stored procedure.

Below is an example using an INSERT trigger to check for duplicate IDs and raise an exception when needed.

Create Sample Tables

Create two tables: tab1 as the target for insertion, and tab2 to track existing IDs.

sql
CREATE TABLE tab1 (
id INTEGER PRIMARY KEY,
name VARCHAR(40)
);

CREATE TABLE tab2 (
id INTEGER,
name VARCHAR(40),
uptime DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND
);

Create the Stored Procedure

The stored procedure performs the validation: it checks whether the id being inserted already exists in tab2, and if so, raises an exception.

sql
CREATE PROCEDURE proc_tri_insert_tab1()
REFERENCING NEW AS new FOR tab1;
DEFINE pid INT;
SELECT COUNT(id) INTO pid FROM tab2 WHERE id = new.id;
IF pid = 1 THEN
RAISE EXCEPTION -746, 0, 'ID exists!';
END IF;
END PROCEDURE;

Create the Trigger

Define an INSERT trigger on tab1 that executes the stored procedure for each row.

sql
CREATE TRIGGER tri_insert_tab1 INSERT ON tab1
FOR EACH ROW (
EXECUTE PROCEDURE proc_tri_insert_tab1() WITH TRIGGER REFERENCES
);

Test the Behavior

When you insert a row into tab1 with an id that already exists in tab2, the trigger invokes the stored procedure, which raises an exception:

sql
INSERT INTO tab1 VALUES (2, 'testssdadsfas');
-- Error: 746: ID exists!
``
This technique extends the capabilities of triggers in the gbase database, allowing you to enforce complex business rules seamlessly. For developers working with GBASE products, leveraging triggered stored procedures is a practical way to add sophisticated logic to data modification events while keeping the database design clean and maintainable.