July 30, 2015 at 2:34 am
How can I constrain the table "Order Details" used to create the below view from being modified?The constrain need to be scripted in the view query below.
Usually when handling this question between more than 1 table, I use a foreign key constrain but I fail to do that with a view.
USE [Northwind]
GO
/****** Object: View [dbo].[Order Subtotals] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[Order Subtotals] AS
SELECT "Order Details".OrderID, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal
FROM "Order Details"
GROUP BY "Order Details".OrderID
GO
July 30, 2015 at 4:27 am
You can create the view using WITH SCHEMABINDING. That will prevent the table from being modified. Here's the CREATE VIEW syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2015 at 3:42 am
Thank you, that works
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply