Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## 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 Progress 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 Progress 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:

> Syntax
>     DROP index <database>.<owner>.<tablename>#_#progress_recid.

> 2. Drop the old insert trigger. The name typically looks like `_TI_<``tablename``>`. The syntax is:

> Syntax
>     DROP trigger <database>.<owner._TI_<tablename>

> 3. Add the new trigger. The syntax is:

>     CREATE TRIGGER _TI_<tablename> ON <tablename> FOR INSERT AS
>         IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL
>         BEGIN
>            UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL
>               FROM <tablename> t JOIN INSERTED i ON
>               t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
>               SELECT CONVERT (int,@@identity)
>         END

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

>     CREATE TRIGGER db.owner._TI_customer ON customer FOR INSERT AS
>        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 (int, @@identity)
>        END

> 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_)

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095