INSTEAD OF trigger and OUTPUT clause

Question

INSTEAD OF trigger and OUTPUT clause

July 6, 2010 at 3:10 am

This was removed by the editor as SPAM

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

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

Chad Crawford SSChampion Points: 11610 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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

Thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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 More actions · Answer 6

Daniel Bowlin

SSC-Dedicated

Points: 34566

May 13, 2010 at 12:11 pm

#1167119

Definitely a head scratcher. Thanks.

Darren Wallace Right there with Babe Points: 774 More actions · Answer 7

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: 259105 More actions · Answer 8

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: 2557 More actions · Answer 9

Chris Houghton

SSCrazy

Points: 2557

May 17, 2010 at 6:21 am

#1168217

Good learning question, a real tough one.

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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 SSC-Addicted Points: 494 More actions · Answer 11

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 More actions · Answer 12

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

ipkunte SSC Enthusiast Points: 123 More actions · Answer 13

ipkunte

SSC Enthusiast

Points: 123

July 6, 2010 at 4:26 am

#1189006

I executed the script on SQL Server 2005.

wiki 82367 SSC Journeyman Points: 90 More actions · Answer 14

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