October 31, 2010 at 12:57 pm
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.
October 31, 2010 at 1:12 pm
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
Andreas Goldman
October 31, 2010 at 2:33 pm
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.
October 31, 2010 at 2:41 pm
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
October 31, 2010 at 2:59 pm
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
October 31, 2010 at 3:06 pm
Andreas Goldman (10/31/2010)
CREATE TRIGGER Employee_CaptureDelationON 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
October 31, 2010 at 3:11 pm
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
October 31, 2010 at 11:42 pm
Can any one tell me how to convert result set into XML.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply