Insert thru a view to a table with an IDENTITY property

Why doesn't my identity property function normally when I try to insert
through a view?
--I create base table with identity property
CREATE TABLE _t
(id int identity
,num int)
--then insert a value
INSERT _t(num) VALUES (1)
--create view on base table
CREATE VIEW t
AS
SELECT * FROM _t
--create trigger to insert from view into the base table
CREATE TRIGGER trg
ON t
INSTEAD OF INSERT, UPDATE
AS
INSERT _t(num)
SELECT num
FROM inserted
--now try to insert into view (w/o specifying an ident value)
INSERT t(num) VALUES (3)
--and get this error
-- Server: Msg 233, Level 16, State 2, Line 1
-- The column 'id' in table 't' cannot be null.
--now try to insert into view (w specifying an ident value)
INSERT t(id, num) VALUES (7,3)
SELECT * FROM t
--and this is the result
-- id num
-- -- --
-- 1 1
-- 2 3
Does anyone have any idea why the IDENTITY property is not functioning
properly?
IOW why it asking me to supply a value for the IDENTITY column in order to
do the insert and then when I supply it, it is ignored.
What am I missing?This is interesting...
As a workaround, omit the IDENTITY column from the view's query:
CREATE VIEW t
AS
SELECT num FROM _t
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||Dave
Yes, there is an issue with instead of trigger on view. scop_identity
function returns NULL
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:05338885-9863-42AE-A2FE-3BC68F94AD30@.microsoft.com...
> Why doesn't my identity property function normally when I try to insert
> through a view?
> --I create base table with identity property
> CREATE TABLE _t
> (id int identity
> ,num int)
> --then insert a value
> INSERT _t(num) VALUES (1)
> --create view on base table
> CREATE VIEW t
> AS
> SELECT * FROM _t
>
> --create trigger to insert from view into the base table
> CREATE TRIGGER trg
> ON t
> INSTEAD OF INSERT, UPDATE
> AS
> INSERT _t(num)
> SELECT num
> FROM inserted
>
> --now try to insert into view (w/o specifying an ident value)
> INSERT t(num) VALUES (3)
> --and get this error
> -- Server: Msg 233, Level 16, State 2, Line 1
> -- The column 'id' in table 't' cannot be null.
> --now try to insert into view (w specifying an ident value)
> INSERT t(id, num) VALUES (7,3)
> SELECT * FROM t
> --and this is the result
> -- id num
> -- -- --
> -- 1 1
> -- 2 3
>
> Does anyone have any idea why the IDENTITY property is not functioning
> properly?
> IOW why it asking me to supply a value for the IDENTITY column in order to
> do the insert and then when I supply it, it is ignored.
> What am I missing?|||On Wed, 26 Oct 2005 17:48:02 -0700, Dave wrote:

>Why doesn't my identity property function normally when I try to insert
>through a view?
Hi Dave,
That's because SQL Server checks if the NOT NULL constraint is violated
BEFORE the INSTEAD OF trigger is fired.
<speculation>
I *think* that this has an architectural reason. The new row(s) have to
be present in the "inserted" pseudo-table. This table has the same
structure as the table or view that the INSTEAD OF trigger is defined
for - up to and including nullability. That meanst that if a column
can't be NULL in the table (or view), there will be no space in the data
structure to represent whether a real value or a NULL was inserted.
And since SQL Server can't faithfully represent a NULL in the inserted
table that is passed to the INSTEAD OF trigger, it takes the safe route
and generates an error message.
</speculation>

>Does anyone have any idea why the IDENTITY property is not functioning
>properly?
It has nothing to do with the IDENTITY property, as explained above. If
you check Books Online, you'll find an example where a bogus value has
to be passed for a computed value in the view.

>IOW why it asking me to supply a value for the IDENTITY column in order to
>do the insert and then when I supply it, it is ignored.
>What am I missing?
You missed the discussion of a similar situation in Books Online, under
the heading "INSTEAD OF INSERT Triggers".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)