Passing value to one table and updating in parent table

Question

I have two tables. dbo.Emp EmpID(PK) | Name | TypeID(FK)

dbo.EmpType TypeID(PK) | Type

TypeID: AutoIncrement only in dbo.EmpType

When I update value for TypeID in Emptype, it needs get stored in TypeID of dbo.Emp I used SP which is not working.

Create Procedure bridge_Type(
@EmpID INT,
@Name   varchar(50),
@Mob2   numeric(10,0),
@Email  varchar(50),
@Type   varchar(50)
)
AS 
DECLARE @TYPEID int
Set NOCOUNT OFF

BEGIN TRANSACTION
Insert into dbo.Emp VALUES (@EmpID, @Name, @Mob2, @Email, @TYPEID)
IF @@ERROR <> 0
BEGIN 
ROLLBACK
RETURN
END

INSERT INTO dbo.EmpType VALUES (@Type)
IF @@ERROR <> 0 
BEGIN     
ROLLBACK     
RETURN 
END  

declare @id int
SET @id= @TYPEID;
Update Dbo.Emp 
Set @TYPEID= (Select TypeID from dbo.EmpType
          WHERE  TypeID=@typeID)
COMMIT

try to use trigger for this.. When you are inserting data in emptype create a trigger which also insert it to table emp too — Ram, Jul 20 '12 at 08:28
Hello, I am new to SP as well. Here is the code that I used. But it is not getting stored in dbo.Emp — Girish, Jul 20 '12 at 08:29
read this http://www.sqlteam.com/article/an-introduction-to-triggers-part-i — Ram, Jul 20 '12 at 08:56

score 1 · Accepted Answer · answered Jul 20 '12 at 08:38

Try This SP, This will first insert the EmpType, and From SCOPE_IDENTITY() it will get the Inserted Identity Value, and then insert emp.

Create Procedure bridge_Type(
@EmpID INT,
@Name   varchar(50),
@Mob2   numeric(10,0),
@Email  varchar(50),
@Type   varchar(50)
)
AS 
DECLARE @TYPEID int
Set NOCOUNT OFF

BEGIN TRANSACTION

INSERT INTO dbo.EmpType VALUES (@Type)

SET @TYPEID = SCOPE_IDENTITY()

IF @@ERROR <> 0 
BEGIN     
ROLLBACK     
RETURN 
END 


Insert into dbo.Emp VALUES (@EmpID, @Name, @Mob2, @Email, @TYPEID)
IF @@ERROR <> 0
BEGIN 
ROLLBACK
RETURN
END

COMMIT

OMG that was great! One line did the trick !! SET @TYPEID = SCOPE_IDENTITY() Thanks a lot Yograj :) Thanks — Girish, Jul 20 '12 at 09:05

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Not 100% sure why you would be updating the typeID in your EmpType table (particularly as it is an autoincrement key), however, you could look at updating your FK constraint to 'cascade on update':

When to use "ON UPDATE CASCADE"

http://msdn.microsoft.com/en-us/library/aa933119(v=sql.80).aspx

edited May 23 '17 at 12:20

Community

1
1

answered Jul 20 '12 at 08:46

Paddy

33,309
15
79
114

Sorry - didn't read your SP, just the text. You're not actually updating your type ID, rather inserting - previous answer by Mr. Gupta seems to make sense. – Paddy Jul 20 '12 at 08:51
Yes, because TypeID in Emp table is not a autoincrement value. The reason for this is I have many other bridging tables that needs the value from other table. :) – Girish Jul 20 '12 at 09:17

Passing value to one table and updating in parent table

2 Answers2

Linked