How do I update a key field on insert for SQL Server (Siebel table S_CAMP_CON)

Question

I need help on writing a trigger in SQL Server to be used in Siebel:

• The system field ROW_ID has to be unique (key)
• When the field INSERT_CD and CAMP_WAVE_ID is null then ROW_ID must be generated (see below).
• If not, leave ROW_ID as is.
• ROW_ID is a key field of varchar(15).

The following statement generates the perfect key/row id:

select substring(replace (CAST (newid() as varchar(36)),'-',''),1,15)

I need help on writing a SQL Server 2005 trigger to generate this key.

Answer 1

I would strongly suggest not to modify ROW_ID. Use a different column, maybe add your own extension column, but don't modify (or try to set yourself) ROW_ID. Leave it at the value that Siebel puts in there.

You must not modify any of the system columns (Type "System", check in Tools, in Table > Columns)

Answer 2

Here is one way we did it. We setup OBIEE to generate a ROW_ID that is unique for the current load and hence why the WHERE clause can get the record to be updated.

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'S_CAMP_CON_Direct_Load_ROW_ID' AND type = 'TR')
   DROP TRIGGER S_CAMP_CON_Direct_Load_ROW_ID
GO
CREATE TRIGGER S_CAMP_CON_Direct_Load_ROW_ID
ON S_CAMP_CON FOR INSERT
AS

UPDATE S_CAMP_CON
    SET ROW_ID = (select substring(replace (CAST (newid() as varchar(36)),'-',''),1,15))
   WHERE S_CAMP_CON.ROW_ID IN
   (SELECT ROW_ID FROM inserted WHERE INSERT_CD = 'Direct Load')

But we are concerned by the uniqueness of ROW_ID since we are using a substring.