Create TRIGGER???

Try this:

CREATE TRIGGER
	trigCustomerName
AFTER INSERT ON
	"Customers"
REFERENCING
	NEW ROW AS newrow
FOR EACH ROW BEGIN ATOMIC
	UPDATE "Customers"
	SET newrow."CustomerName" = newrow. COALESCE("FirstName",'') || newrow."LastName";
END

Tips: Work on a COPY of your table, just in case the UPDATE does something unexpected you don’t want. The COALESCE function is used to deal with a NULL FirstName

I tried to run your suggestion and I get the error shown below. Am I using the correct process, I go to menu Tools -> SQL… and type in my code and click ?

Yes, you are doing the correct process. One question – what version of HSQLDB are you using? If you are using the default version 1.8 which comes with the standard ‘embedded’ Base installation, then you’re out of luck because that version won’t run TRIGGERS. (You need HSQLDB version 2.3.x which you may use connecting Base to the HSQLDB database in ‘split’ mode.

Anyway, just delete the text PUBLIC. in the Trigger creation statement I posted before (I’ve updated it), and see what happens. You should get a message saying the TRIGGER was created successfully if it works.

OK. Here’s some info on converting your Base database into ‘split’ mode.

It occurred to me though that you don’t really need to write the full name into your table, because it can always be ‘calculated’ by concatenation on-the-fly in a QUERY (like the one you are uing in the TRIGGER CREATION statement. So you mightn’t really need to worry about TRIGGERS at all! (And therefore also avoid having to do the ‘split’ mode conversion to use the latest HSQLDB version 2.3.x)

CREATE TABLE "CUSTOMERS"
    (
    "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH <img src="http://en.libreofficeforum.org/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content"/> NOT NULL PRIMARY KEY,
    "FirstName" VARCHAR(20) NOT NULL,
    "LastName" VARCHAR(20) NOT NULL,
    "CustomerName" VARCHAR(41) GENERATED ALWAYS AS(("CUSTOMERS"."FirstName"||' ')||"CUSTOMERS"."LastName"),
    "CustomerName2" VARCHAR(42) GENERATED ALWAYS AS(("CUSTOMERS"."LastName"||', ')||"CUSTOMERS"."FirstName")
    )

R

Maybe once the new row has been inserted in your customer table (because you are using the AFTER INSERT clause), you should just reference the target table as “CustomerName” (not newrow.“CustomerName” as I suggested earlier) – so try:

CREATE TRIGGER
	trigCustomerName
AFTER INSERT ON
	"Customers"
REFERENCING
	NEW ROW AS newrow
FOR EACH ROW BEGIN ATOMIC
	UPDATE "Customers"
	SET "CustomerName" = newrow.COALESCE("FirstName",'') || newrow.COALESCE("LastName",'');
END

Let us know if this does or does not work. (I have indeed got a rather similar trigger to work without a problem. )

Did you try the method of setting default values using the GENERATED ALWAYS AS syntax as suggested by F3K Total?

I have tested your latest solution out on my own sample database, and yes, F3K Total is correct. Your solution updates the fullname column of ALL the rows with the the current (new) value of “CustomerName” (you have to REFRESH the table to see this result). Not what you want!

But this modified TRIGGER works on my setup:

CREATE TRIGGER
	trigCustomerName
AFTER INSERT ON
	"Customers"
REFERENCING
	NEW ROW AS newrow
FOR EACH ROW BEGIN ATOMIC
	UPDATE "Customers"
	SET "CustomerName" = COALESCE(newrow."FirstName",'') || COALESCE(newrow."LastName",'') WHERE "Customers"."id"= newrow."id";
END

Note: the update is restricted to ONLY the row matching the newrow id value (using the WHERE clause). So it can be done with a TRIGGER (with HSQL 2.3.x). But I think F3K Total’s two alternative solutions are very nice also.

To which question (at least with enough details to give an answer).