CODEWITHSUNDEEP
sqlsql-servertriggerssql-insert
Jordumus
Jordumus

Reputation: 2783

Use INSERT information from trigger in another insert

I am writing a trigger after insert that inserts data in another table using the inserted data. This part, I figured out But then, I want to use that freshly inserted data, to insert in another table.

CREATE TRIGGER [dbo].[tr_Subsite_Insert]
   ON  [dbo].[Subsite]
   AFTER INSERT
AS 
BEGIN


    INSERT INTO TranslationKey
        SELECT 'base.' + inserted.[name] + '.' + sst.name FROM SubsiteText sst
            CROSS JOIN inserted

    --Use the result of the above INSERT to insert in another table.
    --Example code below:
    INSERT INTO Translation
       SELECT lng.id, inserted2.id, '' FROM Languages lng
       CROSS JOIN inserted2
    --Obviously, "inserted2" doesn't exist.

END
GO

How can I get the values from the first insert, to use them in the 2nd insert?

Upvotes: 1

Views: 90

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You have 2 options here:

  1. Create another trigger on TranslationKey table (this is straightforward)
  2. Use OUTPUT keyword(I will elaborate on this)

Declare a table variable and use OUTPUT keyword to grab newly inserted IDs. Then use that temp table in the CROSS JOIN. Here is an example:

/*here you should change UNIQUEIDENTIFIER to 
  actual type of ID column in TranslationKey table*/
DECLARE @t TABLE ( ID UNIQUEIDENTIFIER )

INSERT  INTO TranslationKey
OUTPUT  INSERTED.ID
        INTO @t
        SELECT  'base.' + inserted.[name] + '.' + sst.name
        FROM    SubsiteText sst
                CROSS JOIN inserted

INSERT  INTO Translation
        SELECT  lng.id ,
                t.ID ,
                ''
        FROM    Languages lng
                CROSS JOIN @t t

Upvotes: 1

Related Questions