A typical method is to update or add to each data field in the primary table, and to delete all related records from the table before inserting. That is, every time you save, the data from the table is executed again to delete and insert the process. This is a good way to use a database transaction and there will be no deletion and no insertion resulting in inconsistent data.
OK, the principle is this, then to the actual operation, how do we operate? It is possible that this data inconsistency may exist because there is no guarantee that an accident occurred between two data statement operations by completing the requirements as above through multiple database operations in a program. Then we merge into a stored procedure to complete it, complex parameters and receive after processing, making this stored procedure difficult to change. Below I will be practical application to say this process. Application scenario, in student management, add a student record that corresponds to adding a student's corresponding course. That is, each update and the addition of student records, corresponding to its curriculum changes.
The process, then, conforms to the logic above.
General parameter transfer, no longer detailed in this, focus on explaining how to deal with the delivery of the subject information. Suppose a Studentid,subjectid two fields exist from the table Tb_studentsubject. So the code is as follows:
Copy Code code as follows:
Create proc Saveinfo
(
---argument list is no longer written individually
@subjectList varchar (400))
As
BEGIN Tran
--Add or modify primary table ellipsis
DECLARE @subjectID int,
@subjectIDsLen int,--Account length
@subjectIDLen int,--A single account length
@nStart INT,--the starting position of the record lookup
@nLast INT--record ', ' position
Select @subjectIDsLen =len (@SubjectIDs), @nStart =1, @nLast =0
Delete Tb_studentsubject WHERE studentid= @sId--Delete all related records
IF (@subjectIDsLen >0)
BEGIN
while (@nStart < @subjectIDsLen)
BEGIN
Set @nLast =charindex (', ', @SubjectIDs, @nStart)--record the position of the comma starting at @nstart position
Set @subjectIDLen = @nLast-@nStart--record the length of each Subjectid
Set @subjectID =substring (@SubjectIDs, @nStart, @subjectIDLen)--Gets a Subjectid
Insert into Tb_studentsubject (Studentid,subjectid) VALUES (@sId, @subjectID)
Set @nStart = @nLast +1--Set the starting position of the next lookup
End
End
If @ @Error =0
Commit TRANSACTION
Else
ROLLBACK TRANSACTION
End
In this code, the list of subjects is segmented by "," and the top stored procedure is annotated with a focus on the functions that are brought by the two systems
CHARINDEX (expression1, expression2 [, Start_location])
Expression1: The sequence that contains the characters to find
Expression2: Typically a column that is searched for a specified sequence
Start_location: The character position at the beginning of the search for expression1 in expression2. If start_location is not specified, is a negative number or 0
Another function is: SUBSTRING (expression, start, length) intercepts the specified length of string from the specified data source string
Where expression: is a string, binary string, the data source string to manipulate
Start: To intercept the starting position, note that this method and C # is not the same location, the database strat starting from 1, the program starting from 0
Length: Intercept the lengths of content
The other one on Len (expres) This is easier, get the length of the specified character
Through the above code, you can implement a complete stored procedure to achieve the master-slave table data processing, data consistency and ease of operation are guaranteed, and the same general.
Note: In a similar master-detail table, it is recommended that you do not set an AutoNumber primary key from a table, because frequent deletions and additions cause the primary key to continue to increase, and without the use of this primary key.
The above content is personal opinion, hope everybody exchanges the corresponding method more.
