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.

1. 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
);

1. 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;

1. 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
);

1. 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.