Trying to insert a Trigger to update a field

Creating tables and queries
Post Reply
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Trying to insert a Trigger to update a field

Post by Colkard »

Hi all,
I'm trying to insert a trigger from command line in OpenOffice 4.1.3, and I'm using this configuration:
Tools --> Options --> OpenOffice --> Java --> "Use..." checked --> classpath --> add package --> "The file (hsqldb.jar) downloaded from that i downloaded from here: Access2Base
http://www.access2base.com/access2base.html
So... This is the code that I'm trying to execute:

Code: Select all

CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
FOR EACH ROW 
BEGIN ATOMIC
    update "Costes" set "importenoiva" = (NEW."importe");
END
And That give me the following error:

Code: Select all

1: Unexpected end of command: BEGIN in statement [CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
FOR EACH ROW 
BEGIN]
PS: I execute this code from: Tools --> SQL....
PS2: I had a similar problem in mysql but I solved it there changing the delimiter to //
this is a sample code:

Code: Select all

CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
FOR EACH ROW 
BEGIN ATOMIC
    update "Costes" set "importenoiva" = (NEW."importe");
END//
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Trying to insert a Trigger to update a field

Post by eremmel »

The trigger help page states:
If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names. The REFERENCING clause is used to give names to the old and new rows. The clause, REFERENCING OLD | NEW TABLE is used for statement level triggers. The clause, REFERENCING OLD | NEW ROW is used for row level triggers. If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names. In the SQL statements, the columns of the old or new rows are qualified with the specified names.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

I tried that too but didn't worked:
CREATE TRIGGER "t1" AFTER UPDATE ON "DesgloseCostes"
REFERENCING NEW AS newrow FOR EACH ROW
FOR EACH ROW
BEGIN ATOMIC
update "Costes" set "importenoiva" = (NEW."importe");
END

I still get this error:

Code: Select all

1: Unexpected end of command: REFERENCING in statement [CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
REFERENCING]
Now I'm trying to update the version of hsqldb to the lastest. In stackoverflow a guy said to me that may be that that you said but the problem can be also:
"Check the HSQLDB JAR version and make sure it is later than version 2.20"
So
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Trying to insert a Trigger to update a field

Post by Sliderule »

Colkard:

Please read your post in the link below, with the wise response given by Fred:

http://stackoverflow.com/questions/4253 ... ffice-base

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

Yes, I tried that code, but got the same error. Now Im trying to update my version of hsqldb of openoffice but I dont know how to do it :(
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Trying to insert a Trigger to update a field

Post by eremmel »

Colkard wrote:Yes, I tried that code, but got the same error. Now Im trying to update my version of hsqldb of openoffice but I dont know how to do it :(
The guy that started the post TRIGGER use with a VIEW to prevent duplicates, made also a cross post to here, were you find his database example with the right jar file. I used it to help solving his issue.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Trying to insert a Trigger to update a field

Post by F3K Total »

Did you already split your .odb-file?
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

@eremmel One thing that i noticed is that I only have the file ".odb" I don't have any folder named "database", and "driver" neither. Maybe I should do something to have these files/folders?
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

@F3K Total No, I didn't :( I'll see your link and then when I do that I'll say if my problem was solved. Thanks all!
PS: Is that "split your .odb-file" totally necesary?
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Trying to insert a Trigger to update a field

Post by F3K Total »

Colkard wrote:Is that "split your .odb-file" totally necesary?
If you want to use an upgraded HSQL-version, needed to run triggers, in combination with OpenOffice as frontend, YES!
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

Okay, Thans for your help!
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Trying to insert a Trigger to update a field

Post by Sliderule »

For anyone else reading this forum post ( as of 2017-03-01 ), user Colkard has a signature line of:
Colkard wrote:OpenOffice 4.3.1 on Windows 10
As of 207-03-01, the latest Version of OpenOffice is 4.1.3 and OpenOffice 4.3.1 does not exist.

Link below give information on changing the User Profile Signature line:

viewtopic.php?f=50&t=87114&p=408094

Sliderule
Colkard
Posts: 9
Joined: Wed Nov 09, 2016 10:52 am

Re: Trying to insert a Trigger to update a field

Post by Colkard »

Signature updated, thanks for telling!
OpenOffice 4.1.3 on Windows Server 2012 R2 Standard
Post Reply