December 30, 2004 at 8:32 am
On each table in my database of have triggers that record who inserted the record and when. I also have one that record who made the change and when. The insert trigger is working fine. I am having trouble figuring out the update trigger. I just want to update the record changed with the username and timestamp but can't figure out how to only update the record effected. For example, I have 4 extra fields for userentered, dateentered, userlastchanged, datelastchanged, the userentered and dateentered i can update those records using the @@IDDENTITY function to make sure I have the correct record but I can't figure out how to update the record when it is being changed. Also, is there a better way of performing the insert trigger instead of telling SQL Server to update the record just added because then the update trigger fires when it really wasn't updated yet(I guess I could elimante those columns from the update trigger)
Thanks.
Steve
December 30, 2004 at 9:01 am
There is a special table during inserts/updates/deletes available in triggers. During an update/insert you can perform something like the following:
declare @RecordKey int
select @RecordKey = [KeyFieldName] from inserted
update [tablename] set userlastchanged = user(), datelastchanged = getdate() where [KeyFieldName] = @RecordKey
But with that said, why are you using triggers instead of processing this within the stored procedure? This is extra overhead when you should be able to add it to the insert/update statements.
One other note is to be careful with the @@Identity value. That is the last identity created and if you have triggers creating other records you may not always get the identity you expected. Use scope_identity() to return the identity of the record you just inserted within the scope of the session.
Hope this helps.
If the phone doesn't ring...It's me.
December 30, 2004 at 9:10 am
Thank you I will try your suggestions. The tables are being touched by a couple different applications in different ways and I want a central point where I can tell who is inserting and updating the record and when if there is a problem that I need to track down. I don't want to rely on the programmers to put this in their apps and stored procedures. I want it this way for my own piece of mind so I know the data will be correct.
December 30, 2004 at 9:18 am
Fair enough. Also, in case you want to track who deletes using triggers a delete trigger allows access to the table deleted.
select @recordkey = [keyfieldname] from deleted would give you the key value of the record deleted.
Also, as a suggestion if you aren't already I but the user's name and date in the modifiedby field even on the intial create so I can always refer to the same field to show a last modified date and I don't have to worry if there is a value stored or not.
If the phone doesn't ring...It's me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply