Modifying your insert trigger

Progress Version 9.1D modified the insert trigger that it places on a table in the foreign database when the PROGRESS_RECID support is selected. The modification reduces the level of contention between multiple users performing inserts simultaneously. The reduction in contention can produce performance improvement.

If your database was created using the OpenEdge DB to MS SQL Server utility and PROGRESS_RECID support is enabled, Progress Software Corporation recommends that you re-create your database by dumping your data, executing the OpenEdge DB to MS SQL Server migration again, and then reloading your data. If this is not possible, then you can manually change your existing MS SQL Server database by doing the following on each table that has been defined with PROGRESS_RECID support from your MS SQL Server Migration:

  1. Drop the unique index created over the PROGRESS_RECID field. There should be one index for each table created during the initial migration. The syntax is: 
    DROP index database.owner.tablename#_#progress_recid.
  2. Drop the old insert trigger. The name typically looks like _TI_tablename. The syntax is: 
    DROP trigger database.owner._TI_tablename
  3. Add the new trigger. The syntax is: 
    create trigger _TI_tablename ON tablename for insert as
 RAISERROR ('PSC-init',0,1) 
 SET XACT_ABORT ON 
 SET LOCK_TIMEOUT -1 
    if  ( select PROGRESS_RECID from inserted) is NULL
    begin
        update t set PROGRESS_RECID = i.IDENTITYCOL 
         from customer t  JOIN inserted i ON 
         t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
        select convert (bigint, @@identity)
    end
 SET XACT_ABORT OFF 
 RAISERROR ('PSC-end',0,1) 
 RAISERROR ('PSC-end',0,1)

    Sample code for the db.owner.Customer table is:

    SET XACT_ABORT ON 
 SET LOCK_TIMEOUT -1 
    if  ( select PROGRESS_RECID from inserted) is NULL
    begin
        update t set PROGRESS_RECID = i.IDENTITYCOL 
         from customer t  JOIN inserted i ON 
         t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
        select convert (bigint, @@identity)
    end
 SET XACT_ABORT OFF 
 RAISERROR ('PSC-end',0,1)
  4. Create a new nonunique index for each table naming the new index the same name as was dropped earlier in Step 1. Make sure it is not unique. The syntax is: 
    CREATE INDEX database.owner.table#_#progress_recid ON 
tablename(PROGRESS_RECID)

    A second index must be created for the PROGRESS_RECID_IDENT field. This index must be created as unique and named tablename#_#progress_recid_ident. The syntax is:

    CREATE UNIQUE INDEX tablename#_#progress_recid_ident_ ON 
tablename(PROGRESS_RECID_IDENT_

    An example of the two indices that must be created for the customer table is as follows:

    CREATE INDEX customer#_#progress_recid ON CUSTOMER (PROGRESS_RECID)
CREATE UNIQUE INDEX customer#_#progress_recid_ident_ ON CUSTOMER(PROGRESS_RECID_IDENT_)