Kaidanov Tzvi Gregory Technical Blog

The environment :
Working with Oracle 10g.
At the mapping files of the FNH on the ID column define the sequence to work with.
This is a relevant sequence used by the relevant trigger used for the current table.
The db is a legacy db - using triggers to increment the records on the insert.
The problem :
When I save the record I'm getting back the record number. Well I'm getting the expected number +1 , every time . The record is being inserted and the record number jumps two times instead of one.
The solution:
In the trigger you must enter check to see if the new value is null , and only then increment the trigger . If it's not null it means it was incremented by FNH already and there is no need to activate the increment in the trigger.


CREATE OR REPLACE TRIGGER DOAR_DEV.CUSTOMER_TRG1
BEFORE INSERT
ON DOAR_DEV.CUSTOMER REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.CST_RECID IS NULL
)
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;

SELECT Customer_SEQ.NEXTVAL INTO tmpVar FROM dual;
:NEW.Cst_RecID := tmpVar;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;
/