H2 Triggers and Classes

[Szaijan]

Hi,

I'm new to H2 SQL programming, with a modicum of experience in Oracle
SQL programming. My database needs are fairly simple, but I am having
issues with some basic SQL statements that worked fine in Oracle
coming back with errors in H2.

I am creating an object database where H2 will be used to store object
JSON code and the meta data used to retrieve those objects. I want
every entry to have a unique numeric ID. In Oracle, I used a trigger
to pull the next value from a sequence and inserted that on insert.
My trigger looked as follows:

create trigger V_USERS_ID
before insert on V_USERS
for each row
begin
select ID_SEQ.NEXTVAL into :NEW.ID from DUAL;
end
/

I suspect H2 doesn't use the DUAL table, so that piece is likely out,
but the bigger problem is that the syntax is requiring me to call come
sort of predefined class to replace my single line of SQL code I want
to run. Is there a way to program the above SQL directly in H2, or am
I stuck trying to figure out how to define and declare an external
class? If the latter, any pointer to documentation on how to go about
this would be greatly appreciated.

Failing either of the above, is there a simple way to implement an
automatically assigned unique numeric identifier to an ID field, one
which will overwrite any value included for that field in an insert
statement? I've looked at the UUID, but it seems like I'd need the
same sort of mechanism to insert a random UUID as I need to insert a
sequential ID.

Again, any pointers would be greatly appreciated.

[Thomas Mueller]

Hi,

I suspect H2 doesn't use the DUAL table

Actually, DUAL is supported.
 

but the bigger problem is that the syntax is requiring me to call come
sort of predefined class to replace my single line of SQL code I want
to run.  

Yes, H2 doesn't support PL/SQL (BEGIN ... END) currently, and triggers need to call a Java method. Support for this is on the roadmap (procedural language), but it will still take some time.

Is there a way to program the above SQL directly in H2

Yes:

CREATE TABLE V_USERS(ID AUTOINCREMENT,...);
or
CREATE TABLE V_USERS(ID BIGINT PRIMARY KEY DEFAULT ID_SEQ.NEXTVAL,...);

See also:
http://www.h2database.com/html/grammar.html#createtable

Regards,
Thomas

[Szaijan]

Thank you Thomas, that's a big help. I missed the AUTO_INCREMENT
option in the CREATE_TABLE syntax. Thinking about the problem
further, I can actually just use UUID for the field with a random UUID
default value and let the PRIMARY KEY constraint prevent users from
shoving in their own non-unique values.

[manish yadav]

Hello,

I m creating Trigger in H2 database, But I don't H2 database, Please help to convert below oracle query into H2 database

CREATE TRIGGER IDN_CERTIFICATE_TRIGGER

BEFORE INSERT

 ON IDN_CERTIFICATE

REFERENCING NEW AS NEW

FOR EACH ROW

BEGIN

SELECT IDN_CERTIFICATE_SEQUENCE.nextval INTO :NEW.ID FROM dual;

END;

[Evgenij Ryazanov]

Hello.

You really should use an identity column instead of sequence with such trigger in both Oracle and H2 instead of all these complications with triggers.

But if you need to execute some more complicated actions in the trigger, you need to write an own class with implementation of org.h2.api.Trigger and define a trigger using an H2-specific CREATE TRIGGER command:
https://h2database.com/html/commands.html#create_trigger