请告诉我如何使用1个事务将数据保存在两个表中. [英] Please tell me how to save data in two tables using 1 transaction.

问题描述

我想将数据保存到主表和辅助表中.他们有外键关系.主表一次只能有一个行条目，但是辅助表将为主键输入很多行.
我想为此目的使用事务，因此所有数据都不输入.
我知道如何使用交易，但对于单个查询却不使用这些复杂的交易

主表结构

I want to save data into a Primary table and into a secondary table. They have foreign Key relationship. Primary table will have only one row entry at a time but Secondary table will have many rows entered for the primary key.
I want to use transactions for this purpose so either all the data is entered on none.
I know how to use transactions but for single query not for these complex one''s

Primary Table Structure

TicketNo , HeadName, ValidFrom , ValidTo , UnitPrice , NoOfPerson , TotalPrice 

二级表结构

Secondary Table Structure

TicketNo , Name

我已经制定了将数据输入到这两个表中的过程.这是我到目前为止所做的

I have made procedures for entering data into these two tables.This is what i have done upto now

trns = con.BeginTransaction();
               ans = obj.SaveTicketDetails(trns);

if (ans>0) // i.e if nonquery gives result as 1


<pre>for (int i = 1; i < Member.Count; i++)
                    {
                        objMem.Visitorname = Name[i];
                        

                        ans1 = objMem.SaveGroupMembers(trns);
                        

                       

                    }

                      if (ans1)> 1)
                        {
                            flag = false;
                            trns.Rollback();
                        }

推荐答案

建议您在StoredProcedure中创建事务，而不是创建C#事务.

请参考: SQL Server事务和错误处理 [ ^ ]

谢谢
-Amit Gajjar

Hi,

Instead of creating C# Transaction i suggest you to create transaction in your StoredProcedure.

Please refer : SQL Server Transactions and Error Handling[^]

Thanks
-Amit Gajjar

最好在第一个表上编写触发器.当第一个表更新时，第二个表将立即更新..

例如:

Better to write trigger on the first table. When first table is update immediately second table will update..

Ex:

CREATE TRIGGER trig_Update_Employee
ON [EmployeeResult]
FOR INSERT
AS
Begin
    Insert into Employee (Name, Department) 
    Select Distinct i.Name, i.Department 
    from Inserted i
    Left Join Employee e
    on i.Name = e.Name and i.Department = e.Department
    where e.Name is null
End

检查此
如何在多个表中插入数据在一个存储过程中? [

Hi ,
Check this
how can i insert data in multiple table in one store procedure?[^]
Best Regards
M.Mitwalli

这篇关于请告诉我如何使用1个事务将数据保存在两个表中.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！