Modify example TRIGGER BEFORE UPDATE HSQL 2.7.1

[dreamquartz]

Hello All,

REFERENCE: (pg 227 HyperSQL User Guide HyperSQL Database Engine 2.7.1)
Trying to modify

Code: Select all

CREATE TRIGGER t BEFORE UPDATE ON customer
	 REFERENCING NEW AS newrow FOR EACH ROW
	 BEGIN ATOMIC
		 IF LENGTH(newrow.firstname) > 10 THEN
			 SET newrow.firstname = LOWER(newrow.firstname);
		 END IF;
	 END

This example is very close to what I am looking for, but cannot change it as such that it suits my needs.

Code: Select all

IF LENGTH(newrow.firstname) > 10 THEN
			 SET newrow.firstname = LOWER(newrow.firstname);

should be modified to something like:

Code: Select all

IF
	 (
		 "tPerson"."Surname" = NEWROW."Surname" AND
		 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
	 ) SET "tPerson"."Check" = 'TRUE';

Combining them would look like

Code: Select all

CREATE TRIGGER PERSON_BU_NEW BEFORE UPDATE ON "tPerson"
	 REFERENCING NEW AS newrow FOR EACH ROW
	 BEGIN ATOMIC
		 IF
			 (
				 "tPerson"."Surname" = NEWROW."Surname" AND
				 "tPerson"."DateOfBirth" = NEWROW."DateOfBirth"
			 ) SET "tPerson"."Check" = 'TRUE';
		 END IF;
	 END

This is the error code:

1: user lacks privilege or object not found: tPerson.Surname at /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:173

that continuously appears.

Any thoughts?

Thanks in advance,

Dream

[robleyd]

Do any of the results from this search help?

[dreamquartz]

It appears therefore to be a connection error with the hsqldb.jar?

I run my databases without any problem, and different TRIGGERS run in most of them.

I use FreeHSQLDB (latest: FreeHSQLDB-0.4.0.oxt) from Villeroy for years already to be able to identity 1 hsqldb.jar in the general section of a 'folder-cluster'.
That meas that I only use one hsqldb.jar per multiple databases?
It would make sense to use the same hslqldb.jar for many different databases, so there is only one 'jar' I have to update when the time comes.

@Villeroy,
Can you please shed some light on this?
Am I able to select one and the same hsqldb.jar for different databases, using FreeHSQLDB?

Dream