Status: Unverified
GOAL:
How to convert from existing trigger solution to computed columns to support RECID in MS SQL server dataserver?
GOAL:
MS SQL server dataserver: Converting RECID-trigger mechanism to RECID computed column mechanism
GOAL:
Procedure to convert a table that supports the ROWID function from one that is trigger-enabled to the more reliable computed column-enabled
FACT(s) (Environment):
Windows
OpenEdge 10.2A
OpenEdge 10.2B
FIX:
OE MS SQL server dataserver manual has following section on how to convert a trigger based RECID support to computed column based RECID support. ====================================
Converting RECID-trigger mechanism to RECID computed column mechanism
Use the following procedure to convert a table that supports the ROWID function from one that is trigger-enabled to the more reliable computed column-enabled.
Note: Computed column enablement is available only through MS SQL Server 2005 and later.
Note: Prior to performing the following steps, ensure you have backed up the tables with which you will be working.
To adopt a table to support the ROWID function using computed column from trigger mechanism
1. Drop the trigger on INSERT for PROGRESS_RECID column as:DROP TRIGGER _TI_<tbl-name>
GO
2. Store the current identity value on the table as follows:
select ident_current('<schemaname.tbl-name>') as old_identity_value
go
3. Drop the indexes on PROGRESS_RECID, PROGRESS_REID_IDENT_ columns as:
drop index <table>#_#progress_recid on <table)
drop index <table>#_#progress_recid_ident_on <table>
4. Drop the other indexes which include PROGRESS_RECID column as:
drop index <table>#_#<index> on <table>
5. Rename the PROGRESS_RECID column to PROGRESS_RECID_bkp as:
EXEC sp_rename '<tbl-name.PROGRESS_RECID', 'PROGRESS_RECID_BKP' ,'COLUMN'
6.Drop the PROGRESS_RECID_IDENT_ column as:
alter table <tbl-name> drop column PROGRESS_RECID_IDENT_
7. Add new columns for computed column mechanism as:
ALTER TABLE <tbl-name>
ADD PROGRESS_RECID AS
CASE WHEN PROGRESS_RECID_ALT_ is null
THEN PROGRESS_RECID_IDENT_
ELSE PROGRESS_RECID_ALT_
END PERSISTED not null,
PROGRESS_RECID_IDENT_ bigint identity,
PROGRESS_RECID_ALT_ bigint null default NULL,
CONSTRAINT <tbl-name>#_#progress_recid UNIQUE(PROGRESS_RECID)
GO
8. Update the PROGRESS_RECID_ALT_ column with PROGRESS_RECID_BKP as:
update <tbl-name> set PROGRESS_RECID_ALT_ = PROGRESS_RECID_BKP
go
9. Reseed the altered table with previously stored identity value (in step-2)as:
DBCC CHECKIDENT ('<tbl-name>', RESEED, <old-identity-value>)
go
10. Drop the PROGRESS_RECID_BKP column as:
alter table <tbl-name> drop column PROGRESS_RECID_BKP
go
11. Re-create the dropped indexes during Step 4 as:
create index <table>#_#<index> on <table> (column, PROGRESS_RECID)
go
12. If you have already created your schema holder, delete and recreate it.
13. Perform an adjust schema on the schema holder generated if the basis for your server database is an OpenEdge equivalent database from which you previously migrated.