How to insert data in two tables simultaneously.

Question

0.00/5 (No votes)

See more:

I am new to MVC

I have two tables
Employee and Users

Employee              
---------	      
PK_EmployeeID int      
EmpNo string	       	
EmpName string

...

Users
-------
UserName string
UserPassword string

UserName field in Users table is equal to EmpNo field in Employee.

I want to insert the record EmpNo into UserName simultaneously while creating new employee.
how can this be achieved in MVC3 ?
Do i need to make any Pk/Fk Relationship between these two tables.

Posted 2-Sep-13 1:10am

saurabh kumar mahto

Updated 2-Sep-13 1:26am

Nelek

v2

Add a Solution

Comments

Bala Selvanayagam 2-Sep-13 7:13am

You could use a database trigger ?

saurabh kumar mahto 2-Sep-13 7:26am

Thanks..

saurabh kumar mahto 3-Sep-13 1:36am

Hi Bala..
Can you please help me out with some code sample regarding this..
Thanks a lot..

kesav prakash 2-Sep-13 8:00am

bala can u post the query over here

Bala Selvanayagam 3-Sep-13 4:16am

Please see my answer below

saurabh kumar mahto 3-Sep-13 4:27am

Thank you Bala for your help..You are a genious.

Bala Selvanayagam 3-Sep-13 4:45am

Pleasure

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bala Selvanayagam · Accepted Answer · 2013-09-02T22:16:00

Here the sample trigger further to my comments above

1. Create the trigger - run this in the SQL query analyser

CREATE TRIGGER tr_insert_user 
   ON  dbo.Employee
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    insert into users (UserName)
	select  EmpName from inserted

END
GO

2. Run a test query on the query analyser (insert data into the employee table)

SQL

INSERT INTO [dbo].[Employee]
           (
           [EmpName])
     VALUES
           ('Hello')

3.Now the trigger is automatically invoked (when ever you do a insert statement) and the users table is inserted with a new entry

Run the query on the database to check this out

select * from users