how to get old/new guid of a row?

Question

how to get old/new guid of a row?

hatemgamil

Grasshopper

Points: 16
More actions
October 13, 2010 at 2:11 am

#227927

Greetings ,,,
i have read about guid recently and i found that it helps to find the version of a row
now lets say i have a row with columns empID,,EmpName
and a row 1,hatem
if that row is updated then we will have a new version of this row ,,so it will have 2 GUIDs old & New
i want to make a trigger on that table when updated it takes the value of Old and New Guid and insert it in new table called (UpdatedGuidsTable) ,,with columns Old Guid,,New Guid
any suggestion will be highly apperciated
thnx

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you might be mixing information from a different database system...as far as i know, I SQL Server there is not a GUID associated with the old/new rows;

you have the rows available in the INSERTED/DELETED virtual tables in a trigger or as an output clause, and there is some undocumented functions where you can retrieve the partitionID/PageID/RowID for a given row of data, but there is not a GUID;

after the INSERTED row of data is put into the current page of data, saving that information related to the INSERTED/DELETED row identifier would have no value anyway...you cannot go back and select it, as the memory is freed up and available ot hte next process.

Maybe you want to audit the actual data that is changed instead, and keep old vs new values?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hatemgamil Grasshopper Points: 16 More actions · Answer 2

thnx SScert. for ur post

yes i want to audit data and keep new / old values

can u suggest me the best practice to do so

thnx

Lowell SSC Guru Points: 323518 More actions · Answer 3

well everything depends on what you want to do;

there's usually a reason why you want to audit old and new data; typically you create an audit table with nearly the same structure as the table to be audited, and the trigger inserts old and new rows into the audit table.

there are alots of articles and forum posts on the subject, take a look at some of them, and then post what you are really trying to accomplish:

http://www.sqlservercentral.com/search/?q=audit+trigger

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 4

Since you're on SQL 2008 (at least you posted on that forum), you might want to investigate "Change Data Capture".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2