The OUTPUT clause and T-SQL Syntax

Question

Post reply

The OUTPUT clause and T-SQL Syntax

Viewing 10 posts - 16 through 24 (of 24 total)

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

Ken SSCoach Points: 17191 More actions · Answer 1

Thanks for the question Pramod. I had never considered how output would work with triggers.

Anipaul SSC-Insane Points: 24681 More actions · Answer 2

Anipaul

SSC-Insane

Points: 24681

July 24, 2013 at 12:47 am

#1635212

Nice...

Bangla Hall of Fame Points: 3137 More actions · Answer 3

Danny Ocean (7/22/2013)
Good question. I learn something new. 🙂

Yes...+1

Carlo Romagnano SSC-Insane Points: 22663 More actions · Answer 4

Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.

The question is WHY?

Technically, I do not see the connection or the hindrance of TRIGGER -> OUTPUT -> INTO.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

Carlo Romagnano (7/24/2013)
Hugo Kornelis (7/23/2013)
...
In other words - if the statement causes a trigger to fire, it should have either no OUTPUT clause or an OUTPUT clause with an INTO keyword. Using the OUTPUT clause without INTO (so that the results of the OUTPUT specification go to the client) is not allowed when a trigger fires.
The question is WHY?
Technically, I do not see the connection or the hindrance of TRIGGER -> OUTPUT -> INTO.

OUTPUT without INTO sends data to the client.

A trigger might also send output to the client.

Maybe there were some test cases where the two outputs somehow interfered, and the team decided that fixing it would take too much resources???

</speculation>

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

udayroy15 Ten Centuries Points: 1076 More actions · Answer 6

udayroy15

Ten Centuries

Points: 1076

July 24, 2013 at 5:58 am

#1635314

Good and tough question 🙂

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

July 24, 2013 at 11:14 am

#1635449

This was an interesting one - thanks, Pramod!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Lokesh Vij (7/22/2013)
Good Question Pramod. But explanation could have been refined.

+2

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

thava SSCrazy Points: 2275 More actions · Answer 9

Sorry, seems something not clear in this question, in the Question (select 4) what it means

i am choosing 2 3 5 it say i am wrong

if object_id('test2') is not null

drop table test2

if object_id('test1') is not null

drop table test1

create table test1 (a int not null primary key);

create table test2 (b int,a int )

GO

--statement 1

insert into test1

output inserted.a

select 1

GO

--statement 2

create trigger trig_test1

on test1 after insert

as

select 2

GO

--statement 3

insert into test1

select 3

GO

--statement 4

insert into test1

output inserted.a

select 4

GO

--statement 5

declare @temp as table (b int)

insert into test1

output inserted.a

into @temp

select 5

select * from @temp

GO

So what is statement 1

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

sqlnaive SSCoach Points: 17437 More actions · Answer 10

Good QOTD. And some good clarification by Hugo (as always). 🙂