BEFORE INSERT in Derby DB

Question

At the moment my application uses an Oracle db and I am really happy about it. My intention is to implement the same app with Derby (just to make it more portable). With Oracle I use a trigger with 'BEFORE INSERT' and a sequence and it works ok. I would like to implement the same with Derby, but it seams impossible. I also found this article: Sequences and Triggers in Derby DB Is the content of the article correct ? Why Derby does not have such a common feature implemented ?

Thanks Alb

This is what I used in oracle:

CREATE TABLE  "SITES" 
("SITE_ID" NUMBER NOT NULL ENABLE, 
"SITE_NAME" VARCHAR2(100) NOT NULL ENABLE, 
"SITE_LINK" VARCHAR2(500) NOT NULL ENABLE, 
"SITE_DESC" VARCHAR2(100) NOT NULL ENABLE, 
"SITE_DATA_IN" DATE, 
 CONSTRAINT "SITE_PK" PRIMARY KEY ("SITE_ID") ENABLE
)

CREATE OR REPLACE TRIGGER  "SITES_TRIGGER" 
before insert on SITES
for each row
begin
select SITES_SEQ.nextval into :new.SITE_ID from dual;
end;
/
ALTER TRIGGER  "SITES_TRIGGER" ENABLE
/

CREATE SEQUENCE   "SITES_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 301 CACHE 20 NOORDER  NOCYCLE

What's the sequence for? What's the information used for? Do you use JDBC, JPA oder something else to deal with your data? — Brian, Jul 18 '15 at 19:22
Also if you are calling derby from java code this is not a question related to java, but only to derby. Remove the tag java please. — Davide Lorenzo MARINO, Jul 18 '15 at 19:22
@Constantin, no does not have to be derby, but because I am developing on Netbeans, it seamed to me the easier. — gocan76, Jul 18 '15 at 19:54
Ok, using a sequence for a primary key. So, how do you persist data? Via JDBC or JPA or something?! — Brian, Jul 18 '15 at 20:06
I use hibernate to populate the table, apart for the primary key (incremented by the sql code above) — gocan76, Jul 18 '15 at 20:09
http://database-management.softwareinsider.com/compare/6-16/Apache-Derby-vs-H2 — Constantin, Jul 19 '15 at 11:57
@gocan76 You should use Hibernate for the primary key column too. This way you would not need triggers at all - or at least not for the primary key. This would make your application more portable, just as you desire it to be. — Brian, Jul 19 '15 at 16:02
I think I will opt for the hibernate solution, Constantin pointed out an interesting option also — gocan76, Jul 19 '15 at 17:03
In the end, after some testing, to make things simple, I followed this solution, and it works (in my app) perfectly, thanks to all Alb http://stackoverflow.com/questions/3308329/create-autoincrement-key-in-java-db-using-netbeans-ide — gocan76, Jul 24 '15 at 13:56

score 0 · Answer 1 · answered Jul 18 '15 at 19:35

0

According to the documentation of v10.11, Derby does support before-insert-triggers.

answered Jul 18 '15 at 19:35

Brian

872
8
16

ok Brian, I tried myself, but with no luck, then when I came across that article I decided to ask here, just not to waste other time. – gocan76 Jul 18 '15 at 20:07
@gocan76: what happened when you tried. Did you get an error message? What line of code returned the error? – Bryan Pendleton Jul 19 '15 at 15:59
1

@BryanPendleton, ...... CREATE TRIGGER COUNTER_TRG AFTER INSERT ON SITES REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL UPDATE SITES O SET O.SITE_ID = NEXT VALUE FOR COUNTER_SEQ; .......This is the error: Error code 30000, SQL state 42X01: Syntax error: Encountered "SET" at line 7, column 1. – gocan76 Jul 19 '15 at 17:23

BEFORE INSERT in Derby DB

1 Answers1