LastUpdatedDate and LastUpdatedUser

Question

Post reply

LastUpdatedDate and LastUpdatedUser

Leon Yak

SSC Journeyman

Points: 77
More actions
February 25, 2004 at 2:06 pm

#86083

Hi,
Have a table with LastUpdatedDate and LastUpdatedUser columns at the end.
If row been updated these columns in that row must populated by Getdate() and current_user values.
Users can update table directly from Access linked tables.
Is the way to "stamp" (trigger) update of the LastUpdatedDate and LastUpdatedUser fields when row is updated .
Can it be done without any changes on Access side.
Thank you.
Leon

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

wz700

SSCertifiable

Points: 7133

February 25, 2004 at 2:55 pm

#495688

Use timestamp in pre-trigger is a way.

noeld SSC Guru Points: 96590 More actions · Answer 2

Yep

TRIGGER TR_U_TblName ON TblName

FOR UPDATE

IF @@ROWCOUNT = 0

RETURN

UPDATE TblName

Set LastUpdatedDate = Getdate(), LastUpdatedUser = Current_user

FROM Inserted I

WHERE TblName.Pk = I.PK

Now, there is a catch! this works if nested triggers are disabled

* Noel

Leon Yak SSC Journeyman Points: 77 More actions · Answer 3

Thank you very much.

Going to try it tomorrow morning.

Have a good night.

Leon

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 4

one thing to add, if you will have multiple rows updated at once you will need to change this line:

WHERE TblName.Pk = I.PK

to WHERE TblName.Pk in (select PK from inserted)

HTH

------------

Ray Higdon MCSE, MCDBA, CCNA

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

In addition to the above posts I would also place a DEFAULT of GETDATE() on the LastUpdateDate and a DEFAULT of CURRENT_USER on the LastUpdateUser columns.

Another thing is, you write that users can update the table directly via Access. Do you mean they open the table and hack in the grid? If so, it might be better to create a view and exclude those 'internals columns' from the view.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 6

I don't think that is correct

You can check that the Trigger perform on ALL AFFECTED ROWS with

tblName.pk = I.pk

(That is Joining the Inserted Table with the Destination so the "In" is not necessary!)

* Noel

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 7

NoelD, You're right, read it too fast.

------------

Ray Higdon MCSE, MCDBA, CCNA

Leon Yak SSC Journeyman Points: 77 More actions · Answer 8

Thank you.

It trigger working fine.

It works even nested triggers set enabled!?

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 9

I said Nested Triggers I should have said Recursive Triggers which by default is disabled

but even if you enable it it still works because is acting on the same set of records until it reaches level 32 of nesting and you don't want that right?

* Noel

Leon Yak SSC Journeyman Points: 77 More actions · Answer 10

You bet.

".. until it reaches level 32 ..."

It sounds like playing computer game: "SQL server advanture"

It has 32 levels, each is more difficalt.

( Now I trying to pass level 22 - "performance and tuning". Interested, but can not pass ))

Thanks.

Leon