How to get delete/Update row + SQL
-
As stated you are going at it from the wrong direction. Other solutions include: - using the OUTPUT Clause[^] - using trigger[^] both can track the data. If the code cannot be changed the trigger would be the way to go.
What does the trigger-solution provide, besides extra trouble in maintainability? We're not synchronizing on every record, and he merely needs a duplicate. Changing the command being executed would be a tad more efficient than adding triggers here to copy each and every reveived value to another table (with the same structure).
Bastard Programmer from Hell :suss:
-
What does the trigger-solution provide, besides extra trouble in maintainability? We're not synchronizing on every record, and he merely needs a duplicate. Changing the command being executed would be a tad more efficient than adding triggers here to copy each and every reveived value to another table (with the same structure).
Bastard Programmer from Hell :suss:
Since you can put logic in a trigger to only pull what you want this could create the table of updated columns. I did say that trigger might be better if the main code could not be changed.
-
Since you can put logic in a trigger to only pull what you want this could create the table of updated columns. I did say that trigger might be better if the main code could not be changed.
-
I get Old Value and New Value u can see the red color
part:OldName,OldFamily--->UpdatedName,UpdatedFamilyvia this trace :/****************************************************/
/* Created by: SQL Profiler */
/* Date: 16/04/2009 12:29:20 */
/****************************************************/-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500
declare @OutputFileName nvarchar(200)
declare @EndTime datetimeSET @OutputFileName = 'C:\STrace' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')SET @EndTime = DATEADD(mi,30,getdate())
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network shareexec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTime
if (@rc != 0) goto error-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @onexec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_seteventIf you run it manually, you'll see the text as sent to the server. A trace is meant to "trace" what commands are being executed. AFAIK, it wouldn't even remotely interested in the "current" values. Repeating the question will not change the answer.
Bastard Programmer from Hell :suss:
-
Hi, How can i get the full record that is delete/Update via
exec sp_trace_setevent @TraceIdI mean: I have table with this columnsId int ,Name nvarchar(max) ,Family nvarchar(max)with the values:1,jojoba,alin 2,babk,babyi... now i when i update like this:UPDATE tblName
SET Name='okki', Family='koki' where Id=2now I wanna this in my trace:
2,babk,babyi Deleted 2,ooki,koki InsertedMay I offer a possible solution, if all of the database actions are being "called" by a front end application then another method to maintain an audit trail is to simply append and database change details to a ascii log file (simple text file). :) For example where I work if anybody makes a change to any database field the action is recorded also to the log file. Then if we need to access who made the changes and what changes were made on any particular date in time, even years ago it's a simple matter to check the log file. When it get's too big archive it and start a new one. :cool:
-
May I offer a possible solution, if all of the database actions are being "called" by a front end application then another method to maintain an audit trail is to simply append and database change details to a ascii log file (simple text file). :) For example where I work if anybody makes a change to any database field the action is recorded also to the log file. Then if we need to access who made the changes and what changes were made on any particular date in time, even years ago it's a simple matter to check the log file. When it get's too big archive it and start a new one. :cool:
Thanks all for Reply. Can u give me a small example on how to do that? I exactly wanna this.
-
If you run it manually, you'll see the text as sent to the server. A trace is meant to "trace" what commands are being executed. AFAIK, it wouldn't even remotely interested in the "current" values. Repeating the question will not change the answer.
Bastard Programmer from Hell :suss:
thanks for you reply for both C# and Database pages. But isnt it possible to have when running update SP. Its very important to do that . if possible then its end of story. Q2)is it possible to have the OLD trace if the user delete the .trc file from SQL.
-
Thanks all for Reply. Can u give me a small example on how to do that? I exactly wanna this.
Sure, this is a snippet from a routine in VB6, but it would be easy to convert it to VB.Net. In this instance I'm recording the method of payment and amounts from each transaction. If the log.txt file does not exist then create it, otherwise append to it. You will notice this particular log file is called LogReturns.txt and it's in the root directory of the program executable. Hope this is what you are looking for.
On Error Resume Next
Dim fso As New Scripting.FileSystemObject
Dim mFile As String
Dim txtfile As Object
mFile = "/LogReturns.txt"
If fso.FileExists(App.Path & mFile) Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.OpenTextFile(App.Path & "/LogReturns.txt", ForAppending)
Else
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile(App.Path & "/LogReturns.txt", True)
End If
Dim mVar As Integer
txtfile.WriteLine (" ")
txtfile.WriteLine ("Receipt Number : " & recReceipt.Fields("ReceiptNum"))
txtfile.WriteLine ("Member Number : " & recReceipt.Fields("MembNum"))
For mVar = 0 To intMoviesReturnCount - 1
txtfile.WriteLine ("Movie Number(s): " & arrMoviesReturned(mVar))
Next mVar
txtfile.WriteLine ("Rec Date & Time: " & recReceipt.Fields("ReceiptDateTime"))
txtfile.WriteLine ("Amount Payable : " & Format(recReceipt.Fields("Amount"), "$0.00"))
' v1.0.159 : 26-Jul-2006 : JPG : Added CashNett value to report
txtfile.WriteLine ("Cash Nett : " & Format(recReceipt.Fields("CashNett"), "$0.00"))
txtfile.WriteLine ("Cash Tendered : " & Format(recReceipt.Fields("CashTendered"), "$0.00"))
txtfile.WriteLine ("Eftpos Selected: " & Format(recReceipt.Fields("Eftpos"), "$0.00"))
txtfile.WriteLine ("Cheque Provided: " & Format(recReceipt.Fields("Cheque"), "$0.00"))
txtfile.WriteLine ("Credit Card : " & Format(recReceipt.Fields("CreditCard"), "$0.00"))
txtfile.WriteLine ("GiftCard Used : " & Format(recReceipt.Fields("GiftCardUsed"), "$0.00"))
txtfile.WriteLine ("Discount : " & Format(recReceipt.Fields("Discount"), "$0.00"))
txtfile.WriteLine ("Transfer : " & Format(recReceipt.Fields("DebitMemberBalance"), "$0.00"))txtfile.WriteLine (" ") txtfile.WriteLine ("\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*") txtfile.Close Set fso = Nothing -
Sure, this is a snippet from a routine in VB6, but it would be easy to convert it to VB.Net. In this instance I'm recording the method of payment and amounts from each transaction. If the log.txt file does not exist then create it, otherwise append to it. You will notice this particular log file is called LogReturns.txt and it's in the root directory of the program executable. Hope this is what you are looking for.
On Error Resume Next
Dim fso As New Scripting.FileSystemObject
Dim mFile As String
Dim txtfile As Object
mFile = "/LogReturns.txt"
If fso.FileExists(App.Path & mFile) Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.OpenTextFile(App.Path & "/LogReturns.txt", ForAppending)
Else
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile(App.Path & "/LogReturns.txt", True)
End If
Dim mVar As Integer
txtfile.WriteLine (" ")
txtfile.WriteLine ("Receipt Number : " & recReceipt.Fields("ReceiptNum"))
txtfile.WriteLine ("Member Number : " & recReceipt.Fields("MembNum"))
For mVar = 0 To intMoviesReturnCount - 1
txtfile.WriteLine ("Movie Number(s): " & arrMoviesReturned(mVar))
Next mVar
txtfile.WriteLine ("Rec Date & Time: " & recReceipt.Fields("ReceiptDateTime"))
txtfile.WriteLine ("Amount Payable : " & Format(recReceipt.Fields("Amount"), "$0.00"))
' v1.0.159 : 26-Jul-2006 : JPG : Added CashNett value to report
txtfile.WriteLine ("Cash Nett : " & Format(recReceipt.Fields("CashNett"), "$0.00"))
txtfile.WriteLine ("Cash Tendered : " & Format(recReceipt.Fields("CashTendered"), "$0.00"))
txtfile.WriteLine ("Eftpos Selected: " & Format(recReceipt.Fields("Eftpos"), "$0.00"))
txtfile.WriteLine ("Cheque Provided: " & Format(recReceipt.Fields("Cheque"), "$0.00"))
txtfile.WriteLine ("Credit Card : " & Format(recReceipt.Fields("CreditCard"), "$0.00"))
txtfile.WriteLine ("GiftCard Used : " & Format(recReceipt.Fields("GiftCardUsed"), "$0.00"))
txtfile.WriteLine ("Discount : " & Format(recReceipt.Fields("Discount"), "$0.00"))
txtfile.WriteLine ("Transfer : " & Format(recReceipt.Fields("DebitMemberBalance"), "$0.00"))txtfile.WriteLine (" ") txtfile.WriteLine ("\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*") txtfile.Close Set fso = Nothingthanks for ans. But i wanna to get the info from my SQL database . i wanna to have every thing the user changed,Updated,Inserted,Deleted. So i can trace that.
-
thanks for ans. But i wanna to get the info from my SQL database . i wanna to have every thing the user changed,Updated,Inserted,Deleted. So i can trace that.
Hi jojoba2011, You get the values you need to save from the front end application, what I mean to say is the values you are using in the database insert or update query are also used for the text report audit trail. In the case of the original values that are being replaced, they are stored as a variable when the update/insert page is first populated. Do you understand what I'm saying?
-
Hi jojoba2011, You get the values you need to save from the front end application, what I mean to say is the values you are using in the database insert or update query are also used for the text report audit trail. In the case of the original values that are being replaced, they are stored as a variable when the update/insert page is first populated. Do you understand what I'm saying?
first of all thanks for your attention! Sorry i cant understand! I think that u add values to txt when inserting them to Database. Correct? But i wanna to get it from DB.even if the text file deleted i can get info back.
-
thanks for you reply for both C# and Database pages. But isnt it possible to have when running update SP. Its very important to do that . if possible then its end of story. Q2)is it possible to have the OLD trace if the user delete the .trc file from SQL.
jojoba2011 wrote:
But isnt it possible to have when running update SP.
Its very important to do that .
if possible then its end of story.No, not possible.
jojoba2011 wrote:
is it possible to have the OLD trace if the user delete the .trc file from SQL.
Yes, as I already suggested when I answered the post; embed a trace-file as an embedded resource. That way it will be compiled "into" your executable. ..but no, unless you're the administrator and have more rights on my machine than I do, I'll not only change the trace-file, I'll even make sure that there's fake data entered for your amusement.
Bastard Programmer from Hell :suss:
-
jojoba2011 wrote:
But isnt it possible to have when running update SP.
Its very important to do that .
if possible then its end of story.No, not possible.
jojoba2011 wrote:
is it possible to have the OLD trace if the user delete the .trc file from SQL.
Yes, as I already suggested when I answered the post; embed a trace-file as an embedded resource. That way it will be compiled "into" your executable. ..but no, unless you're the administrator and have more rights on my machine than I do, I'll not only change the trace-file, I'll even make sure that there's fake data entered for your amusement.
Bastard Programmer from Hell :suss:
thanks, your really active one. can you give me an example that how can i
embed a trace-file as an embedded resource in C#in my app.??? -
thanks, your really active one. can you give me an example that how can i
embed a trace-file as an embedded resource in C#in my app.??? -
first of all thanks for your attention! Sorry i cant understand! I think that u add values to txt when inserting them to Database. Correct? But i wanna to get it from DB.even if the text file deleted i can get info back.
Yes that's right, you keep the values you are inserting/updating/deleting then call a routine to record the changes in the ascii text audit file. :) Re Getting data from the DB, I'm sorry but I don't know how to retrieve data from SQLServer from past transactions, I'm sure it's possible from the transaction log. But in my circumstances it's been easier to just store the old values and new values when they happen to variables then send to the ascii log file.
-
Yes that's right, you keep the values you are inserting/updating/deleting then call a routine to record the changes in the ascii text audit file. :) Re Getting data from the DB, I'm sorry but I don't know how to retrieve data from SQLServer from past transactions, I'm sure it's possible from the transaction log. But in my circumstances it's been easier to just store the old values and new values when they happen to variables then send to the ascii log file.
thanks! but i wanna to have that .
how to retrieve data from SQLServer from past transactions -
I could, but it's your work. There are enough examples on the internet, and they don't take more than a few lines of code. Good luck :)
Bastard Programmer from Hell :suss:
Hi,Thanks i wanna to have something like "ApexSQL Log" but not that much big.
-
Hi,Thanks i wanna to have something like "ApexSQL Log" but not that much big.
I don't know the product, but I'm guessing it's a tool that saves both the old and the new values. You already have a table - you can easily insert new values without ever touching the old ones. It won't be "as big" as any external audit, since any external data-file would have more overhead. If your client needs to keep all historic values, then you simply don't update or delete.
Bastard Programmer from Hell :suss:
-
I don't know the product, but I'm guessing it's a tool that saves both the old and the new values. You already have a table - you can easily insert new values without ever touching the old ones. It won't be "as big" as any external audit, since any external data-file would have more overhead. If your client needs to keep all historic values, then you simply don't update or delete.
Bastard Programmer from Hell :suss:
hi dear. this is exact the tool that i wanna.this read .ldf(log) file of SQL and convert it to human Language.it exactly show the old values and new values both .i wanna to do this my self without buying it and i dont wanna that much info. See can u give me something like that > I know that u can do that cause ur expert in SQL.
-
hi dear. this is exact the tool that i wanna.this read .ldf(log) file of SQL and convert it to human Language.it exactly show the old values and new values both .i wanna to do this my self without buying it and i dont wanna that much info. See can u give me something like that > I know that u can do that cause ur expert in SQL.
jojoba2011 wrote:
hi dear
I'd like to suggest a more neutral greeting.
jojoba2011 wrote:
See can u give me something like that
No, because the structure of the ldf-file isn't public. You'd still need to talk to Microsoft if you want to read the ldf-file. Or try and lull the company into handing you some help.
jojoba2011 wrote:
I know that u can do that cause ur expert in SQL.
Nope; I can only give some options -
- You can do a real audit in Sql Server, not Sql Express
- You can trace, but that doesn't show the "old" values
- You can insert without update. Sounds wrong, but is often the best solution, since your table is the most optimized structure to hold that type of data.
- You could create triggers to copy both old and new values to some other table, which would be the complex version of the point above, without too much added value.
- You can change the datalayer of the code talking to your server, and have it log there.
- Talk to Microsoft
Bastard Programmer from Hell :suss: