INSTEAD OF trigger and OUTPUT clause

  • Chad Crawford

    SSChampion

    Points: 11610

    HA HA HA! I took a guess (which was wrong), but checked by running the code before I answered (some consider this cheating, I consider it "using all available resources for the task at hand"). But I forgot to change my answer before submitting, so I still got it wrong! Serves me right, I guess! 😛

    Chad

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    Wow, this was such a good question, I wish I'd written it! 😀 I learned at least three things from it, so I don't mind having gotten it wrong.

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Bradley Deem

    SSCrazy

    Points: 2565

    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?

    Change the trigger as follows. Of course, I'm not sure how you would capture the output result set, save using the temp table method as previously mentioned, but then the caller would have to know to look for that temp table.

    CREATE TRIGGER TestTrigger ON TestTable

    INSTEAD OF INSERT

    AS

    INSERT TestTable (string)

    OUTPUT INSERTED.*

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

    FROM inserted

    Excellent question, I definitely learned something today!

  • UMG Developer

    SSChampion

    Points: 13482

    Great question and learned a couple things in the process, a very good start to a day.

    Thanks!

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for a great question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Definitely a head scratcher. Thanks.

  • Darren Wallace

    Right there with Babe

    Points: 774

    Bradley Deem (5/13/2010)


    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?

    Change the trigger as follows. Of course, I'm not sure how you would capture the output result set, save using the temp table method as previously mentioned, but then the caller would have to know to look for that temp table.

    CREATE TRIGGER TestTrigger ON TestTable

    INSTEAD OF INSERT

    AS

    INSERT TestTable (string)

    OUTPUT INSERTED.*

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

    FROM inserted

    Excellent question, I definitely learned something today!

    Wow, I can't decide whether I'm amazed at the ingenouity in making an INSERT statement do that or appalled that an INSERT statement can be made to do. Probably both. Bradley, that's a beautiful piece of code, excuse me now, I have to go disinfect my dev server with bleach and thermite.

    Excellent QOTD.

    -DW

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question. And not so easy...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Chris Houghton

    SSCrazy

    Points: 2497

    Good learning question, a real tough one.

  • TomThomson

    SSC Guru

    Points: 104773

    Good question. Had me gawping at the insert statement for a while because I didn't know the syntax of the OUTPUT clause, so now I've learned something, but once I realised that the output...into bit wasn't specifying anything about what was to be inserted the answer was obvious because the inserted table can't have post-trigger values in it (because the trigger has the inserted table available to it when it starts execution).

    Tom

  • sandeepivs

    Old Hand

    Points: 394

    hi,

    As I look into the Output Clause,

    While we use insert command

    then the Temp table i.e @tab

    should be shown only the inserted values only right?But now the output is having some 0's why?

  • ipkunte

    SSC Enthusiast

    Points: 123

    When I ran the script I got the following errors.

    Msg 170, Level 15, State 1, Line 5

    Line 5: Incorrect syntax near 'OUTPUT'.

    Msg 170, Level 15, State 1, Line 7

    Line 7: Incorrect syntax near 'x'.

    Can anyone point out the reason?

    Thanks

  • This was removed by the editor as SPAM

  • ipkunte

    SSC Enthusiast

    Points: 123

    I executed the script on SQL Server 2005.

  • wiki 82367

    SSC Journeyman

    Points: 90

    What a badly-worded question - it was not clear whether the question was about the contents of @tab or TestTable :crazy:

Viewing 15 posts - 16 through 30 (of 31 total)

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