Output current id of inserted record using OUTPUT in a trigger

I need to do this: On inserted record I need to store Inserted item identity and selected item identity. (Example below) I'm using after insert trigger (basically I copy one row from one table into another and do some more modifications. I have a table parameter like this:

DECLARE @Tempequipment TABLE
(Equipment_Id int,
DefaultEquipment_Id INT)

Then I insert into table like this:

INSERT INTO dbo.tblEquipmentType
        ( Name, EquipmentType_Id)  
SELECT name,(SELECT Equiment_Id FROM INSERTED)
FROM dbo.tblDefaultEquipmentType

This works fine!

What I need to do is: I need to insert into @TempEquipment EquipmentTypeId's that were just ineserted (c开发者_运维问答an be more than one) and DefaultEquipmentTypeId's that were just copied.

I was thinking about doing something like:

INSERT INTO dbo.tblEquipmentType
            ( Name, EquipmentType_Id)  
Output EquipmentTypeId, DefaultEquipmentTypeId into @TempEquipment
    SELECT name,(SELECT Equipment_Id FROM INSERTED)
    FROM dbo.tblDefaultEquipmentType

but of course this is not going to work, since it cannot get values from select statement, and not written correctly. Any help is appreciated!

UPDATE:

I have an Item. Item can be built on different equipment. Equipment has types (foreign key. And equipmentType has attributes (foreignkey).

So this mean that we have four tables Item->Equipment->EquipmentType->EquipmentAttribute. I need to store default EquipmentTypes and default EquipmentAtrributes for that type.

So I also got these replationship: Equipment->DefaultEquipmentType->DefaultEquipmentAttribute.

Now, When I insert new Item and select an equipment I want to copy defaults over to real tables (EquipmentType, EquipmentAttribute). Is it clear at least a little?

Aside from how you're trying to do this (which isn't working), what specifically are you trying to do?

It may be that this can be resolved by changing / normalizing your paradigm, instead of some kind of exotic code. For example, it looks odd to have a customers table with an orderID field in it. Unless your customers only ever order one thing... I would have expected to see a customers table, an items table, and then an orders table that joined customers with items.

Hope that makes sense -- but anyway, if not, can you post your table structure, and maybe be a little more clear on what you know ahead of time (e.g., I imagine you know who your customers are, and what they ordered...before you do the insert...yes?)

For an INSERT statement you can only access the columns which are in the insert column list, so the solution is to rewrite the statement as a MERGE statement which can access all the columns including columns which are in the INSERT target table for instance IDENTITY columns.

In the demo I've used dbo.INSERTED to emulate the virtual table INSERTED from the trigger.

USE master
GO
IF DB_ID('MergeOutputExample') IS NOT NULL
    DROP DATABASE MergeOutputExample
GO
CREATE DATABASE MergeOutputExample
GO
USE MergeOutputExample
GO
DECLARE @Tempequipment TABLE
(EquipmentId int,
DefaultEquipmentId INT,
ID int);

CREATE TABLE    dbo.INSERTED
(
    EquipmentTypeId         int PRIMARY KEY
);

CREATE TABLE    dbo.tblEquipmentType
(
    ID                      int         IDENTITY(1,1),
    Name                    varchar(50),
    EquipmentTypeId         int         PRIMARY KEY
);

CREATE TABLE    dbo.tblDefaultEquipmentType
(
    EquipmentTypeId         int,
    DefaultEquipmentTypeId  int         IDENTITY(1,1) PRIMARY KEY,
    Name                    varchar(50)
);

INSERT  dbo.inserted
        (
            EquipmentTypeId
        )
VALUES  (1);

INSERT  dbo.tblDefaultEquipmentType
        (
            EquipmentTypeId,
            Name
        )
VALUES  (
            1,
            'Hammer'
        );

MERGE   dbo.tblEquipmentType    AS ET
USING   (
            SELECT      DE.EquipmentTypeId,
                        DE.DefaultEquipmentTypeId,
                        DE.Name
            FROM        dbo.tblDefaultEquipmentType     DE
            INNER JOIN  dbo.INSERTED                    I
            ON          DE.EquipmentTypeId              = I.EquipmentTypeId 
        )                       AS DET
ON      ET.EquipmentTypeId          = DET.EquipmentTypeId
WHEN    NOT MATCHED BY TARGET
THEN    INSERT
        (
            Name,
            EquipmentTypeID
        )
        VALUES
        (
            DET.Name,
            DET.EquipmentTypeID
        )
OUTPUT  DET.EquipmentTypeId,
        DET.DefaultEquipmentTypeId,
        INSERTED.ID
INTO    @Tempequipment;

SELECT      *
FROM        @Tempequipment;

SELECT      *
FROM        dbo.tblEquipmentType;