|
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 
|
|
|
|
|
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 know that it's possible, the question was whether it would be a good idea 
..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
|
|
|
|
|
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. 
|
|
|
|
|
Thanks all for Reply.
Can u give me a small example on how to do that?
I exactly wanna this.
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
thanks!
but i wanna to have that .how to retrieve data from SQLServer from past transactions
|
|
|
|
|
how i make the entity relation of "car selling and car purchase "
plz help me about this question
lsajidali@yahoo.com
|
|
|
|
|
You draw an arrow from one entity to the other. Done.
Did you mean in code? What database-system? Here's one in SQL92;
CREATE TABLE Car
(
cId INTEGER,
Description CHARACTER VARYING(50),
PRIMARY KEY (cId)
)
CREATE TABLE CarPurchase
(
pId INTEGER,
boughtCar INTEGER,
PRIMARY KEY (pId),
FOREIGN KEY (boughtCar) REFERENCES Car
)
This syntax should work on most systems.
Bastard Programmer from Hell
|
|
|
|
|
Hi all,
in my database table , i have one field (say field1) which contains the value like 1,2,3,10,11,20, a, b,c , 30, 40. I want to write a query which sore the result on the base of field1 and output should be like
1
2
3
10
11
20
30
40
a
b
c
whether it is possible through query or i need to adjust in code ?
|
|
|
|
|
Yhat should be possible using a query. Add a computed field that indicates whether it's a number or a range of characters, and put that as the first column to sort on. Or check out the collations if you're on Sql Server - might be as easy as changing the setting and have the server order everything in the correct way for your locale.
Bastard Programmer from Hell
|
|
|
|
|
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names.
It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
|
|
|
|
|
None I know about, but you can select a range from the sheet like: SELECT * FROM [Sheet1$A4:F1243] Which could solve your problem.
But you would need to know, or find out, how many rows there are before doing the select.
|
|
|
|
|
But could it then use the first row as column headers?
|
|
|
|
|
|
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget).
Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g. ['sheet 1$'] and I have been unable to get that to work with a range.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.
Just for testing I renamed the sheet to remove the SPACE and it works. 
Do you have a work around for this?
|
|
|
|
|
I never knew about the space problem, thanks for the feedback.
I can't think of any workaround at the moment, but I'm having a look at it tomorrow as I'm having a potential bug in one of my programs.
|
|
|
|
|
Hello,
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50.
The table may not have all the Sequence number. For example following is the data in my table..
NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table.
I thought of using the following query to generate a sequence and then join, but does not help
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
|
|
|
|
|
I guess you're using Oracle, since you select from 'dual'.
You may:
1. Declare a temporary table:
CREATE GLOBAL TEMPORARY TABLE temp_number
( number_column NUMBER( 10, 0 )
)
ON COMMIT DELETE ROWS;
2. Fill it with numbers from 1 to 50.
INSERT INTO temp_number
SELECT rownum FROM ALL_OBJECTS
WHERE rownum BETWEEN 1 AND 50
You may prefer a for loop.
3. Outer-join it to your table.
SELECT number_column, NAME
FROM TEMP_NUMBER
LEFT OUTER JOIN ABC
ON TEMP_NUMBER.number_column = ABC.SEQUENCE
ORDER BY number_column
Hope this helps,
Pablo.
Pablo.
"Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
