Date of an added row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date of an added row

Author

Topic

asp_noob
Starting Member

3 Posts

Posted - 2008-12-13 : 08:37:15

Hi,

I want to find the date when a new row is added to a table, but I don't have a column where it's stored.
Is it possible to retrieve the date from every row in that table?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 08:42:12

Nope. if you don't have any audit columns like dateadded,datemodified,... its not possible. DO you at least have any supplementing audit tables to capture these details?
if you want implement this anyways, just add a column lastmodified of type datetime using ALTER TABLE ADD... statement and create a default constraint with value GETDATE() on it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 08:42:55

also you may still require trigger for capturing modification dates for update action

asp_noob
Starting Member

3 Posts

Posted - 2008-12-13 : 08:53:31

Thanks already,

any help with the trigger part would be great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 09:40:24

[code]
CREATE TRIGGER UpdateAudit
ON YourTable
AFTER UPDATE
AS
BEGIN
UPDATE t
SET t.lastmodified=GETDATE()
FROM YourTable t
INNER JOIN INSERTED i
ON i.PK=t.PK
END
[/code]

PK is primary key of your table
also make sure nested triggers settings is disabled for server.

asp_noob
Starting Member

3 Posts

Posted - 2008-12-13 : 11:30:57

thanks,
I'll check it out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 11:32:45

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources