Create a surrogate key in Oracle

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; 

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