OUTPUT Clause

We have a table, which is partitioned on a computed column, based on a UDF function (custom logic). We populate this computed column in an INSTEAD OF Trigger. But when we try to "output" the computed column using OUTPUT caluse, NULL values are returned for computed column. SQL BOL is not clear on this part of using output clause in instead of triggers. This does not even work for a table that is not partitioned. Here is an example of what we are trying to do...

I would appreciate any help on this...

IF OBJECT_ID('dbo.Test') IS NOT NULL

DROP TABLE dbo.Test

GO

CREATE TABLE dbo.Test

( IdCol INT IDENTITY(1,1) NOT NULL,

Date DATETIME NOT NULL,

CalcCol CHAR(10) NOT NULL

)

GO

CREATE TRIGGER trgTest_InsUpd ON dbo.Test

INSTEAD OF INSERT AS

BEGIN

INSERT INTO dbo.Test

( Date, CalcCol ) SELECT Date, CONVERT(CHAR(10), Date, 101) FROM INSERTED

END

GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

GO

CREATE TABLE #tmp(IdCol INT, Date DATETIME, CalcCol VARCHAR(10))

TRUNCATE TABLE #tmp

INSERT INTO dbo.Test(Date) OUTPUT INSERTED.IdCol, INSERTED.Date, INSERTED.CalcCol INTO #tmp

VALUES(GETDATE())

--Here the identity and Computed column are returned as NULL

SELECT * FROM #tmp

SELECT * FROM dbo.Test

Instead of trigger would not have data in INSERTED and DELETED tables, because instead of the original insert statement your INSTEAD OF tRIGGER is getting fired. The tables will be populated if you write a AFTER TRIGGER.