Status: Unverified
GOAL:
How to manually replace pre-9.1D insert triggers with 9.1D insert triggers?
GOAL:
How to manually replace pre-9.1D insert triggers with OpenEdge insert triggers?
GOAL:
Insert trigger support for maintaining progress_recid in 9.1D
GOAL:
Insert trigger support for maintaining progress_recid in OpenEdge
GOAL:
Changes in insert triggers in Progress 9.1D MS SQL server dataserver
GOAL:
How does MS SQL server dataserver support ROWID function?
FACT(s) (Environment):
Progress 9.1B
FACT(s) (Environment):
Progress 9.1C
FACT(s) (Environment):
Progress 9.1D
FACT(s) (Environment):
OpenEdge 10.x
FACT(s) (Environment):
MS SQL DataServer
FACT(s) (Environment):
ODBC DataServer
FIX:
Progress migration utility for MS SQL server dataserver creates progress_recid column in SQL server in order to support ROWID/RECID 4GL function. The migration utility also creates an insert trigger to maintain this column.
However, the insert triggers created in versions prior to 9.1D occasionally block resources and create lock contention under heavy load, this is caused by SQL server's handling of the UNIQUE constraint on the index progress_recid.
Progress Version 9.1D has 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.
For example, the insert trigger created in 9.1B and 9.1C looks like following
CREATE TRIGGER _TI_tab1 ON tab1 for insert as
if ( select max(inserted.PROGRESS_RECID) from inserted) is NULL
begin
update tab1 set PROGRESS_RECID = @@identity
where PROGRESS_RECID is NULL
select convert (int, @@identity)
end
The insert trigger created in 9.1D looks like following
create trigger _TI_tab1 ON tab1 for insert as
if ( select PROGRESS_RECID from inserted) is NULL
begin update t set PROGRESS_RECID = i.IDENTITYCOL from tab1 t JOIN inserted i ON t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
select convert (int, @@identity)
end
If you perform a migration using the MS SQL Server Progress DB to MS SQL Server utility and select to have Progress RECID support, Progress recommends that you dump your data, re-create your MS SQL Server database by redoing the migration and reload the 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 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>.<table name>#_#progress_recid.
2.Drop the old insert trigger. The name typically looks like _TI_<tablename>. The syntax is:
SYNTAX
DROP trigger <database>.<owner._TI_<table name>
3.Add the new trigger. The syntax is:
CREATE TRIGGER _TI_<table name> ON <table name> FOR INSERT AS
IF ( SELECT PROGRESS_RECID FROM INSERTED ) IS NULL
BEGIN
UPDATE t SET PROGRESS_RECID = i.IDENTITYCOL
FROM <table name> t JOIN INSERTED i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
SELECT CONVERT (int,@@identity)
END
Sample code for db.owner.Customer table is:
CREATE TRIGGER 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
<table name>(PROGRESS_RECID)
A second index must be created for the progress_recid_ident field. This index must be created as unique and named <table name>#_#progress_recid_ident. The syntax is:
CREATE UNIQUE INDEX <table name>#_#progress_recid_ident_ ON
<table name>(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_)