Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

INSTEAD OF trigger and OUTPUT clause Expand / Collapse
Author
Message
Posted Thursday, May 13, 2010 12:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 2,818, Visits: 2,560
Definitely a head scratcher. Thanks.
Post #921538
Posted Thursday, May 13, 2010 2:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 3:13 PM
Points: 306, Visits: 1,458
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
Post #921629
Posted Thursday, May 13, 2010 11:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 13,605, Visits: 10,486
Nice question. And not so easy...



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #921771
Posted Monday, May 17, 2010 6:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:55 AM
Points: 558, Visits: 1,475
Good learning question, a real tough one.
Post #922851
Posted Monday, May 31, 2010 11:12 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 8,707, Visits: 9,255
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
Post #930438
Posted Thursday, June 10, 2010 4:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 29, 2014 11:28 AM
Points: 76, Visits: 69
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?
Post #935255
Posted Tuesday, July 6, 2010 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 8:05 AM
Points: 57, Visits: 5
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
Post #947767
Posted Tuesday, July 6, 2010 3:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,914, Visits: 5,092
ipkunte (7/6/2010)
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


what version of SQL server are you running the query on?


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #947774
Posted Tuesday, July 6, 2010 4:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 8:05 AM
Points: 57, Visits: 5
I executed the script on SQL Server 2005.
Post #947806
Posted Saturday, August 7, 2010 3:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 31, 2014 5:21 PM
Points: 66, Visits: 12
What a badly-worded question - it was not clear whether the question was about the contents of @tab or TestTable
Post #965595
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse