Using triggers to enable PROGRESS_RECID support of the ROWID function

If you want to use the ROWID function with a data source, you must select an index to associate with this function. Adding a new integer column named PROGRESS_RECID and defining it as a single-component index is the preferred method. This method can only be applied to a table that does not currently have an identity column defined. If your table already has an identity column, you can support ROWID by selecting an existing index. See Defining theROWID for instructions on selecting an existing index. The following procedure describes how to create the PROGRESS_RECID column to support ROWID. To use the ROWID function with a MS SQL Server database, you must make changes to your data source table.

Note: Starting in OpenEdge Release 10.1B, the default PROGRESS_RECID recognizes an INT64 value for a MS SQL Server DataServer. The unique key value in the PROGRESS_RECID will be derived by default from a 64-bit value. (For prior releases, the value was derived, by default, from the 32-bit INTEGER.)

To modify a table to support the ROWID function:

1. Perform these tasks:

a. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null, as shown:

alter table table
add PROGRESS_RECID integer null

b. Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the integer data type, as shown:

alter table table
add PROGRESS_RECID_IDENT_
integer identity

2. Create a trigger to maintain the PROGRESS_RECID column:

create trigger _TI_table ON table 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

3. Create a non-unique index for the PROGRESS_RECID and a unique index for the PROGRESS_RECID_IDENT, respectively:

create index table#_#progress_recid on table (PROGRESS_RECID)

create unique index table#_#progress_recid_ident_on table (PROGRESS_RECID_IDENT_)

4. Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component, as shown:

create index table#_#index on table (column, PROGRESS_RECID)

5. If you have already created your schema holder, delete and recreate it.