October 14, 2011 at 1:33 pm
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!
October 14, 2011 at 1:37 pm
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
October 14, 2011 at 1:44 pm
Many, MANY thanks!
October 15, 2011 at 2:13 pm
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]
October 15, 2011 at 7:50 pm
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.
October 17, 2011 at 6:57 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply