Get the FieldName and specified record Value in Co

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get the FieldName and specified record Value in Co

Author

Topic

bpradeep
Starting Member

1 Post

Posted - 2010-10-31 : 16:25:19

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 help me for the same

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:38:35

As it is being deleted fro an application and it's probably not easy to amend that then an audit trail trigger would probably be simplest.
The deletedrecords table should be the same structure as the original table with the addition of a z_date, z_user column for the audit

create trigger au_MyTable on MyTable for delete
as
insert into deletedrecords
select *, getdate(), system_user
from deleted
go

You can also audit trail updates as well.
Have a look at
http://www.nigelrivett.net/#Triggers

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-31 : 16:42:35

Also asked here: http://www.sqlservercentral.com/Forums/Topic1013610-391-1.aspx

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:57:37

Should we stop using this thread then?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-31 : 17:34:09

Up to you (and the mods here). I posted that so that people could see what's already been said and not repeat comments

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 17:47:02

I'm ( and I suspect others) aren't going to do that so I guess this thread should be abandonned in favour of the other one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources