HSQLDB Create Trigger Examples
The HSQLDB database provides support for creating triggers using the CREATE TRIGGER statement. Triggers can be written to perform SQL statments in the trigger body such as insert, update, or delete statements. HSQLDB triggers can also call Java functions. The examples below will use SQL statements in the body of the triggers.
Triggers can be written to be execute before or after insert, update, or delete statements. In the examples below, part of the create trigger statement specifies when the trigger gets executed. For example, if the create trigger statement specifies AFTER INSERT, the trigger fires after the row is inserted. If the statement specifies BEFORE UPDATE, the trigger fires before the row is updated.
Example 1: After Insert Trigger
The below create trigger statements creates a trigger that inserts a log record into the EMPLOYEE_LOG table every time a row is inserted into the EMPLOYEE table.
CREATE TRIGGER EMPLOYEE_LOG AFTER INSERT ON EMPLOYEE
REFERENCING NEW ROW as newrow FOR EACH ROW
INSERT INTO EMPLOYEE_LOG(LNAME, STATUS) values (newrow.lname, 'INSERT')
Example 2: Before Update Trigger
The below trigger trims any spaces from an employee first name before the employee table record is updated.
CREATE TRIGGER EMPLOYEE_NAME_CHECK BEFORE UPDATE ON EMPLOYEE
REFERENCING NEW AS newrow FOR EACH ROW
SET newrow.FNAME = TRIM(newrow.FNAME)
Example 3: After Delete Trigger
The below trigger inserts a record into the EMPLOYEE_LOG table whenever a row is deleted from the EMPLOYEE table. This AFTER DELETE trigger references OLD ROW as opposed to an AFTER INSERT trigger that would reference a NEW ROW.
CREATE TRIGGER EMPLOYEE_DELETE_LOG AFTER DELETE on EMPLOYEE
REFERENCING OLD ROW as oldrow FOR EACH ROW
INSERT INTO EMPLOYEE_LOG(LNAME, STATUS) values (oldrow.lname, 'DELETE')
