Post reply

INSTEAD OF trigger and OUTPUT clause

  • May 12, 2010 at 9:46 pm

    #221092

    Comments posted to this topic are about the item INSTEAD OF trigger and OUTPUT clause

  • May 12, 2010 at 11:31 pm

    This was removed by the editor as SPAM

  • May 13, 2010 at 1:14 am

    #1166669

    thank you... however i will take some time to understand this query ... :-D.. still not so perfect.

  • May 13, 2010 at 2:18 am

    #1166693

    Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!

    So how would you get the identity values of the rows you'd just inserted?

  • May 13, 2010 at 3:35 am

    #1166736

    Lost...

    Selected wrong option.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • May 13, 2010 at 4:49 am

    #1166756

    Good Excellent question. Learned quite a bit in the 2nd link (INSTEAD OF trigger).

    FYI, in the explanation, it should be the string 'AnotherString' to be replaced with 'Stub', not 'TestString'.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 13, 2010 at 4:51 am

    #1166757

    Toreador (5/13/2010)

    Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!

    So how would you get the identity values of the rows you'd just inserted?

    Good question, I didn't think about it when I was writing the QOTD.

    Here is one of the possible solutions. We can get the identity values inside the trigger using the OUTPUT clause. These values can be passed outside the trigger via a temporary table.

    CREATE TABLE TestTable (id INT IDENTITY, string VARCHAR(100))

    GO

    CREATE TRIGGER TestTrigger ON TestTable

    INSTEAD OF INSERT

    AS

    IF OBJECT_ID('tempdb..#id') IS NOT NULL

    INSERT TestTable (string)

    OUTPUT inserted.id

    INTO #id

    SELECT CASE WHEN string = 'TestString' THEN string ELSE 'Stub' END

    FROM inserted

    ELSE

    INSERT TestTable (string)

    SELECT CASE WHEN string = 'TestString' THEN string ELSE 'Stub' END

    FROM inserted

    GO

    CREATE TABLE #id (id INT)

    SELECT * FROM TestTable

    INSERT TestTable (string)

    SELECT x.string

    FROM

    ( SELECT 'TestString' AS string

    UNION ALL

    SELECT 'AnotherString' AS string

    ) x

    SELECT * FROM #id

    GO

    Maybe it's not the best solution, but it works 🙂

  • May 13, 2010 at 4:52 am

    #1166758

    Toreador (5/13/2010)

    Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!

    So how would you get the identity values of the rows you'd just inserted?

    By not using an "INSTEAD OF" trigger. But I agree with you... you normally don't go investigating the types of triggers on tables when writing code to insert into them.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 13, 2010 at 4:55 am

    #1166760

    Excellent question ... really got the brains cells working.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 13, 2010 at 5:03 am

    #1166761

    WayneS (5/13/2010)

    in the explanation, it should be the string 'AnotherString' to be replaced with 'Stub', not 'TestString'

    You are right, thanks for the correction!

  • May 13, 2010 at 5:58 am

    #1166782

    I selected "Returns an error". It didn't seem right, but I'm not sure why the following code works

    INSERT TestTable (string)

    OUTPUT inserted.*

    Doesn't the select return more columns (ID and String) than the insert list? That is one of my more common errors...

    Great question, BTW. I need to learn more about OUTPUT!

  • May 13, 2010 at 7:15 am

    #1166845

    tlewis-993411 (5/13/2010)

    I'm not sure why the following code works

    INSERT TestTable (string)

    OUTPUT inserted.*

    Doesn't the select return more columns (ID and String) than the insert list?

    When a row is inserted into a table, SQL Server fills all columns in that row. All these columns are contaned in the 'inserted.*' construct. Some of these values are explicit, and some are implicit (NULL, identity, default value, computed value etc).

    Here is an example:

    create table #t

    ( id int identity,

    a varchar(10),

    b varchar(10) default 'hello',

    c varchar(10),

    computed_column as b + ' ' + c

    )

    insert #t (c)

    output inserted.*

    values ('test')

    In the QOTD, the construct 'OUTPUT inserted.*' is equal to 'OUTPUT inserted.id, inserted.string'. 'Inserted.string' is an explicit column, while 'inserted.id' is an impilcit column.

  • May 13, 2010 at 7:46 am

    #1166870

    My problem is that I expected that "inserted" would be a complete copy of the inserted row (I haven't used OUTPUT, but triggers certainly work that way). As such, the Inserted.* should return 2 columns, but the insert list only contains one field. See the code below and the resulting error message. This is almost exactly the same construct.

    I'll do some research, but it appears that Output is only returning the "explicitly added" columns?

    BTW, generically, I'm opposed to * for this very reason!

    Declare @Foo Table

    (

    ID int,

    String varchar(100)

    )

    Insert into @Foo(String)

    Select * from

    (Select 1 as intval, 'sample1' as string

    union all

    Select 2 as intval, 'sample2' as string

    ) as bar

    Msg 121, Level 15, State 1, Line 7

    The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

  • May 13, 2010 at 7:47 am

    #1166871

    Toreador (5/13/2010)

    Interesting - it never occurred to me that the identity values wouldn't be available in the inserted table!

    So how would you get the identity values of the rows you'd just inserted?

    If you are using an instead of trigger, I'm not sure. However, not using an instead of trigger works fine to retrieve the identity values using OUTPUT. I ran the below on SQL Server 2008.

    CREATE TABLE Test

    (

    ID int identity(1,1) NOT NULL PRIMARY KEY,

    AnotherColumn varchar(50) NULL

    )

    GO

    CREATE TRIGGER Test_Insert

    ON Test

    AFTER INSERT

    AS

    SELECT 'Trigger Executed'

    GO

    DECLARE @tTableVar table(TestID int NOT NULL,

    TestAnotherColumn varchar(50) NULL)

    INSERT INTO Test(AnotherColumn)

    OUTPUT inserted.ID, inserted.AnotherColumn

    INTO @tTableVar(TestID,TestAnotherColumn)

    SELECT 'Blah'

    UNION ALL

    SELECT 'Blah2'

    SELECT TestID,TestAnotherColumn FROM @tTableVar

    DROP TABLE Test

  • May 13, 2010 at 7:55 am

    #1166877

    Duh - The "Into @tab" is not using the Select, but instead the output, which does include two columns.

    Select * into a table (or Output * into table, in this case) only works if the fields are selected in the same order and same count as the target, which is obviously true in this case. However, I would still avoid the * if possible, explicitly naming the fields instead. Otherwise, a change to the target table would break that condition.

    Thanks for putting up with me working through my own question. That's how I learn almost everything!

Viewing 15 posts - 1 through 15 (of 30 total)

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