I would like to create an history module to my application..
I need some advice to use a duplicate database with who, when and where fields added to that new duplicate table, or create a table with a character field saying which fields have been affected and others fields with who, when and where.
What is the best way to keep an historical table of other table?
Any example or article that I can read.
Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000. 3 2631
Hi Rafael,
If this is a question especific to SQL I think you will get a better answer in a SQL Server NG. I have never had the need of this but I think that using a trigger will be the key, not sure if you will be able to get the who/when part though.
You can set a trigger FOR INSERT/UPDATE and compare the new fields to the original ones and act accordingly.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"Rafael Tejera" <ra**********@h otmail.comwrote in message news:%2******** *******@TK2MSFT NGP02.phx.gbl.. .
I would like to create an history module to my application..
I need some advice to use a duplicate database with who, when and where fields added to that new duplicate table, or create a table with a character field saying which fields have been affected and others fields with who, when and where.
What is the best way to keep an historical table of other table?
Any example or article that I can read.
Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.
If the historical information will be referred to on a daily basis, it is
probably best to set up your history tables within the same database. It is
also unlikely that you will need ALL of the fields from your main tables in
your history tables. Here is a simplification of how I do it: In one of my
tables, I have all of the details of a particular item and another table
contains the current cost information (why cost is separate is topic for
another discussion and not relevant to this one). In my history table, I
save the foreign key of the item table, but instead of an FK from the cost
table, I save the cost info at the time the entry was made along with an
entry date and an edit date.
"Rafael Tejera" <ra**********@h otmail.comwrote in message
news:%2******** *******@TK2MSFT NGP02.phx.gbl.. .
I would like to create an history module to my application..
I need some advice to use a duplicate database with who, when and where
fields added to that new duplicate table, or create a table with a character
field saying which fields have been affected and others fields with who,
when and where.
What is the best way to keep an historical table of other table?
Any example or article that I can read.
Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000.
I built some classes to do this, and it wasn't easy. But now I can have an
application that performs database operations that can be undone and redone.
Let me see if I can sketch it out for you:
I created 2 tables in a database: ChangeLog and ChageRows:
ChangeLog
ChangeID (identity)
UserName (nvarchar)
ChangeTime (DateTime)
TableName (nvarchar)
ChangeType (int)
RolledBack (bool)
ChangeRows
RowID (identity)
ChangeID (int - foreign key to changeLog)
RowIndex (int)
RowValues (nvarchar)
The ChangeType stores an enumerated value from an enumeration I created:
DataChangedEven tType
Uninitialized = -1,
RowUpdated = 0,
RowAdded = 1,
RowDeleted = 2,
RowsUpdated = 3,
RowsAdded = 4,
RowsDeleted = 5,
OperationCancel led = 6
Next, I created several interfaces and generic classes:
IHistoryEntry - Interface for HistoryList member classes.
IHistoryList - Interface used by History Client
to access HistoryList members
IHistoryAction : IList - Provides non-Generic access
to HistoryActionLi st as IList
HistoryList<T : IEnumerable<T>, IEnumerable,
ICollection<T>, ICollection, IList<T>, IList, IHistoryList
where T: IHistoryEntry - Represents a History List of any data type.
HistoryActionLi st<T: List<T>, IHistoryAction
where T : IHistoryEntry - Undo or Redo List in a HistoryList.
Now, this is a bit more complex than you perhaps need, because I designed it
to be able to work with strongly-typed instances of any type of data. I also
created a ToolStrip Control with Undo and Redo buttons on it.
Basically, this is how it works. You create your custom HistoryEntry class,
using the IHistoryList interface, and then you wire up event handlers to
actions in your interface. Each database action is of one of the types in
the enumeration. When, for example, a row in a DataTable (this is what I
used this for initially) changes, I use the ItemArray of the row to get an
array of all the row values, which I store as a pipe-delimited string in the
RowValues column of the ChangeRows table. This records the current state of
the row. I then make the change, and record the change in the database. The
RowIndex is the current index of the changed row in the database. When
undoing, each action is undone in reverse order, ensuring the state
reverting back in its original form. Deleting a row works the same way. And
so on. Redoing works the same way. The state of the row is stored in the
History list prior to re-doing the last action, and then the action is
re-done. And of course, if you undo to a certain point, and then make a
change, the ReDo list is cleared.
The hardest part (all of it is hard, of course) is building the UI to work
with it. There are 2 History Lists, the Undo and the Redo History List, and
when you undo, the Undone item is moved from the Undo to the Redo History
List, and so on.
That's it in a nutshell. Any more and you'd have to pay me! ;-)
--
HTH,
Kevin Spencer
Microsoft MVP
Digital Carpenter
A man, a plan, a canal,
a palindrome that has gone to s**t.
"Rafael Tejera" <ra**********@h otmail.comwrote in message
news:%2******** *******@TK2MSFT NGP02.phx.gbl.. .
I would like to create an history module to my application..
I need some advice to use a duplicate database with who, when and where
fields added to that new duplicate table, or create a table with a character
field saying which fields have been affected and others fields with who,
when and where.
What is the best way to keep an historical table of other table?
Any example or article that I can read.
Sincerely,
Rafael
I'm new using C#.NET. I have C# 2003 with MS SQL Server 2000. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: John Dunlop |
last post by:
leegold2 wrote:
> I know php is server side scripting, but say a page is rendered by php
> were A = 1. Now, I want when the user clicks the back button on the
> browser and sees the page again...I want A to show as 2. Is this
> possible?
It is possible, depending on the configuration of the
browser and the expiration time of the resource, but not
what should happen. Even if the resource has expired, the
|
by: WertmanTheMad |
last post by:
Ok here goes, another odd SQL Question ....as always..
How do I get ...
The value of a paramater passed to say a Trigger
OR The SQL Itself
Like this , say I have a Trigger set on delete,
Now this trigger will add a row to another table (a history table) but
it need to be passed a paramater, throught the sql itself as it cannot
|
by: craig |
last post by:
I have a question about database design that I think some of the more
experienced developers might be able to answer...
Early on in the development of the DB for a project we are working on, the
decision was made to use physical deletion of records from the DB rather
than logical deletion. This seemed to be working just fine until now.
We have added entity history functionality to our application (per the
requirements) in which we use...
|
by: Phil Sherman |
last post by:
UDB LUW (Windows) 8.1 FP9a
I issued a PRUNE HISTORY 20050819 AND DELETE and was surprised to see
that the archived log files were not physically deleted. The history
record of the 20050818 backup was correctly deleted from the history
file. I then issued a PRUNE LOGFILE PRIOR TO Snnnnnnn.LOG command which
did delete the logs. Both commands completed with no errors. LOGRETAIN
parameter is set to RECOVERY.
What does the 'AND DELETE'...
|
by: John Baker |
last post by:
Hi:
I have a table with many months of data on it. I am attempting to create a delete
transaction, based on matching a date in the table with a date in another table. The match
works perfectly in a Select Query, but when I change the query to a delete, it asks me to
specify which table I wish to delete from. I just cannot figure out how to do this, and
would appreciate a hint! I am using the Access Query interface NOT the SQL interface.
...
| |
by: David |
last post by:
A user logs into a web site. He is then redirected to a web page of his choosing - based on menu options - for example: "WeeklyReport.aspx" is a page the user is currently viewing
If the user session times out, I redirect the user to a "logoff.aspx" web page that now informs the user he must relogin to the web site
Question: Is there anyway to clear the browser's history list (prior to Response.Redirect("logoff.aspx") - to ensure the user...
|
by: Jeff |
last post by:
Hello-
Ive never used a vector or vectors in C++ so I have a question for you
all.
I know I can dynamically create the size I need upfront, but is it
possible to
create them on the fly dynamically? That is, for a 2 dim array, I want
to create
the first element and then push_back the columns after that:
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: Nyh |
last post by:
I tried to explain the best I can but it is very difficult to do. Here it goes.
I have three history tables. Teacher, student and school. School and teacher, and school and student are linked together.
I need to make a query that makes in one query a temporary table that list all the students that had a teacher change at some given date in the past.
For example:
If I give the query the following date 01/01/2007 it needs to give me all...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |