Generate trigger in split DB to create field derived from another

rotormansa · April 10, 2017, 10:11pm

I am using a split database and have the need to sort one of the fields in a report. The field has letters and numbers and I wish to only sort for characters to the right of the number. Ex: KC1SA KC1AXJ, W1KKF, KB1JL would be sorted as AXJ, JL, KKF, SA. I have the beginnings of an SQL implementation using CASE statements but that doesn’t lend it self to be in the database table. I’m looking for a way to have perhaps another field be populated by the content of this parent field then I could generate the report based on the his generated field.

Much thanks for the assistance.

Ratslinger · April 10, 2017, 10:19pm

Creating triggers in HSQLDB is discussed in Chapter 9 of the guide (pdf of v2.3 found here.

A trigger in a database can be used before or after a record is created to perform additional functions when a NEW, UPDATE, or Delete of a record occurs. In your case a BEFORE INSERT and a BEFORE UPDATE will be needed.

SQL of this type needs to be entered from the initial screen menu Tools->SQL.... There are two main items to a trigger - creation and deletion. The first time you only need the creation section, but if changes to the trigger are to be done it must first be deleted and the created with the new changes.

Now the problem with entering SQL in this method is that Base has no mechanism for saving these statements. You can save them in text files for future reference or you can use a seperate program such a SQL Workbench/J. This program allow connection to the DB (cannot do this if using embedded DB) and storage of SQL as well as the ability to execute the statements.

Now for the actual statements. Based upon the previous post, here are the necessary statements to create a trigger when inserting new records:

To drop (delete) trigger used on new record creation:

DROP TRIGGER PUBLIC.PUBLIC.INSERT_SORTFLD

To create (add) trigger used on new record creation:

CREATE TRIGGER INSERT_SORTFLD
BEFORE INSERT ON TESTSAMPLE

REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
SET NEWROW.SORTFLD= CASE WHEN LOCATE('0',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('0',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('1',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('1',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('2',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('2',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('3',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('3',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('4',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('4',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('5',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('5',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('6',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('6',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('7',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('7',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('8',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('8',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('9',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('9',NEWROW.STRFIELD) + 1) ELSE
				NEWROW.STRFIELD END END END END END END END END END END

To drop (delete) trigger used on record updating:

DROP TRIGGER PUBLIC.PUBLIC.UPDATE_SORTFLD

To create (add) trigger used on new creation updating:

CREATE TRIGGER UPDATE_SORTFLD
BEFORE UPDATE ON TESTSAMPLE

REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
SET NEWROW.SORTFLD= CASE WHEN LOCATE('0',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('0',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('1',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('1',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('2',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('2',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('3',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('3',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('4',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('4',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('5',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('5',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('6',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('6',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('7',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('7',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('8',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('8',NEWROW.STRFIELD) + 1) ELSE
				CASE WHEN LOCATE('9',NEWROW.STRFIELD) > '0' THEN SUBSTR(NEWROW.STRFIELD, LOCATE('9',NEWROW.STRFIELD) + 1) ELSE
				NEWROW.STRFIELD END END END END END END END END END END

In the above, the following needs to be defined:

Table name used was TESTSAMPLE
The input field was STRFIELD
The resulting field was SORTFLD

INSERT_SORTFLD was the name of the given to the trigger in the insert new record trigger (first trigger above).
UPDATE_SORTFLD was the name of the given to the trigger in the update record trigger (second trigger above).

Please refer to the HSQL manual for further information. Triggers can be used for many things such as adding new records, inserting values depending upon other data and more.