how to get old/new guid of a row? 1

[hatemgamil]

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

 

[bborissov]

Where are GUIDs in that table?
I don't see any. You have int, varchar() fields.
Could you post some example data and what you want as a final result.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[TheBugSlayer]

Assuming you are using a SQL Server 2005 or above, you could use the an UPDATE with an OUTPUT clause like so:

CREATE TABLE [dbo].[TestTable](
	[ColGUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TestTable_ColGUID]  DEFAULT (newid()),
	[Created] [datetime] NOT NULL CONSTRAINT [DF_TestTable_Created]  DEFAULT (getdate()),
	[Updated] [datetime] NOT NULL CONSTRAINT [DF_TestTable_Updated]  DEFAULT (getdate()),
	[CreatedBy] [varchar](50)  NOT NULL
)

INSERT INTO TestTable (CreatedBy) VALUES('John')
INSERT INTO TestTable (CreatedBy) VALUES('Juan')
INSERT INTO TestTable (CreatedBy) VALUES('Jean')

UPDATE TestTable SET CreatedBy = CreatedBy + ' Doe', ColGUID = NEWID()
OUTPUT INSERTED.ColGUID,
       DELETED.ColGUID,
       INSERTED.CreatedBy,
       DELETED.CreatedBy
--INTO YourOutputTableName
WHERE CreatedBy = 'John'

Create the YourOutputTableName table and uncomment the INTO statement to redirect your output to a table. No triggers needed.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[hatemgamil]

thnx bugslayer for ur answer ,,ur query works so fine