I feel like this trigger should work but it does not.

I want the trigger to fill in 2 fields with existing data from the
insert that should appear in the __new table after insert.
Assume Emp_FName, Emp_LName are never null.

(ADS version 8.0.0.8)

Why wouldn't this work?

Jim


CREATE TRIGGER UpdateDisplayNamesinStaffRoster
ON StaffRoster
AFTER
INSERT
BEGIN

declare @displayname string, @displayname2 string, @id integer;
declare StaffRosterNew CURSOR;

// this select statement works fine in Native SQL...

OPEN StaffRosterNew AS
Select IBH_ID,
RTRIM(Emp_Lname)+', '+RTRIM(Emp_FName)+'
'+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+' '
DisplayName2,
RTRIM(Emp_Fname)+'
'+IIF(IFNULL(Rtrim(Emp_MName),'')>'',Left(Emp_MName,1)+'.','')+RTRIM(Emp_LName)
DisplayName
from __New;

TRY

While Fetch StaffRosterNew DO

@id = StaffRosterNew.IBH_ID;
@displayname = StaffRosterNew.DisplayName;
@displayname2 = StaffRosterNew.DisplayName2;

Update StaffRoster Set StaffRoster.DisplayName = @DisplayName,
StaffRoster.DisplayName2 = @DisplayName2 where StaffRoster.IBH_ID = @id;

End;

FINALLY

CLOSE StaffRosterNew;

END TRY;


END
NO MEMOS
PRIORITY 1;