There is a code. It completely fulfills its requirement, but a problem arose that it was necessary to keep a history of changes in the database.

Class1.Adapter1 = new MySqlDataAdapter("SELECT * FROM dist ORDER BY `Номер`", new MySqlConnection(Class1.ConString.ToString())); var builder = new MySqlCommandBuilder(Class1.Adapter1); Class1.Adapter1.UpdateCommand = builder.GetUpdateCommand(); Class1.Adapter1.InsertCommand = builder.GetInsertCommand(); Class1.Adapter1.DeleteCommand = builder.GetDeleteCommand(); Class1.Adapter1.Fill(Class1.Dt1); dataGridView1.DataSource = Class1.Dt1; dataGridView1.DoubleBuffered(true);

UPD Base Structure enter image description here

It is desirable to fully all the data before and after editing this or that line in the data grid.

please help with the implementation of this idea

  • If not difficult to describe in more detail the history of any changes you want to keep? Database schemas or data in the database itself? - cheops
  • @cheops Added - Pavel
  • @cheops data in the database itself - Pavel

2 answers 2

I took the liberty to reproduce your table, if the field names do not match, you should correct them with your own. Let there is a source table dist with the following structure.

 CREATE TABLE dist ( number varchar(380) NOT NULL AUTO_INCREMENT, department text NOT NULL, account text NOT NULL, query text NOT NULL, until_at datetime NOT NULL, fio text NOT NULL, comment text NOT NULL, state text NOT NULL, PRIMARY KEY (number) );

In order to store the history, we need the table dist_history in which there will be fields for new and old values ​​(prefixes new_ and old_ ), the primary key id (since we cannot select the number field as such - it will be repeated many times), create_at operations and operation name

 CREATE TABLE dist_history ( id int(11) NOT NULL AUTO_INCREMENT, new_number varchar(380) DEFAULT NULL, new_department text, new_account text, new_query text, new_until_at datetime DEFAULT NULL, new_fio text, new_comment text, new_state text, old_number varchar(380) DEFAULT NULL, old_department text, old_account text, old_query text, old_until_at datetime DEFAULT NULL, old_fio text, old_comment text, old_state text, create_at datetime NOT NULL, operation varchar(20) NOT NULL, PRIMARY KEY (id) );

We will log three operations: create ( INSERT ), update ( UPDATE ) and delete ( DELETE ). The name of the operation will be placed in the dist_history.operation field (in the target database, this field can be normalized and only the foreign key of the operation can be stored in the dist_history table, the names themselves can be put in a separate table).

Fill the log table, as correctly indicated by Marat Batalandabad, the best triggers. We need three triggers for each type of request.

For insert insert operation

 DELIMITER // CREATE TRIGGER insert_dist AFTER INSERT ON dist FOR EACH ROW BEGIN INSERT INTO dist_history VALUES ( NULL, NEW.number, NEW.department, NEW.account, NEW.query, NEW.until_at, NEW.fio, NEW.comment, NEW.state, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), 'insert'); END//

For update operation UPDATE

 DELIMITER // CREATE TRIGGER update_dist AFTER UPDATE ON dist FOR EACH ROW BEGIN INSERT INTO dist_history VALUES ( NULL, NEW.number, NEW.department, NEW.account, NEW.query, NEW.until_at, NEW.fio, NEW.comment, NEW.state, OLD.number, OLD.department, OLD.account, OLD.query, OLD.until_at, OLD.fio, OLD.comment, OLD.state, NOW(), 'update'); END//

For delete operation DELETE

 DELIMITER // CREATE TRIGGER delete_dist AFTER DELETE ON dist FOR EACH ROW BEGIN INSERT INTO dist_history VALUES ( NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, OLD.number, OLD.department, OLD.account, OLD.query, OLD.until_at, OLD.fio, OLD.comment, OLD.state, NOW(), 'update'); END//

Note the DELIMITER // command. Triggers in the body contain a semicolon, so that the mysql query interpreter does not get confused, we change the sign of the end of the query using the DELIMITER command to // . This method works only on the mysql command line, for other clients there may be other ways to indicate the end of the request.

Now, if any attempt to insert a record into the dist table, update it, or delete it, will create a record in the dist_history table about this event.

  • one
    And why in the table "_history" need old values? Is it not the case that the old value for the record is the previous row from the "_history" table? - cache
  • @cache In principle, yes, you can only store new values, focusing on previous entries. This will make the _history table more compact, however, it will require additional calculations if necessary to form a diff of changes. - cheops
  • @cheops Appeared the problem swears on id says the cell can not be empty during the correction - Paul
  • @cheops te he doesn’t fill in id himself - Pavel
  • @cheops figured it out, thank you very much, may God bless you. - Paul

Hang the triggers on the tables and events of the tables to be monitored, write a stored procedure that will save data about the changes in a table with the following fields

TableName, FieldName, OldValue, NewValue, EventDateTime, EventType, DbUser,

etc ...

  • Is it possible in more detail? Since I haven’t come across triggers yet - Paul
  • Long paint. Triggers and stored procedures are the essence of a DBMS. Here is a good example in the topic habrahabr.ru/post/37693 - Marat Batalandabad