Get the FieldName and specified record Value in Comma separated

Question

Get the FieldName and specified record Value in Comma separated

bpradeep

SSC Veteran

Points: 249
More actions
October 31, 2010 at 12:57 pm

#231061

My requirement is if any record in any table deleted from via application then I need to cature that record in some where for future reference. I just given example one table strucure like this but idea is I Plan to create one
For Example
Table name : Employee
ID, Name,salary,createdate,modifieddate
1,xxx,123,10102010,03242010
Expected Output is:
Field1
ID,1|Name,xxx|salary,123|createdate,10102010|modifieddate,03242010
new table called "deletedrecords" with Field Name called "SerialNo","TableName","DeletedRecord","CreatedDate","CreatedBy"
Whenever end user click the delete button then immediatly it will give "Table Name" and "Key ID" of the table as input to my SP's . Then I have to Take that record and store it into above table and then delete the record
in the actual table.
So I belive there are lot of query export are here defenitly any one can help me for the same.
1. I have a Table Name and a Primary Key value of the Table
2. Using the Table Name, I can get the Column Name from sys.syscolumns table.
3. Using Key ID and Table name , I can get the a record from that table
4. I don't Know how to contact fieldname and value as I mentioned above?
Please any one help me for the same.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Andreas Goldman Old Hand Points: 313 More actions · Answer 1

Hi,

For this purpose you'd be better off implementing a trigger that fires on the deleted event of the table. Within the trigger you could forward the information you'd like to your log table including the deleted record, user, etc.

Even if a trigger has some performance cost, in this particular case it would save you a lot of work.

Andreas Goldman

bpradeep SSC Veteran Points: 249 More actions · Answer 2

I can use the trigger to get the data using the below query but how can i store it like below

select * from deleted

For Example:

Table name : Employee

ID, Name,salary,createdate,modifieddate

1,xxx,123,10102010,03242010

If I delete ID 1 then I will get the whole record "select * from deleted" using this query

but I need to get the output like below

Expected Output is:

Field1

ID,1|Name,xxx|salary,123|createdate,10102010|modifieddate,03242010

since I have a archive table strucure is like this

archive table called "deletedrecords" with FieldName called

"SerialNo","TableName","DeletedRecord","CreatedDate","CreatedBy"

1,Employee,ID,1|Name,xxx|salary,123|createdate,10102010|modifieddate,03242010,getdate(),0

Please help me for the same.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you want to stick the entire row into a single column, may I rather suggest XML? There is built in support for converting resultsets to XML and vis versa.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andreas Goldman Old Hand Points: 313 More actions · Answer 4

Trigger would look something like this, I omitted the datetime fields since I don't know how you'd want them presented

CREATE TRIGGER Employee_CaptureDelation

ON Employee

AFTER DELETE

AS

DECLARE @DeletedRecord VARCHAR(4000)

SELECT @DeletedRecord = 'ID,' + CAST(ID AS VARCHAR(20)) + '|Name,' + Name + '|salary,' + CAST(salary AS VARCHAR(20))

FROM DELETED

INSERT DeletedRecords (TableName, DeletedRecord, CreatedDate, CreatedBy)

SELECT 'Employee', @DeletedRecord, GETDATE(), SYSTEM_USER

RETURN

Andreas Goldman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Andreas Goldman (10/31/2010)
CREATE TRIGGER Employee_CaptureDelation
ON Employee
AFTER DELETE
AS
DECLARE @DeletedRecord VARCHAR(4000)
SELECT @DeletedRecord = 'ID,' + CAST(ID AS VARCHAR(20)) + '|Name,' + Name + '|salary,' + CAST(salary AS VARCHAR(20))
FROM DELETED
INSERT DeletedRecords (TableName, DeletedRecord, CreatedDate, CreatedBy)
SELECT 'Employee', @DeletedRecord, GETDATE(), SYSTEM_USER

And what happens if more than one row is deleted in a single statement?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andreas Goldman Old Hand Points: 313 More actions · Answer 6

It would only store the "last" deleted record and ignore the others. I followed the example from the beginning stating that one record at at time would be deleted from the application level. However your absolutely right, the trigger would have to be adjusted to handle a set-deletion.

Thanks for the remark.

Andreas Goldman

bpradeep SSC Veteran Points: 249 More actions · Answer 7

Can any one tell me how to convert result set into XML.