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

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.

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

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

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

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

2. 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! 👇