Search code examples
sqltriggershsqldbopenoffice.org

Trying to insert a Trigger to update a field OpenOffice Base

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:

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:

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:

CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
FOR EACH ROW
BEGIN ATOMIC
update "Costes" set "importenoiva" = (NEW."importe");
END//

Thanks for your help!

this is where I'm putting the new hsqldb folder, but I had the same error, i tried to select this folder, the super folder, the super/super folder and tried to to just put the hsqldb/lib/hsqldb.jar file as package, but still nothing

There is where I'm putting the new hsqldb folder, but I had the same error, i tried to select this folder, the super folder, the super/super folder and tried to to just put the hsqldb/lib/hsqldb.jar file as package, but still nothing

Solution

  • Check the HSQLDB JAR version and make sure it is later than version 2.20. The trigger definition needs an extra line with REFERENCING ...

    Your code is updating all the rows in the "Costes" table. The UPDATE statement needs a WHERE clause with a condition to select only the row you want to update. 

    CREATE TRIGGER  "t1" AFTER UPDATE ON "DesgloseCostes"
REFERENCING NEW ROW AS NEWROW
FOR EACH ROW
BEGIN ATOMIC
update "Costes" set "importenoiva" = NEWROW."importe" WHERE ... ;
END//