Inserting with a view

Question

Inserting with a view

SpectralGhost

Mr or Mrs. 500

Points: 533
More actions
October 14, 2011 at 1:33 pm

#249419

I am running into a problem with an updatable view. When I try to insert against the view, it is giving me errors because I am not specifying a required value on the table. I REALLY don't want to specify that value in my insert statement as that significantly changes the level of effort on the project I'm working on. My view defines that field's value in the WHERE clause of the view. Is there a way to make it use the key value from the view's where clause?
This shows the basic problem:
/*
DROP TABLE [dbo].[LinkObject]
DROP VIEW [dbo].[LinkTest]
*/
CREATE TABLE [dbo].[LinkObject](
[LinkTypeID] TINYINT NOT NULL,
[LinkID] INT NOT NULL IDENTITY(1,1),
[TestBit] BIT NOT NULL
)
GO
--There will be about 10 views similar to this view, but the LinkTypeID value in the where clause will change.
CREATE VIEW [dbo].[LinkTest]
AS
SELECT
[LinkID]
,[TestBit]
FROM [dbo].[LinkObject](NOLOCK)
WHERE [LinkTypeID]=1
GO
INSERT INTO [dbo].[LinkTest] ([TestBit]) VALUES(1)
Any help would be greatly appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'll need to create an INSTEAD OF TRIGGER on the view.

then the trigger body can use a cosntant, or lookup from another table, or your logic of reusing a passed value to handle the required not nulls behind the scenes.

--edit--

tested example:

CREATE TRIGGER TR_LinkTest_INSTEAD ON [dbo].[LinkTest]

INSTEAD OF INSERT

AS

INSERT INTO [dbo].LinkObject (LinkTypeID,[TestBit])

SELECT

1 AS LinkTypeID,

INSERTED.[TestBit]

FROM INSERTED

GO

INSERT INTO [dbo].[LinkTest] ([TestBit]) VALUES(1)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SpectralGhost Mr or Mrs. 500 Points: 533 More actions · Answer 2

SpectralGhost

Mr or Mrs. 500

Points: 533

October 14, 2011 at 1:44 pm

#1394842

Many, MANY thanks!

Performance Guard (Shehap) Ten Centuries Points: 1297 More actions · Answer 3

I do agree with Lowell , triggers could be a suitable solution here by that simple insert

But if huge data inserts took place there...Triggers might yield to significant performance degradation

Therefore , it is preferred to use default value =1 for [LinkTypeID] to save delays resulted by triggers

[font="Arial Black"]Performance Guard[/font]

[font="Courier New"]Just ask me to get 0 sec for all queries[/font]

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 4

Hi Joe,

CELKO (10/14/2011)
[...]
link_id INTEGER NULL IDENTITY(1,1),
IDENTITY is the count of physical insertions attempts on one machine. It is no more a part of ta valid data model than the color of ink the pen that filled out the forms. There Noobs that actually use this proprietary “feature” to mimic record numbers in a magnetic tape file or as pointers to fake non-RDBMS links. Awfull, isn't it?
[...]

I'm genuinely interested in why you're against using IDENTITY. Is it because they're not updateable? Or you object to integer PKs? Or ...? What would be preferred for a PK? A GUID? An int populated via some method other than using IDENTITY?

If there's a better way, I'd be curious to know what it is.

SpectralGhost Mr or Mrs. 500 Points: 533 More actions · Answer 5

@celko

The code I posted is a drastic simplification and incomplete implementation of the overall problem and doesn't show all of the relationships nor best practices. Thanks for you input though.

@Performance Guard (Shehap)

Triggers will be OK because there are few inserts.