September 20, 2008 at 7:55 pm
Guys,
I have scenario where trigger on a table should insert in multiple child tables, but the parent and child tables are referenced by identity ID columns.
I am unable to capture the Parent ID columns and inserting into child tables.
In the below example all the tables have primary keys with autogenerated property and foreign keys refer to these auto generated primary keys. In the 2nd and 3rd insert
statements of stored procedure P_EMPLOYEE_INSERT below how can parentid which have identity property be passed.
I cannot use below select statement to capture ParentIDs, since there are cases where name and dob are duplicated and do not want to use select statements before each insert.
select empid from employee where name = @name and dob = @dob
Is there anyway to capture identity ParentID.
Any suggestions/inputs would help.
Thanks
CREATE TABLE PERSON
PERSONID INT IDENTITY(1, 1) NOT NULL, (primary key)
PARTYID BIGINT,
NAME VARCHAR(200),
DATE_TIME_CREATED DATETIME NULL,
DATE_TIME_MOD DATETIME NULL
)
CREATE TABLE EMPLOYEE (
EMPID INT IDENTITY (1, 1) NOT NULL, (primary key)
PERSONID INT NOT NULL, (Foreign key constraint with PERSON.PERSONID),
STARTDATE DATETIME
)
CREATE TABLE EMPLOYEEHIST (
EMPHISTID INT IDENTITY (1, 1) NOT NULL, (primary key)
EMPID INT NOT NULL, (Foreign key constraint with EMPLOYEE.EMPID),
STATUSDT DATETIME,
STATUS VARCHAR(200)
)
create procedure [dbo].[P_EMP_INSERT](@PARTYID BIGINT, @NAME VARCHAR(200), @DOB DATETIME)
as
begin
begin transaction
insert into PERSON (PARTYID, NAME, DOB, DATE_TIME_CREATED, DATE_TIME_MOD)
values(@PARTYID, @NAME, @DOB, GETDATE(), GETDATE())
insert into EMPLOYEE (PERSONID, STARTDATE)
values(????, getdate())
insert into EMPLOYEEHIST (EMPID, STATUSDT, STATUS)
values (????, getdate(), 'open')
commit
end
September 20, 2008 at 8:17 pm
You're going to want to look up the OUTPUT clause, since you're using SQL Server 2005. This will allow you to spit out the ID from the initial phase for the rows you just inserted. You can then use that result to then insert into the child tables.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2008 at 3:00 pm
Have you examined the @@IDENTITY function check BOL for a complete description.
insert into PERSON (PARTYID, NAME, DOB, DATE_TIME_CREATED, DATE_TIME_MOD)
values(@PARTYID, @NAME, @DOB, GETDATE(), GETDATE())
SET @key = @@Identity
Got carried away here and Anjali is correct in saying the above line should be
SET @key = Scope_Identity
Thank you Anjali for catching that mistake.
Then use the @key as the value to insert into the additional tables.
September 22, 2008 at 5:49 am
Try This........Hope this will give required results
create procedure [dbo].[P_EMP_INSERT](@PARTYID BIGINT, @NAME VARCHAR(200), @DOB DATETIME)
as
begin
begin transaction
insert into PERSON (PARTYID, NAME, DOB, DATE_TIME_CREATED, DATE_TIME_MOD)
values(@PARTYID, @NAME, @DOB, GETDATE(), GETDATE())
select @personId = personId from PERSON where PARTYID = @PARTYID
insert into EMPLOYEE (PERSONID, STARTDATE)
values(@personId , getdate())
insert into EMPLOYEEHIST (EMPID, STATUSDT, STATUS)
values (@personId , getdate(), 'open')
commit
end
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply