I have a table in a SQL Server database with some columns along with
CreatedBy (varchar)
CreatedOn (datetime)
UpdatedBy(varchar)
UpdatedOn(datetime)
as standard columns. The CreatedOn and CreatedBy values are automatically populated as and when there is a new row added to this table (using default value approach).
Question: is there any out-of-the-box solution in SQL Server which can pupulate UpdatedOn and UpdatedBy values as and when row updates?
Apart from adding a trigger as mentioned by Alex K et all., you could also add a RowVersion column. This data type was previously called TimeStamp, but unfortunately the name is misleading.
This will give you the UpdatedOn smalldatetime timestamp, but not the UpdatedBy information.
If you want to convert the RowVersion colunm into a Date & Time format, you need to do a bit of work;
(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:
Create Table dbo.UpdateTimeStamp(
OldRowVersion binary(8),
CreatedDate DateTime constraint DF_UpdateTimeStamp_TimeStamp Default getdate(),
NewRowVersion ROWVERSION CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
)
Insert the first row manually
Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate) VALUES (0x0000000000000000, '2000-01-01')
Which inserts one row in the table every one minute.
Make step 1 run this code:
Insert into dbo.UpdateTimeStamp(OldRowVersion) SELECT TOP (1) NewRowVersion FROM dbo.UpdateTimeStamp ORDER BY NewRowVersion DESC
Set the schedule to run every 1 minute.
Join the UpdateTimeStamp table to your table with a between join claus like this:
SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate FROM dim.MyTable MT
LEFT JOIN dbo.UpdateTimeStamp uts ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate
HIH.
You can add a trigger like the one below. This trigger works if multiple rows are updated are the same time. Please notes it assumes you have added an identity column to your source table.
CREATE TRIGGER [dbo].UpdateTrans ON [dbo].YourTable
FOR UPDATE
AS
SET NOCOUNT ON
UPDATE yt
SET UpdatedBy = SUSER_SNAME() ,
UpdatedDate = GETDATE()
FROM DELETED d
INNER JOIN YourTable yt ON yt.YourTableId = d.YourTableId
As is mentioned by Alex K. in the comments on the OP. This can be handled by a trigger object on the table.
Something like:
CREATE TRIGGER updateTable
ON table
AFTER UPDATE
AS
[YOUR UPDATE CODE HERE]
GO
You can use triggers to do the same, below is the sample code (not tested, just a pseudo)
CREATE TRIGGER [dbo].UpdateTrans
ON [dbo].YourTable
FOR UPDATE
AS
UPDATE YourTable
SET UpdatedBy= SYSTEM_USER
, UpdatedDate = GETDATE()
WHERE YourColumn IN (SELECT DISTINCT YourColumn FROM Inserted)
But it is not wise to use triggers on warehouse database, here is a article about reasons to avoid Triggers
For better performance, I would recommend you to update UpdatedBy and UpdatedDate at the same time when you are updating any row.
You can read more about Triggers here.
updatedbyvalue can be determined).