Hi all.

I'm using Kettle 3.1 and i have a simple insert/update transformation with a table input, three lookups and an insert/update step. The insert/update step uses a unique key field to insert or update the data. The database is Oracle and i have a sequence and a before insert trigger like this one:

DROP TRIGGER SCHEMA.TRG_XXX_BI;

CREATE OR REPLACE TRIGGER SCHEMA.TRG_XXX_BI
BEFORE INSERT
ON SCHEMA.TABLE1 REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
IF :NEW.ID_FIELD IS NULL THEN
SELECT TABLE1_SEQ.NEXTVAL INTO :NEW.ID_FIELD FROM DUAL;
END IF;
END SCHEMA.TRG_XXX_BI;
/

When i first execute the transformation i get an ORA-00001: Unique constraint table_name.column_name violated error. No rows are inserted but i the sequence has a new value (currvalue + 1) so the sequence.nextval has been executed. If i execute the transformation again then everything runs perfect.

I understand sequences are external to transactions but i don't know if Kettle running insert in parallel could be the problem.

Using a trigger is a must because other applications insert values in the table.

Anyone could help me with this little problem please?

Thanks a lot.