INSTEAD TRIGGER return zero number into OUTPUT identity - by Fabio Lunardon

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 427666 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 3/27/2009 9:43:07 AM
Access Restriction Public

Description

The team was taken in deception on the fact that un'identity can not be null,
this is true only in the table, but outside, before being assigned
the value identity it is unknown, and therefore NULL. 
Certainly can not be zero an unknown value
(Is the same used in the ambiguity of the poem Ulysses, which was presented as "None" to Polifemo ...)
Sign in to post a comment.
Posted by Fabio Lunardon on 1/20/2010 at 12:11 AM
In programming, do not count the values that are used,
but the meaning and the information they carry.

The choice of using a defined value when a column can not contain NULL
should be done only when the value is actually inserted into the column.

Return 0 in an identity is not an approximation to NULL,
because there is no metric compared to a NULL value.
0 is not a special value; the fact that 1 is often used as start value for an identity column
it is just a mental conditioning. If identity starts at -1000, 0 is a value as another.

Then, return 0 in the case an INSTEAD TRIGGER does not bring reliable information,
conversely, NULL brings the information that the inserted id it is unknown.

I suggest to add a setting of the server or database or session,
which allows to maintain the current operation (returns 0) or the most correct way (returns NULL)

Fabio
Posted by Microsoft on 1/18/2010 at 4:52 PM
Hi Fabio,

The OUTPUT clause outputs the same content that the source of the INSERT is inserting. In this case, the source of the INSERT is the DEFAULT VALUES clause. Normally, DEFAULT VALUES clause would attempt to insert NULL into all columns. I am saying "attempt", because this would lead to failure in case of non-nullable columns. Having an INSTEAD OF trigger on the table presents an interesting challenge for non-nullable columns: should the corresponding column in the "inserted" table be also non-nullable, or nullable? If non-nullable, what should be the contents? Back in SQL Server 2000, the decision has been made to not fail the INSERT in the first place in case of INSTEAD OF trigger (it is up top the trigger to fail/succeed). Unfortunately, the behavior is different in case of identity vs non-identity columns (remember we are only talking about non-nullable columns here). In case of a non-identity column, the corresponding column of the "inserted" table will be nullable and NULL will be inserted. In case of the identity column, SQL Server will insert 0 as the best approximation of NULL for a non-nullable integer column. We are reluctant to fix this non-uniformity because there may be application by now that rely on both these behaviors.

Eugene Zabokritski, SQL Engine team
Posted by Microsoft on 4/1/2009 at 2:17 PM
Hi Fabio,
Thanks for reporting the issue. We will investigate the problem and let you know about our resolution. For now, you could use @@IDENTITY to retrieve the identity value. This has restrictions though and it depends on what the trigger does. This will also not work if you want to retrieve multiple identity values.

--
Umachandar, SQL Programmability Team
Posted by Steve Kass on 3/29/2009 at 9:20 PM
I think this is a bug, and I think INSERTED.id should be NULL, not 0. NULL would be consistent with the similar situation involving a computed column, which is adapted from an example in BOL.

USE AdventureWorks2008;
GO
CREATE VIEW dbo.vw_ScrapReason AS (
SELECT ScrapReasonID, Name, ModifiedDate, ScrapReasonID-1 AS SRLess1
FROM Production.ScrapReason
);
GO
CREATE TRIGGER dbo.io_ScrapReason
    ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--ScrapReasonID is not specified in the list of columns to be inserted
--because it is an IDENTITY column.
    INSERT INTO Production.ScrapReason (Name, ModifiedDate)
        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,
             INSERTED.ModifiedDate
    SELECT Name, getdate()
    FROM inserted;
END
GO

DECLARE @t TABLE (i INT, j INT);
BEGIN TRAN
INSERT vw_ScrapReason (Name, ModifiedDate, SRLess1)
OUTPUT INSERTED.ScrapReasonID, INSERTED.SRLess1 INTO @t
VALUES (N'My scrap reason','20030404',1);
INSERT vw_ScrapReason (Name, ModifiedDate)
OUTPUT INSERTED.ScrapReasonID, INSERTED.SRLess1 INTO @t
VALUES (N'Next scrap reason','20030404');
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
OUTPUT INSERTED.ScrapReasonID, INSERTED.SRLess1 INTO @t
VALUES (-1,N'Third scrap reason','20030404');
ROLLBACK

SELECT * FROM @t;
GO
DROP VIEW dbo.vw_ScrapReason;
/* Results
ScrapReasonID Name                                             ModifiedDate
------------- -------------------------------------------------- -----------------------
34            My scrap reason                                    2009-03-30 00:20:11.433

ScrapReasonID Name                                             ModifiedDate
------------- -------------------------------------------------- -----------------------
35            Next scrap reason                                 2009-03-30 00:20:11.437

ScrapReasonID Name                                             ModifiedDate
------------- -------------------------------------------------- -----------------------
36            Third scrap reason                                 2009-03-30 00:20:11.440

i         j
----------- -----------
0         1
0         NULL
-1         NULL
*/