Database Discussion Boards

Re: How to get delete/Update row + SQL

Eddy Vluggen19-May-12 0:42

Eddy Vluggen

19-May-12 0:42

Still working on that audit? Smile | :)

Stop wasting time, if your customer really needs the old values of the record, then you'd be best of with doing only inserts; somebody wants to update a record? Mark the current one as outdated (datetime) and insert a new one.

Yes, that'll make your database huge, but logging every action would result in yet even more data, since you're not only storing the new values (ignoring the old) - but also the command.

You best rethink your requirements here.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201119-May-12 4:38

jojoba2011

19-May-12 4:38

thanks for your attention!

but its the end of story.
is it possible to get that info via <pre lang="c#">sp_trace_setevent</pre>.
i know that it is in ldf file.

plz give the help to close the question.

Re: How to get delete/Update row + SQL

Eddy Vluggen19-May-12 5:05

Eddy Vluggen

19-May-12 5:05

jojoba2011 wrote:
is it possible to get that info via

Not as far as I can see.

MSDN[^] mentions:

<layer>Text value dependent on the event class captured in the trace. However, if you are tracing a parameterized query, the variables will not be displayed with data values in the TextData column.

jojoba2011 wrote:
i know that it is in ldf file

How did you know?

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201120-May-12 2:42

jojoba2011

20-May-12 2:42

Thanks for reply!
i have got the row update old value when i go to table and manually change the value "via sp_trace_setevent (Transact-SQL)" but cannot get the old value when using SP for update.

Plz help to get the old value when runing SP

Re: How to get delete/Update row + SQL

Eddy Vluggen20-May-12 3:03

Eddy Vluggen

20-May-12 3:03

A trace shows the commands written to the server; not the values that the server holds. A trace will not show the current values. Executing a SP with some values might even require different permissions than reading those values.

As said, this is the wrong approach. If your customers need the old and the new values, then you should not update or delete, just insert.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201120-May-12 17:38

jojoba2011

20-May-12 17:38

I get Old Value and New Value u can see the red color part:OldName,OldFamily--->UpdatedName,UpdatedFamily
via this trace :

SQL

/****************************************************/
/* 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 datetime

SET @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 share

exec @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, @on

exec 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_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on

exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on



exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on



exec sp_trace_setevent @TraceID, 28, 1, @on
exec sp_trace_setevent @TraceID, 28, 6, @on
exec sp_trace_setevent @TraceID, 28, 9, @on
exec sp_trace_setevent @TraceID, 28, 10, @on
exec sp_trace_setevent @TraceID, 28, 11, @on
exec sp_trace_setevent @TraceID, 28, 12, @on
exec sp_trace_setevent @TraceID, 28, 13, @on
exec sp_trace_setevent @TraceID, 28, 14, @on
exec sp_trace_setevent @TraceID, 28, 16, @on
exec sp_trace_setevent @TraceID, 28, 17, @on
exec sp_trace_setevent @TraceID, 28, 18, @on



-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft Dynamics NAV client'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

and the result is :<the red="" color="" item="" is="" the="" one="" that="" i="" wanna="">i get this result when i manually change the item in SQL Management by hand .but i wanna to get this result when running SP.

[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK_tblNames',@_msparam_3=N'dbo',@_msparam_4=N'tblNames'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)exec sp_executesql N'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)',N'@Name nvarchar(11),@Family nvarchar(13),@Param1 int,@Param2 nvarchar(7),@Param3 nvarchar(9),@Param4 int',@Name=N'UpdatedName',@Family=N'UpdatedFamily',@Param1=2,@Param2=N'OldName',@Param3=N'OldFamily',@Param4=1select col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idselect col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idSET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'SELECT
db_name() AS [Database_Name],

Re: How to get delete/Update row + SQL

Eddy Vluggen21-May-12 4:30

Eddy Vluggen

21-May-12 4:30

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 Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201121-May-12 20:11

jojoba2011

21-May-12 20:11

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.

Re: How to get delete/Update row + SQL

Eddy Vluggen22-May-12 0:52

Eddy Vluggen

22-May-12 0:52

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 Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201122-May-12 3:22

jojoba2011

22-May-12 3:22

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.???

Re: How to get delete/Update row + SQL

Eddy Vluggen22-May-12 8:50

Eddy Vluggen

22-May-12 8:50

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 Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201122-May-12 23:24

jojoba2011

22-May-12 23:24

Hi,Thanks
i wanna to have something like "ApexSQL Log" but not that much big.

Re: How to get delete/Update row + SQL

Eddy Vluggen23-May-12 0:16

Eddy Vluggen

23-May-12 0:16

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 Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201123-May-12 3:37

jojoba2011

23-May-12 3:37

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.

Re: How to get delete/Update row + SQL

Eddy Vluggen23-May-12 8:23

Eddy Vluggen

23-May-12 8:23

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 Suspicious | :suss:

Re: How to get delete/Update row + SQL

Mycroft Holmes20-May-12 12:55

Mycroft Holmes

20-May-12 12:55

As Eddy has said, you need to address your requirements first! Attempting to retain every change, both from and to via the .ldf is not going to work. Eddies suggestion that you make copies of records instead of updating is valid (I have used this under duress), marking deleted records as Disabled is also a recognised solution.

Go back to your requirements and reassess them in the light of what you have learned over the last few weeks trying to use the wrong design to achieve a desired result.

Never underestimate the power of human stupidity
RAH

Re: How to get delete/Update row + SQL

Corporal Agarn21-May-12 0:49

Corporal Agarn

21-May-12 0:49

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.

Re: How to get delete/Update row + SQL

Eddy Vluggen21-May-12 3:45

Eddy Vluggen

21-May-12 3:45

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 Suspicious | :suss:

Re: How to get delete/Update row + SQL

Corporal Agarn21-May-12 3:51

Corporal Agarn

21-May-12 3:51

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.

Re: How to get delete/Update row + SQL

Eddy Vluggen21-May-12 4:27

Eddy Vluggen

21-May-12 4:27

I know that it's possible, the question was whether it would be a good idea Smile | :)

..but agreed, if they can't change the application-code, then one would have to make a change in the database-server.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

JohnPayton21-May-12 13:04

JohnPayton

21-May-12 13:04

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). Smile | :)

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 | :cool:

Re: How to get delete/Update row + SQL

jojoba201121-May-12 20:08

jojoba2011

21-May-12 20:08

Thanks all for Reply.
Can u give me a small example on how to do that?
I exactly wanna this.

Re: How to get delete/Update row + SQL

JohnPayton21-May-12 20:25

JohnPayton

21-May-12 20:25

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

Re: How to get delete/Update row + SQL

jojoba201121-May-12 20:34

jojoba2011

21-May-12 20:34

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.

Re: How to get delete/Update row + SQL

JohnPayton21-May-12 20:51

JohnPayton

21-May-12 20:51

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?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.