To create a surrogate key in Oracle is not really trivial. The issue, we address works as follows. Assume we have a table with 3 records an four attributes. The first attribute is a name, the second is a surrogate key. Other attributes refer to a user and a datetime stamp. This surrogate key should contain a unique number that allows us to locate the record uniquely. Let us assume the table looks like:
name, SURKEY, CreatedDate, CreatedUser tom 1 ine 2 paula
In this case, we would like to have a unique value next to “paula”.
This can be accomplished by a so-called trigger that creates such a unique value every time a record is inserted. To ensure that a unique value is retrieved, we use a sequence. The trigger has next code:
create or replace TRIGGER SCOTT.DWH_SAI_ID BEFORE INSERT ON SCOTT.TABLE_SURKEY REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE tmpVar NUMBER; BEGIN tmpVar := 0; SELECT DWH_TECH_ID.NEXTVAL INTO tmpVar FROM dual; :NEW.SURKEY := tmpVar; :NEW.CreatedDate := SYSDATE; :NEW.CreatedUser := USER; END DWH_SAI_ID; create or replace trigger scott.PK_PK before insert on "SCOTT"."SURKEY" for each row begin if inserting then if :NEW."ID" is null then select scott.PKSEQ.nextval into :NEW."SURKEY" from dual; end if; end if; end;
This trigger takes a new (unique) value from a sequence DWH_TECH_ID and stores this value in attribute SURKEY. Along with the surrogate key, the user that inserted the record and the datetime is stored in the record.
The result is:
name, SURKEY, CreatedDate, CreatedUser tom 1 ine 2 paula 3 2010/2/2 tom