GBase 8s Trigger with Stored Procedure Example

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