none
INSERTED Table - When it gets populated with single or multiple rows? RRS feed

  • Question

  • Hi,

    I'm trying to create a trigger which then insert to a table. i'm wondering when does the INSERTED table gets populated with single or multiple rows?

    Should I always assume that the INSERTED Table will contains several rows? What does the scope of the INSERTED table in the trigger, isn't based on the user session?

     The reason why i asked this is because as far as i know inserted table may contain several table when the trigger fires which is why I use cursor to insert  records in the table ( there's a behind why i use cursor).

    But if the inserted table will only contain a single record during the session of the trigger then i can avoid the cursor.

    Thanks.

    Thursday, March 12, 2015 10:45 PM

Answers

  • Hi Vinxster,

    As per  the fragment below picked up from the BOL:Use the inserted and deleted Tables

    • The inserted table stores copies of the affected rows during INSERT and UPDATE statements.

    If your control on transaction process is reliable, namely strictly one record at a time, there will no multiple rows affected.

    For any feedback on our support, you can click here.


    Eric Zhang
    TechNet Community Support


    Friday, March 13, 2015 3:07 AM
    Moderator

All replies

  • Should I always assume that the INSERTED Table will contains several rows?

    In one word: YES!

    What does the scope of the INSERTED table in the trigger, isn't based on the user session?

    The scope is exactly that trigger. It it is not visible elsewhere. That includes any dynamic SQL you may create in the trigger.

     The reason why i asked this is because as far as i know inserted table may contain several table when the trigger fires which is why I use cursor to insert  records in the table ( there's a behind why i use cursor).

    Keep in mind that a trigger always executes in the context of a transaction, which means that you are holding locks, and long-running work should be avoided. I don't know what you do in that cursor, but try to find a set-based solution. Or move the processing elsewhere, if the action is not related to database integrity or auditing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 12, 2015 11:11 PM
  • I created a trigger based on a view and that view is composed of Tbl_A and tbl_B. Now tbl_A is parent while tbl_B is the child.

    Now, when the insert trigger fires, I need to populate both tables using a stored procedure. My dilemma is, if we assume that the INSERTED Table that contains several rows, I  need a cursor for it  since there no Tabled-value parameter in SQL2005 (or use XML perhaps) to pass the rows from INSERTED table. Additionally, it's  difficult to use the code below as i need to pass the identity id of tbl_A to tbl_B

    INSERT INTO tbl_a
    (
    name
    )
    SELECT name  FROM INSERTED
    
    INSERT INSERT tbl_b
    (
    tbla_ID,--foreign_key
    child_name
    )
    SELECT <identity id of tbl_A>,child_name FROM INSERTED

    How exactly it may contains multiple records, in what scenario? we don't do bulk insert. the only scenario that i could think of, if both user save the records at the same time..but they should have there own INSERTED Table isn't?

    I hope I explained my requirements :P

    Please note that this trigger is just temporary until we developed a permanent one but still needs to be done.







    • Edited by Vinxster Thursday, March 12, 2015 11:50 PM
    Thursday, March 12, 2015 11:27 PM
  • Hi Vinxster,

    In the below scenario, there are multiple rows inserted.

    CREATE TABLE test(col1 int identity,col2 VARCHAR(99));
    
    INSERT test(col2) SELECT 'A' UNION ALL SELECT 'B'
    
    INSERT test(col2) VALUES('A'),('B'); -- this syntax is supported in SQL Server 2008 and afterwards


    I believe you have no need to use the cursor, since what you need to do is to pass the inserted rows. Two ways to get the inserted rows, please play the below sample and tweak accordingly.

    CREATE TABLE test(col1 int identity,col2 VARCHAR(99))
    
    CREATE TABLE #Temp (col1 int ,col2 VARCHAR(99))
    
    
    --option1: Without trigger. You have no need to use trigger by applying the below statement when inserting
    INSERT INTO test2(col2)
    OUTPUT inserted.col1,inserted.col2 INTO #Temp
    SELECT 'ABC' UNION ALL SELECT 'BCD'
    
    SELECT * FROM #Temp
    
    --option2: With Trigger
    CREATE TRIGGER trgAIonTest
    ON test
    AFTER INSERT 
    AS
    select @@ROWCOUNT
    INSERT #Temp SELECT * FROM INSERTED
    GO
    
    INSERT INTO test(col2)
    SELECT 'ABC' UNION ALL SELECT 'BCD'
    
    SELECT * FROM #Temp

    If you have any feedback on our support, you can click here.


    Eric Zhang
    TechNet Community Support



    Friday, March 13, 2015 2:07 AM
    Moderator
  • Hi Eric_Zhang,

    thanks for the response.

    With multiple rows, I understand that if you do bulk inserts, it will create several rows in the INSERTED table. But since we control the transaction process and we know for a fact that user will only be able to save a record one at a time, do we still expect multiple rows? I just want to have a clear concept on the INSERTED table.

    That option 1 looks doable, I will try it and let you know. thanks

     

    Friday, March 13, 2015 2:36 AM
  • Hi Vinxster,

    As per  the fragment below picked up from the BOL:Use the inserted and deleted Tables

    • The inserted table stores copies of the affected rows during INSERT and UPDATE statements.

    If your control on transaction process is reliable, namely strictly one record at a time, there will no multiple rows affected.

    For any feedback on our support, you can click here.


    Eric Zhang
    TechNet Community Support


    Friday, March 13, 2015 3:07 AM
    Moderator

  • If you only insert rows one at a time, then the inserted table will only have one row.  But if you go that way, I would recommend that you code your trigger so that it returns an error and rejects the insert if anyone ever attempts to insert multiple rows with one command.  So, for example, your trigger code could look like:
    Create Trigger <triggername> On <yourtablename> For insert AS
    If (Select Count(*) From inserted) > 1
    Begin
      RaisError ('You may not insert multiple rows with one command into yourtablename', 16, 1);
      Rollback;
    End
    Else
    Begin
      -- the code for your trigger goes here
    End
    Tom
    Friday, March 13, 2015 3:51 AM
  • But since we control the transaction process and we know for a fact that user will only be able to save a record one at a time, do we still expect multiple rows? I just want to have a clear concept on the INSERTED table.

    ...and then the DBA or someone else sees fit to enter a number of rows directly from a query window. And don't laugh. That is bound to happen sooner or later.

    However, just because this can (and will) happen does not mean that you need to handle it on equal footing with the normal case user entering data through the application. What you cannot permit yourself to is to drop the DBA case on the floor, that is write the trigger as if there would either be single-row inserts and produce incorrect results for multi-row inserts.

    But, yes, allowing yourself to use a cursor, if you want to reuse the existing stored procedure is feasible. That is also the more drastic solution suggested by Tom to add an explicit check that disallows multi-row inserts.

    Finally, permit me to comment on this:

    Additionally, it's  difficult to use the code below as i need to pass the identity id of tbl_A to tbl_B

    You can use the OUTPUT clause to capture the values, but that requires that you have something you can map the identity values to in the columns you insert, and this is not always the case. However, there is a lot simpler solution to the problem: don't use IDENTITY. IDENTITY is one of these over-used and over-abused features in SQL Server. You need it when you want to support high-concurrency inserts, because rolling your own requires a serialisation point. But with a moderate insertion frequency, IDENTITY only gives you headache.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 13, 2015 8:35 AM
  • Hi Erland,

    We dropped the idea of having a trigger and have an un-normalized flat table instead. Anyway, this is just temporary.

    With regards to Identity, what do you recommend? GUID or a custom one?

    Thanks.

    Sunday, March 15, 2015 9:56 PM
  • With regards to Identity, what do you recommend? GUID or a custom one?

    Rolling your own:

    BEGIN TRANSACTION

    DECLARE @id int

    SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)

    INSERT tbl (id, col1, col2, ...)
       SELECT @id + row_number() OVER(ORDER BY (SELECT NULL)) - 1,
              col1, col2, ...
       FROM   tbl

    COMMIT TRANSACTION

    There are a number of variations of the theme.

    But note that this pattern is not good if you have high-concurrency inserts, as the above creates a serialisation point.

    A good alternative to IDENTITY are sequences, but they are only available on SQL 2012 and later.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 15, 2015 10:57 PM