May 19, 2011 at 6:06 pm
I know that you cannot reference a view in a foreign key. I wonder if there is an alternative for that.
Background:
I have a legacy DB with a lot of code relying to that DB structure. I don't want to embark on changing all the sql queries referencing the table I have to rework:
The table in question contains products, now I want to introduce a new table (e.g. notSupportedProducts) that contains products that aren't supported anymore. That table will only contain an ID, referencing the product table.
Now, transparent for the application, all calls to the product table need to omit the rows that are in the notSupportedProducts table.
I thought about creating a view that does exactly that. Just rename the original table to productsxy and call the view products, so that there is no need for change in the application.
Unfortunately I have a couple of functions, procedures, triggers and tables (foreign keys) that refer to that products table, so that idea got stuck. Has anyone an idea, getting around that limitation? Or an alternative that is (almost) transparent to the application?
Cheers
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 19, 2011 at 7:18 pm
A view named dbo.products with three INSTEAD OF triggers on it (one for INSERT, one for UPDATE and one for DELETE) may be what you are after.
Here is a code sample that demonstrates the technique showing how the INSTEAD OF INSERT trigger would work.
USE tempdb
GO
IF OBJECT_ID(N'dbo.notSupportedProducts') IS NOT NULL
DROP TABLE dbo.notSupportedProducts
GO
IF OBJECT_ID(N'dbo.productsxy') IS NOT NULL
DROP TABLE dbo.productsxy
GO
CREATE TABLE dbo.productsxy
(
id INT PRIMARY KEY ,
name VARCHAR(100)
) ;
CREATE TABLE dbo.notSupportedProducts ( id INT ) ;
GO
ALTER TABLE dbo.notSupportedProducts ADD CONSTRAINT [fk_productsxy] FOREIGN KEY (id) REFERENCES dbo.productsxy(id) ;
GO
IF OBJECT_ID(N'dbo.products') IS NOT NULL
DROP VIEW dbo.products
GO
CREATE VIEW dbo.products
AS
SELECT *
FROM dbo.productsxy ;
GO
CREATE TRIGGER dbo.products_trg_instead_insert ON dbo.products
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.productsxy
( id, name )
SELECT *
FROM inserted ;
END
GO
INSERT INTO dbo.products
( id, name )
VALUES ( 1, 'some product' ) ;
GO
SELECT 'dbo.productsxy - base table' AS _object ,
id ,
name
FROM dbo.productsxy
UNION ALL
SELECT 'dbo.products - view' ,
id ,
name
FROM dbo.productsxy
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 7:40 pm
Thanks for the snippet,
It's about what I tried when I hit the problem that the existing references to the table dbo.product now have to reference a view in a foreign key, which is not possible in MSSQL. The only way around seems to redirect the references to the renamed table dbo.productsxy. I was hoping to achieve that without touching all the referencing tables, views, functions etc.
If anyone has a less intrusive idea, please let me know.
Cheers
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 19, 2011 at 7:49 pm
dankwart menor (5/19/2011)
Thanks for the snippet,It's about what I tried when I hit the problem that the existing references to the table dbo.product now have to reference a view in a foreign key, which is not possible in MSSQL. The only way around seems to redirect the references to the renamed table dbo.productsxy. I was hoping to achieve that without touching all the referencing tables, views, functions etc.
If anyone has a less intrusive idea, please let me know.
Cheers
What do you mean by "redirect the references to the renamed table dbo.productsxy". When you rename the table from dbo.products to dbo.productsxy all the existing keys and key references will stay in place, which I think is a good thing as the rename will be transparent once you add the VIEW.
Maybe this will make more sense. Try this code. I have taken the next step by redefining the VIEW a little closer to what you will want to have in place and by adding more example data it will become more clear:
USE tempdb
GO
IF OBJECT_ID(N'dbo.notSupportedProducts') IS NOT NULL
DROP TABLE dbo.notSupportedProducts
GO
IF OBJECT_ID(N'dbo.productsxy') IS NOT NULL
DROP TABLE dbo.productsxy
GO
CREATE TABLE dbo.productsxy
(
id INT PRIMARY KEY ,
name VARCHAR(100)
) ;
CREATE TABLE dbo.notSupportedProducts ( id INT ) ;
GO
ALTER TABLE dbo.notSupportedProducts ADD CONSTRAINT [fk_productsxy] FOREIGN KEY (id) REFERENCES dbo.productsxy(id) ;
GO
IF OBJECT_ID(N'dbo.products') IS NOT NULL
DROP VIEW dbo.products
GO
CREATE VIEW dbo.products
AS
SELECT p.*
FROM dbo.productsxy p
LEFT JOIN dbo.notSupportedProducts nsp ON p.id = nsp.id
WHERE nsp.id IS NULL ;
GO
CREATE TRIGGER dbo.products_trg_instead_insert ON dbo.products
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.productsxy
( id, name )
SELECT *
FROM inserted ;
END
GO
INSERT INTO dbo.products
( id, name )
VALUES ( 1, 'some product' ),
( 2, 'some other product' ) ;
GO
INSERT INTO dbo.notSupportedProducts
( id )
VALUES ( 1 ) ;
GO
SELECT 'dbo.productsxy - base table' AS _object ,
id ,
name
FROM dbo.productsxy
UNION ALL
SELECT 'dbo.products - view' ,
id ,
name
FROM dbo.products
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 9:25 pm
What I mean is that there are tables that reference dbo.products via foreign key at the moment. If I rename the table (using sp_rename), the foreign keys will still point to dbo.products, won't they? And as that is a view now, it will crash.
is there a reference-aware way to rename?
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 19, 2011 at 9:35 pm
What would the 'instead of update' and 'instead of delete' trigger look like? Insert is pretty straight forward...
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 19, 2011 at 10:16 pm
dankwart menor (5/19/2011)
What I mean is that there are tables that reference dbo.products via foreign key at the moment. If I rename the table (using sp_rename), the foreign keys will still point to dbo.products, won't they?
No, they will continue to point to the original object with the new name, i.e. dbo.productsxy will still be the table referenced by the foreign keys after the rename. Creating a new VIEW after the rename called dbo.products will not affect dbo.productsxy or any of its keys.
is there a reference-aware way to rename?
Renames do not affect keys or key references, it is a data-dictionary name change only, so references are unaffected.
What would the 'instead of update' and 'instead of delete' trigger look like? Insert is pretty straight forward...
In the INSTEAD OF UPDATE trigger you would do an UPDATE...JOIN between the inserted table and dbo.productsxy on the primary key to update any affected rows in the base table. Something like:
UPDATE p
SET name = i.name,
some_other_field = i.some_other_field
FROM inserted i
-- JOIN always works assuming id is your PK and that no one ever updates that
-- field...if the PK is ever updated it gets more complicated as you have to
-- involve the deleted table too
JOIN dbo.productsxy p on i.id = p.id ;
Same principals used in the INSTEAD OF UPDATE trigger holds true for the INSTEAD OF DELETE trigger. If you get stuck building those two triggers post what you have and I or someone will help you along.
Edit: PS if you do post trigger code down the line please be sure to also post your table definitions and some sample data to support remote development efforts on this side 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 19, 2011 at 10:24 pm
Thanks, mate
I will work on that.
Cheers
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 22, 2011 at 6:04 pm
If I do it like that:
UPDATE pSET name = i.name,
some_other_field = i.some_other_field
FROM inserted i
JOIN dbo.productsxy p on i.id = p.id ;
And include every single field that exists in the table p, what happens to the some_other_field field if I say
UPDATE products SET name = "test"
Will it stay untouched? Or will it be overriden by null or the default value? Is that what the JOIN is for?
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 23, 2011 at 10:32 am
dankwart menor (5/22/2011)
If I do it like that:
UPDATE pSET name = i.name,
some_other_field = i.some_other_field
FROM inserted i
JOIN dbo.productsxy p on i.id = p.id ;
And include every single field that exists in the table p, what happens to the some_other_field field if I say
UPDATE products SET name = "test"
Will it stay untouched? Or will it be overriden by null or the default value? Is that what the JOIN is for?
You want to look at the UPDATE() function in SQL Server. The function is useful in the context of an update trigger to determine whether the caller explicitly supplied a value for a particular field that exists in the inserted table.
Your trigger can contain a single UPDATE JOIN statement that addresses all columns in the table and conditionally updates each field depending on whether the caller supplied a value. It might look something like this:
UPDATE p
SET name = CASE WHEN UPDATE(name) = 1 THEN i.name
ELSE p.name
END,
some_other_field = CASE WHEN UPDATE(some_other_field) = 1 THEN i.some_other_field
ELSE p.some_other_field
END
FROM inserted i
JOIN dbo.productsxy p ON i.id = p.id ;
Basically this is saying for each column "if the caller supplied a value for the column then update the table with it, otherwise update the table with what was already there (i.e. no effective data change)".
Is that what the JOIN is for?
You're almost there! There is a distinct data-flow in play here that you need to understand. If you got this part great, but I want to make sure, so here it is restated:
When the caller updates the VIEW dbo.products no data changes are actually taking place because you have the INSTEAD OF INSERT trigger in place. The only thing that happens via the issuance of the UPDATE statement from the caller is that the virtual inserted and deleted tables are populated with the data changes that would take place on the data exposed by the VIEW if the VIEW were updatable. It is up to you, in the context of the INSTEAD OF trigger, to take those data changes from the virtual tables and do something useful with them. The UPDATE...JOIN bridges the gap between the virtual tables available inside the trigger and the concrete table dbo.productsxy.
Edit: modified code sample, UPDATE() returns bit so must say "WHEN UPDATE(name) = 1 THEN" instead of just "WHEN UPDATE(name) THEN"
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 5:02 pm
Thanks, that is working exquisitely
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]
May 23, 2011 at 5:43 pm
dankwart menor (5/23/2011)
Thanks, that is working exquisitely
Excellent! I love it when a plan comes together 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 23, 2011 at 8:48 pm
opc.three rocks it again!
May 23, 2011 at 8:59 pm
Thanks for the nod Yin :Wow:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply