January 30, 2008 at 11:21 am
We have migrated a Sybase db to SS2K5. Most every table have two column attributes: "UpdatedDate" and "UpdatedBy" which, in Sybase, recorded current datetime (getdate()) and Current_user each time the row is updated.
I need to find a way to emulate this functionality in SS2K5. I suppose update triggers can set up for each table; but I am hoping someone has come up with a more elegant solution... such as a user defined datetype or "calculated column" or ????
Any ideas?
Thanks in advance,
Joel
Takauma
January 30, 2008 at 11:46 am
I think you are going to have to use triggers for this one. You can get the current user by using SYSTEM_USER and the date/time using GETDATE(). The only other way to update the table would be to update the columns via your DML (insert/update/delete) statements. Meaning that whenever you perform an update on a table, you update the auditing columns too.
UPDATE MyTable
SET COL1 = Value,
UpdatedDate = GETDATE(),
UpdatedBy = SYSTEM_USER
January 30, 2008 at 2:18 pm
It's a mystery to me how SQL Server is not set up to handle what in our view is a basic necessity (namely: "when" was the record last updated, and "by who") The trigger method sticks out as the obvious solution, but it's so cumbersome when many tables are involved.
Somehow Sybase (SQL Anywhere actually) seems to think so, and has managed to make the functionality available to it's users.
Joel
Takauma
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply