CODEWITHSUNDEEP
sqlsql-server
Sravanthi
Sravanthi

Reputation: 45

How to write query for created User, Created date, Modified User, Modified date for a table

I need help in knowing how to automatically make the table load the values for Created user , created date, modified user and modified date,

once any data goes into the table , we should have the Created user and created date column value to be fixed but the modified user and modified date should change

Upvotes: 0

Views: 4242

Answers (3)

Jan
Jan

Reputation: 16042

For createdby and createtime you can use columns default values:

CreatedAt DATETIME DEFAULT GETDATE(),
CreatedBy VARCHAR(100) DEFAULT USER

For UpdatedAt you have to create a trigger which sets the values:

CREATE TRIGGER [t_Table_upd] ON YourTable AFTER UPDATE AS 
set nocount on 
UPDATE YourTable
set 
    YourTable.[Modified] = getdate(), 
    YourTable.[ModifiedBy] = USER, 
    YourTable.[Version] = YourTable.[Version] + 1 
FROM
    DELETED d
WHERE
    YourTable.ID = d.ID

Upvotes: 0

Yves M.
Yves M.

Reputation: 3318

You can set the default value of a column to GetDate() for the datetime columns and to SYSTEM_USER for the current user. This is good for the Creation of the records.

For the modification columns you need to add a trigger.

Also worth reading...

How to use system_user in audit trigger but still use connection pooling?

Upvotes: 1

Spredzy
Spredzy

Reputation: 5164

Admiting your table structure looks like this -dont forget the DEFAULT GETDATE()- :

YourTable
---------
CreatedUser
CreatedDate  DEFAULT GETDATE()  
ModifierUser   
ModifierDate

On the insertion you trigger a function that will update ModifiedUser and ModifiedDate to GETDATE()

Upvotes: 0

Related Questions