MVC : Insert data to two tables

Question

I have two tables wherein i want to insert the data to the first one (MASTER) and the other table would copy some of the data from the Master table..

Here is my representation:

I want the Ven_ID to also be reflected in my Workflow table Workflow_ReqID automatically.

I know this is possible but can someone give me the directions ?

Answer 1

If you want Ven_ID and Workflow_ReqID to be same get the Vent_ID in the output parameter in store procedure and pass it to the second table insert statement.

Answer 2

You can have a trigger/procedure at database level which will insert data into your second table. It depends if this table is updated anywhere else.

Answer 3

Get last inserted id using SCOPE_IDENTITY() after insertion and add it to workflow table. To save db trip you can use sproc for that.

Answer 4

There are two ways to go about it :

1. Use SQL Server AFTER INSERT Trigger. You can find plenty of resources off the internet on how to create a trigger and how to declare its definition.

2. Another way to do it is through entity framework (I see you have tagged entityframework)

I will explain how you can use entity framework

Let's say you have the entity representing the WorkFlow table as WorkFlow and the table representing Ven (may be vendor) as Vendor. Since you are having required foreign key in the WorkFlow table of the Vendor primary key, you must have a backing stub for that i.e. your WorkFlow table must have a virtual navigational property of type Vendor i.e.

public class WorkFlow
{
    //other properties

    public virtual Vendor Vendor{get;set;}
}

you just have to create WorkFlow object and the Vendor object (either create a new or retreive from db) and just assign it to the workflow object i.e.

WorkFlowObj.Vendor = objVendor

and EntityFramework will take care of rest.

I would prefer this way.

Though using triggers is not bad, but only problem with them is when you have to deploy, you must also deploy them triggers and every time you make changes to them, you must take care of them too.