quote:

---

Originally posted by amsqlguy

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
DECLARE @Person_ID int,@Emp_ID int

begin transaction
insert into PERSON (PARTYID, NAME, DOB, DATE_TIME_CREATED, DATE_TIME_MOD)
values(@PARTYID, @NAME, @DOB, GETDATE(), GETDATE())

SET @Person_ID=SCOPE_IDENTITY()
insert into EMPLOYEE (PERSONID, STARTDATE)
values(@Person_ID, getdate())
SET @Emp_ID = SOPE_IDENTITY()
insert into EMPLOYEEHIST (EMPID, STATUSDT, STATUS)
values (@Emp_ID, getdate(), 'open')
commit

end

---