I have a trigger on each table in a database which updates a datetime column (lastupdatedon) and a varchar field (enteredby) after update on each individual table. The problem is, when one table is updated at the same instant as another table (by different users), the same varchar data (SYSTEM_USER) is put in both tables, even though the users are different.
Here is an example of the trigger:
CREATE TRIGGER EventUpdate ON jrowley.Event
AFTER UPDATE AS
UPDATE jrowley.Event SET LastChangedOn = getdate(),EnteredBy = SYSTEM_USER WHERE EventID in (Select EventID from deleted)
Sorry, I should have added more info about the application. It is a stand alone application deployed to individual desktops and written in DataEase netPlus ( basicly a wrapped c++ RAD tool). The connection to the DB is through the 'SQL server client network utility' with a tcp/ip connection. Is there anything server side to set the transaction granularity?