[Solved] Trigger creation fail: Unexpected end of command

[jonAbq]

Trigger:

Code: Select all

CREATE TRIGGER productDateTrigger BEFORE UPDATE ON "product"
REFERENCING NEW ROW AS NEWROW 
FOR EACH ROW
BEGIN ATOMIC
SET NEWROW."productDate" = CURRENT_TIMESTAMP;
END

Error message:

Unexpected end of command: REFERENCING in statement [CREATE TRIGGER productDateTrigger BEFORE UPDATE ON "product"
REFERENCING]

Can someone please tell me what I'm doing wrong? I looked at a lot of different posts, but I'm not seeing what I'm doing wrong?

[Sliderule]

jonAbq:

Welcome to the OpenOffice / LibreOffice Base forum.

To help you answer your question . . . I would like to ask a few questions, so, we can find the solution.

1. Please confirm, how you are executing the CREATE TRIGGER statement. That is:
   1. From within the Query sub-system ?
   2. From Base Menu: Tools - SQL...
   3. From external tool, reading / writing to your HSQL database ?
   You can NOT use item a. above, since, the command is NOT a Query, but, item b. or c. are the correct places to issue the command.
2. What is the version of HSQL are you using? If it is HSQL 1.8.0.10 . . . it ( the Trigger command ) will not work.
3. Please confirm, the names of our table and column(s) are correct . . . that is . . . defined exactly as you have defined them in your database . . . including . . . CASE: UPPER, Mixed, lower , since, for example, "productDate" is not the same as "ProductDate" .
4. You are using the HSQL function: CURRENT_TIMESTAMP and, according to HSQL 2.3 and HSQL 2.4 documentation:

   
   These datetime functions return the datetime value representing the moment the function is called. CURRENT_DATE
   returns a value of DATE type. CURRENT_TIME returns a value of TIME WITH TIME ZONE type. LOCALTIME
   returns a value of TIME type. CURRENT_TIMESTAMP returns a value of TIMESTAMP WITH TIME ZONE type.
   LOCALTIMESTAMP returns a value of TIMESTAMP type. If the optional [ ( <time precision> ) ] or
   [ ( <timestamp precision> ) ] is used, then the returned value has the specified fraction of the second
   precision.]

   The point, if your TIMESTAMP column is NOT defined WITH TIME ZONE . . . you should use LOCALTIMESTAMP rather than CURRENT_TIMESTAMP . OR, if the column in your database is defined as a DATE ( not a TIMESTAMP ) . . . perhaps you should use CURRENT_DATE .

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jonAbq]

Thank you for getting back to me. Please see answers below to your questions:
1. I am executing the trigger from tools > sql
2. Version: 5.3.3.2
3. table name: product | column name: productDate
4. It is a date field, so it should be CURRNET_DATE; however, I am receiving the following error message:

1: Unexpected end of command: REFERENCING in statement [CREATE TRIGGER productDateTrigger BEFORE UPDATE ON "product"
REFERENCING]

New Trigger changed to following:

Code: Select all

CREATE TRIGGER productDateTrigger BEFORE UPDATE ON "product"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
SET NEWROW."productDate" = CURRENT_DATE;
END

[Sliderule]

Your response for by Question 2. above, is not what I was asking for ( and I understand the confusion ).

While you are using LibreOffice Version 5.3.3.2 , the database back-end that Base is using is not clear to me. The default, included database back-end, as the date I am writing this, with both OpenOffice and LibreOffice is HSQL 1.8.0.10 and, the CREATE TRIGGER command as you wrote it, is NOT supported.

If, on the other hand, you have upgraded your database back-end to HSQL 2.x or above ( as of today, the current release of HSQL is 2.4.0 ) and this is a SPLIT database, NOT and Embedded database . . . you can issue the CREATE TRIGGER command.

To determine your HSQL database version . . . please refer to the link below, the entry by Sliderule :

viewtopic.php?f=13&t=82854&p=384062&hil ... ll#p384062

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jonAbq]

Thank you for your help. I will have to update my database engine.