SQL Server TimeStamp - 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

Old Forums

CLOSED - General SQL Server

SQL Server TimeStamp

Author

Topic

DrkMaster
Starting Member

11 Posts

Posted - 2004-02-02 : 17:00:32

I am designing an application and one of the requirements for this application is that each table should have a column for the time the record was last updated and the ID of the person who made the last update. I looked through the books online and found the timestmap data type but this doesn't satisfy the requirements cause it only provides a version number of the record and not information about the time.

This is my first adventure into SQL programming but I am not a novice in programming nor database design. Any help you provide will be much appreciated.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-02 : 17:39:17

Have a look at GETDATE() in BOL

DrkMaster
Starting Member

11 Posts

Posted - 2004-02-02 : 18:12:37

what about the user ID ?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-02 : 18:25:35

USER_ID or USER_NAME or USER or SUSER_SNAME or SUSER_SID...

Do you know what BOL is?

DavidM

"SQL-3 is an abomination.."

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-02 : 18:26:12

There are a few: SYSTEM_USER, USER_NAME(), etc.. - Books Online is very helpful for finding these types of functions.

If the application is web-based you might consider supplying the userid from the session information for inserts/updates rather than the connection.

DrkMaster
Starting Member

11 Posts

Posted - 2004-02-02 : 18:52:25

Yes, I know what BOL is. Books Online.

I am still having trouble configuring the trigger to fire.

Here's what I have it doing.

CREATE TRIGGER [Application Table Insert] ON [dbo].[Application]
FOR INSERT
AS

DECLARE @num_affected int, @AppID int
SET @num_affected = @@ROWCOUNT
SET @cur_date = SELECT GETDATE()
IF (@num_affected =0)
RETURN
UPDATE [Application]
SET [RowDate] = @cur_date
WHERE AppID = @AppID
Go

But I keep getting a syntax error.

DrkMaster
Starting Member

11 Posts

Posted - 2004-02-02 : 19:01:48

Never mind friends.

i figured it out.

Damn Variable Declarations lol

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-02-02 : 19:03:39

Don't do this in a trigger!!!!

Declare these as DEFAULT's in your table definition!

DavidM

"SQL-3 is an abomination.."

DrkMaster
Starting Member

11 Posts

Posted - 2004-05-04 : 19:42:52

quote:
Originally posted by byrmol

Don't do this in a trigger!!!!

Declare these as DEFAULT's in your table definition!

DavidM

"SQL-3 is an abomination.."

why not?

I have implemented that trigger successfully and it works like a charm.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 19:53:20

Just because it works, doesn't mean that's what you should do. As David mentioned, you should be using table defaults instead of a trigger for this. So RowDate would have a default value of GETDATE(). No need for the trigger. Just don't put a value in RowDate, and SQL Server will provide one for you.

Tara

DrkMaster
Starting Member

11 Posts

Posted - 2004-05-05 : 16:25:36

but that's not what I want it to do.

I want the row date to change when someone makes an update to the row.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-05-06 : 11:24:05

so why do it in the trigger??
in your update sql do GetDate for the RowDate field.
Update dbo.Application set RowDate = GetDate(), other updates

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources