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 Wednesday, May 12, 2010 9:46 PM
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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Comments posted to this topic are about the item INSTEAD OF trigger and OUTPUT clause
Post #920964
Posted Wednesday, May 12, 2010 11:31 PM


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:46 AM
Points: 3,915, Visits: 5,103
Very good question, highlighting the side-effect of INSTEAD OF triggers.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #921000
Posted Thursday, May 13, 2010 1:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,008, Visits: 369
thank you... however i will take some time to understand this query ... .. still not so perfect.
Post #921033
Posted Thursday, May 13, 2010 2:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,739, Visits: 6,359
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?
Post #921063
Posted Thursday, May 13, 2010 3:35 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: Wednesday, August 13, 2014 9:41 AM
Points: 3,241, Visits: 4,999
Lost...
Selected wrong option.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #921107
Posted Thursday, May 13, 2010 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 6,594, Visits: 8,878
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #921132
Posted Thursday, May 13, 2010 4:51 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #921133
Posted Thursday, May 13, 2010 4:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 6,594, Visits: 8,878
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #921134
Posted Thursday, May 13, 2010 4:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 5,589, Visits: 24,968
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

Before posting a performance problem please read
Post #921136
Posted Thursday, May 13, 2010 5:03 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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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!
Post #921138
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse