preventing update thru view

Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgsHi
Yes, you can
As far as I know there are two ways to accomplish that
1) CREATE VIEW ... WITH VIEW_METADATA
2) CREATE TRIGGER ...INSTEAD OF UPDATE
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
> Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
> thks & rdgs|||On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
Hi Maxzsim,
CREATE TRIGGER DontUpdate
ON MyView
INSTEAD OF INSERT, UPDATE, DELETE
AS
RAISERROR ('This view is read-only', 16, 1)
ROLLBACK TRANSACTION
go
Note: the rollback isn't even necessary, as this trigger is defined as an
"instead of" trigger. Without the rollback, the attempt to update the view
will be disregarded but the rest of the transaction will stick; with the
rollback, the complete transaction will be rolled back. To see this
difference, try the following code with both versions of the trigger:
BEGIN TRANSACTION
UPDATE SomeOtherTable
SET SomeThing = SomeThingElse
WHERE Whatever = WhatYouLike
UPDATE MyView
SET YouNameIt = YouGotIt
WHERE Foo = Bar
COMMIT TRANSACTION
SELECT SomeThing
FROM SomeOtherTable
WHERE Whatever = WhatYouLike
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>--Original Message--
>Hi ,
> A view can actually update the data of a table.
> Is there any way for me to create a read-only view ?
>thks & rdgs
>.
>|||Hugo
If you have a big update transaction I would not use a trigger with
rollback.
Instead
create table t1 (col1 int,col2 int)
insert into t1 values (1,11)
insert into t1 values (8,10)
select * from t1
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1+0 AS col1,
col2+0 AS col2
FROM T1
select * from v1
--error
update v1 set col1=100 where col2=11
go
drop table t1
drop view v1
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5ll8i0tvdhfblupp7cbfoaakaqaj1jn4rb@.4ax.com...
> On Wed, 18 Aug 2004 23:31:54 -0700, maxzsim wrote:
> >Hi ,
> >
> > A view can actually update the data of a table.
> >
> > Is there any way for me to create a read-only view ?
> >
> >thks & rdgs
> Hi Maxzsim,
> CREATE TRIGGER DontUpdate
> ON MyView
> INSTEAD OF INSERT, UPDATE, DELETE
> AS
> RAISERROR ('This view is read-only', 16, 1)
> ROLLBACK TRANSACTION
> go
> Note: the rollback isn't even necessary, as this trigger is defined as an
> "instead of" trigger. Without the rollback, the attempt to update the view
> will be disregarded but the rest of the transaction will stick; with the
> rollback, the complete transaction will be rolled back. To see this
> difference, try the following code with both versions of the trigger:
> BEGIN TRANSACTION
> UPDATE SomeOtherTable
> SET SomeThing = SomeThingElse
> WHERE Whatever = WhatYouLike
> UPDATE MyView
> SET YouNameIt = YouGotIt
> WHERE Foo = Bar
> COMMIT TRANSACTION
> SELECT SomeThing
> FROM SomeOtherTable
> WHERE Whatever = WhatYouLike
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||How about using permissions, to control access to this view. You can have a
view, and grant only SELECT permissions on that view to your users.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"maxzsim" <anonymous@.discussions.microsoft.com> wrote in message
news:1b1c01c485b6$3856ef20$a301280a@.phx.gbl...
Hi ,
A view can actually update the data of a table.
Is there any way for me to create a read-only view ?
thks & rdgs