Select From Table Before Transaction Begin

Question

Select From Table Before Transaction Begin

Viewing 2 posts - 16 through 17 (of 17 total)

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

saif_84 SSC Journeyman Points: 76 More actions · Answer 1

I already have SP Creator that build Template for my tables operation but i think adding Code that handle event log every table without have Generic SP will make my code very long, and hard to maintain, but when build tested SP that handle save all Event data will minimize the bugs.

i plan to save only old Value of the Field that Changed only in NVARCHAR(MAX) Column.

for example:

Table1:

ID Col1 Col2 Col3

--- ----- ------ -----

1 500 600 700

and if we update table to

Table1:

ID Col1 Col2 Col3

--- ----- ------ -----

1 500 660 500

the Audit Table will save:

Audit

AuditID Table UpdatedColumns UpdatedValues

-------- ------ ------------------- ----------------

1 Table1 Col2,Col3 600,700

but in my project i save "TableID and ColumnID" instead of "TableName and UpdatedColumns".

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 2

saif_84 (6/13/2011)
I already have SP Creator that build Template for my tables operation but i think adding Code that handle event log every table without have Generic SP will make my code very long, and hard to maintain, but when build tested SP that handle save all Event data will minimize the bugs.
i plan to save only old Value of the Field that Changed only in NVARCHAR(MAX) Column.
for example:
Table1:
ID Col1 Col2 Col3
--- ----- ------ -----
1 500 600 700
and if we update table to
Table1:
ID Col1 Col2 Col3
--- ----- ------ -----
1 500 660 500
the Audit Table will save:
Audit
AuditID Table UpdatedColumns UpdatedValues
-------- ------ ------------------- ----------------
1 Table1 Col2,Col3 600,700
but in my project i save "TableID and ColumnID" instead of "TableName and UpdatedColumns".

I know some time has gone by on this post but it caught my eye again. You have "TableID" and "ColumnID". It would appear that you seriously missing something there. How will you know which row the data is for?

Also, doing this with CSV's is a nearly vane attempt to save storage space, will make the creation of the audit data horribly slow, and will make the reconstruction of a row at a give point in time both very difficult and horribly slow.

My recommendation is to pick another type of "Slowly Changing Dimension" for your auditing. This one is "Death by SQL". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)