Modifying tables to support ROWID function for Sybase
If you want to use the ROWID function with an ODBC data source, you must make certain changes to your data-source table. The following example uses Sybase to illustrate the necessary changes.
To modify tables to support the ROWID function, using a Sybase database:
1. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null. For example:
alter table table add PROGRESS_RECID integer null
2. Add a column with identity characteristics named PROGRESS_RECID_IDENT_-. The new column must have the numeric data type. For example:
alter table table add PROGRESS_RECID_IDENT_ numeric(10,0) identity
3. Create a trigger to maintain the PROGRESS_RECID column, as shown:
create trigger _TI_table on table for insert as
begin
if (select max(inserted.PROGRESS_RECID) from inserted) is NULL
begin
update table set PROGRESS_RECID = @@identify
where PROGRESS_RECID is null
select convert (int, @@identity)
end
end
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, update it to reflect your changes to the data-source table.
If your data source supports stored procedures, you may modify the steps above to implement the ROWID function using a similar syntax.
