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. What do you mean by 'redirect the references to the renamed table dbo.productsxy'. If anyone has a less intrusive idea, please let me know. I was hoping to achieve that without touching all the referencing tables, views, functions etc. The only way around seems to redirect the references to the renamed table dbo.productsxy. 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. SELECT 'dbo.productsxy - base table' AS _object , IF OBJECT_ID(N'dbo.products') IS NOT NULLĬREATE TRIGGER dbo.products_trg_instead_insert ON dbo.products IF OBJECT_ID(N'dbo.productsxy') IS NOT NULLĬREATE TABLE dbo.notSupportedProducts ( id INT ) ĪLTER TABLE dbo.notSupportedProducts ADD CONSTRAINT FOREIGN KEY (id) REFERENCES dbo.productsxy(id) IF OBJECT_ID(N'dbo.notSupportedProducts') IS NOT NULL Here is a code sample that demonstrates the technique showing how the INSTEAD OF INSERT trigger would work. Has anyone an idea, getting around that limitation? Or an alternative that is (almost) transparent to the application?Ī 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. Unfortunately I have a couple of functions, procedures, triggers and tables (foreign keys) that refer to that products table, so that idea got stuck.

Just rename the original table to productsxy and call the view products, so that there is no need for change in the application. I thought about creating a view that does exactly that. Now, transparent for the application, all calls to the product table need to omit the rows that are in the notSupportedProducts table. That table will only contain an ID, referencing the product table.

notSupportedProducts) that contains products that aren't supported anymore. The table in question contains products, now I want to introduce a new table (e.g. I don't want to embark on changing all the sql queries referencing the table I have to rework: I have a legacy DB with a lot of code relying to that DB structure. I wonder if there is an alternative for that. I know that you cannot reference a view in a foreign key.

Iq View Keygen