Trigger creation

[andreinoitalia]

Hi to everybody!

I am just practicing with the creation of TRIGGERs. Without surprise, failing...

I have 2 tables:

TABLE_A(ID INTEGER PK AUTOINCREMENT, NAME VARCHAR(20) NOT NULL)
TABLE_B(ID INTEGER PK AUTOINCREMENT, NAME VARCHAR(20) NOT NULL)

This is the SQL command I am using. I wrote it in the Tools->SQL... dialog box for a direct run.

CREATE TRIGGER TRIG_TRY AFTER INSERT ON TABLE_A FOR EACH STATEMENT INSERT INTO TABLE_B(NAME) VALUES('OK')

I could not figure out anything simpler about triggers.
This command is rejected with "unexpected token" at STATEMENT.
If I substitute STATEMENT with ROW, the command is then rejected with "unexpected end of command" at INSERT.

sadly this is all...

Thanks to everybody making OpenOffice a better thing!

Andrea

[r4zoli]

You must use HSQLDB syntax, details on triggers in HSQLDB 9th chapter.

I never tried triggers, but it seems to me, that the HSQLDB 1.8 trigger support is limited.

[andreinoitalia]

Well, thanks for your reply.
I checked the link, but it's a 2007 revision. I used the syntax I found at the latest:


http://hsqldb.org/web/hsqlDocsFrame.html

it seems to me there's something wrong with the engine interpreting the command, but for sure, that' s me mistaken...

Thanks

Andrea

[eremmel]

The database that comes with OOo Base is HSQLDB 1.8 (likely from 2007). The documentation you are referring to is from version 2.0. That might be supported in OOo 3.3 so that will take some time... But you can always use an external database and use Base and a front end to do your work.

[r4zoli]

Well, thanks for your reply.
I checked the link, but it's a 2007 revision. I used the syntax I found at the latest:


http://hsqldb.org/web/hsqlDocsFrame.html

it seems to me there's something wrong with the engine interpreting the command, but for sure, that' s me mistaken..

HSQLDB documentation went to the newest version 2.0.
OOo 3.4 will include the HSQLDB 2.0.

The documentation problem described here: HSQLDB 1.8 documentation not easily reachable

Editedm changed to OOo 3.4 version, this will include hsqldb 2.0

[yrebrac]

Just to follow up on this, I've been trying to do triggers with a native (HSQLDB) db. The 1.8 documentation seems to be missing any in depth discussion on triggers so it's not possible to work out what Base supports or doesn't. Can anyone confirm that the 2.0 syntax is indeed different to the 1.8? I am also getting errors with what appears to be valid 2.0 syntax.

[r4zoli]

In OOo until 3.3.x (which is a next version) embedded HSQLDB version 1.8.0.10.

If you try to work it with 2.0.0 syntax, it will not show good results.

The 1.8.x.x and 2.0.0 syntax is different, first follows the SQL-92 , the second follow SQL 2008 standard, which is based on earlier version, but different in lot of places.

[yrebrac]

Thanks for the heads-up, it's now clear I need to use an external db.