在 Sql Server 中创建只读视图 [英] Creating readonly views in Sql Server

问题描述

根据 MSDN，由简单选择组成的视图自动允许您在表上使用插入/更新/删除语句.有没有办法防止这种情况——告诉Sql Server视图是只读的，不能用它来修改表?

According to MSDN, views composed of simple selects automatically allow you to use insert/update/delete statements on the table. Is there a way to prevent this - to tell Sql Server that the view is readonly, and you can't use it to modify the table?

推荐答案

最好的方法是删除视图上的 UPDATE/DELETE/INSERT 权限.

The best way would be to remove UPDATE/DELETE/INSERT permissions on the View.

除此之外，您可以在视图上创建一个 INSTEAD OF 触发器，该触发器根本不会使更新静默失败，或者有 相当多的构造使视图不可更新.所以你可以选择一个不改变语义或效率的，然后违反它.

Apart from that you could create an INSTEAD OF trigger on the view that simply does nothing to have the updates silently fail or there are quite a few constructs that make views non updatable. So you can pick one that doesn't change semantics or efficiency and then violate it.

以下内容似乎符合要求.

The below seems to fit the bill.

CREATE VIEW Bar
AS
SELECT TOP 100 PERCENT x
FROM foo
WITH CHECK OPTION

这篇关于在 Sql Server 中创建只读视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！