Multiple INSERT statements into the same table

  • Hi,

    I need a little help to understand why my trigger is not working correctly. Basically my trigger should insert 1 row into 1 table and another 2 rows into a second table. (2 INSERT statements)

    When testing, I can see 1 row in TableTarget1 and 1 row in TableTarget2 (from the first INSERT). I do not catch any errors ...

    I have the following logic in my trigger:

    TRIGGER SourceTable_Insert

    ON SourceTable

    AFTER INSERT

    AS

    BEGIN

    BEGIN TRY

    INSERT INTO TableTarget1 Select(v1, v2, v3) FROM inserted

    INSERT INTO TableTarget2 Select(v1, v5, v6) FROM inserted

    INSERT INTO TableTarget2 Select(v1, v5, v7) FROM inserted

    END TRY

    BEGIN CATCH

    -- send email to admin

    exec msdb.dbo.sp_send_DBMail ...

    END CATCH

    END

    GO

  • I'm not going to address the WHY you are doing this or suggest any better ways, this is simply a suggestion as i'm not sure why your trigger doesn't work as is. The first solution requires you to create a TALLY table explained in various posts on this site.

    This is just something to try .. 😀

    TRIGGER SourceTable_Insert

    ON SourceTable

    AFTER INSERT

    AS

    BEGIN

    BEGIN TRY

    INSERT INTO TableTarget1

    SELECT v1, v2, v3

    FROM inserted AS i

    INSERT INTO TableTarget2

    SELECT v1, v5, CASE WHEN n = 1 THEN v6 ELSE v7 END

    FROM inserted AS i

    CROSS JOIN dbo.tally

    WHERE n IN (1,2)

    END TRY

    BEGIN CATCH

    -- send email to admin

    EXEC msdb.dbo.sp_send_DBMail ...

    END CATCH

    END

    GO

    OR this might work as well ..

    TRIGGER SourceTable_Insert

    ON SourceTable

    AFTER INSERT

    AS

    BEGIN

    BEGIN TRY

    INSERT INTO TableTarget1

    SELECT v1, v2, v3

    FROM inserted

    INSERT INTO TableTarget2

    SELECT v1, v5, v6

    FROM inserted

    UNION ALL

    SELECT v1, v5, v7

    FROM inserted

    END TRY

    BEGIN CATCH

    -- send email to admin

    EXEC msdb.dbo.sp_send_DBMail ...

    END CATCH

    END

    GO

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • It works ....

    After seeing your post with 'it should work' and after a night of sleep I saw what was wrong: I was joining the current table with another table which was not returning anything.... so that's why I was seing just 1 line in the second target table.

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply