Kbase 21394: Dataserver: Error 137 on Trigger to Add 4GL Extended Support
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/12/2008 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.x
MS SQL DataServer
ODBC DataServer
SYMPTOM(s):
Getting error when trying to create an inset trigger to support PROGRESS_RECID column within SQL Server
Error 137: Must declare the variable '@@identify'
The insert trigger is created referencing
Progress DataServer for Microsoft SQL Server
5.9.2 Modifying Tables to Support the ROWID Function
The trigger syntax documented is as following
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
CAUSE:
Documentation bug. The Progress DataServer for Microsoft SQL Server Guide is incorrect when it lists the syntax to create the insert trigger in the associated chapter.
Within the steps to create an insert trigger to support the PROGRESS_RECID column, #3 references;
update table set PROGRESS_RECID = @@identify
Also note that within this same area, #2 is incorrect as well. The new numeric column to support identity characteristics is PROGRESS_RECID_IDENT_, not PROGRESS_RECID_IDENT. Notice the underscore at the end of the line.
The keyword within SQL Server is IDENTITY, not identify.
FIX:
Using following syntax to create trigger
create trigger _TI_table ON table for insert as
if ( select PROGRESS_RECID from inserted) is NULL
begin
update t set PROGRESS_RECID = i.IDENTITYCOL
from table t JOIN inserted i ON
t.PROGRESS_RECID_IDENT_ = i.PROGRESS_RECID_IDENT_
select convert (int, @@identity)
end