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.