The MS Sqlserver trigger shown below is currently the method we use to update the RECNUM field of the CLIENT file with an incrementing value.
Any suggestions on a better way of doing it, that uses less resources would be greatfully accepted.
In particular, I am wondering if the Client field RECNUM can be directly updated by directly referencing the field rather than having to do an update statement. Most other database products we have used can directly update a field in a trigger.
Ie: In Oracle you can do this; :NEW.RECNUM = 1
CREATE TRIGGER Trg_Client ON CLIENT FOR INSERT AS
SELECT @NEW_RECNUM = ISNULL(MAX(RECNUM), 0) + 1 FROM CLIENT
UPDATE CLIENT SET RECNUM = @NEW_RECNUM WHERE RECNUM = 0"