Wrong identity in trigger

[Sean]

Hi,

Can someone help please

I'm trying to write a trigger so that when I insert values in table "FutCon", the trigger will create an entry in table "instrument1" and then insert into "Futcon" using the instrument id that has just been created.

The trigger works for a single insert but when I do a multiple insert it inserts one entry into instrumentId and uses the identity of this for multiple rowes in the futcon table. I wanted to get a uniwue instrument id for each row inserted into the Futcon table.


*****trigger****
create trigger futconinsid
on futcon
instead of insert
as
insert instrument1
(
instrumenttypeid
)
select it.InstrumentTypeId from InstrumentType it where it.InstrumentName = 'FutCon'
insert futcon
(
instrumentid,
myvalue,
myvalue2,
myvalue3,
myvalue4
)
select SCOPE_IDENTITY(), inserted.myvalue, inserted.myvalue2, inserted.myvalue3, inserted.myvalue4 from inserted

[rmiao]

Should read from inserted table to find out how many rows were inserted.

[Sean]

I've read through the inserted table, adding a row at a time and it works now thanks.