?
Solved

Read Only REcord

Posted on 2010-08-26
9
Medium Priority
?
522 Views
Last Modified: 2012-06-27
Dear All,

Is there any way to make 1 record in a table to be read only, in  other words : that 1 record cannot be deleted, or updated.

Please help. Thanks
0
Comment
Question by:softbless
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 120 total points
ID: 33539216
The easiest way is to use delete an update triggers that raise an exception if the row of interest is touched.
0
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 140 total points
ID: 33539217
no i think
While making any changes in that table you can add validation for that record so that it cannot be updated or deleted.
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 33539260
I agree with the above mentioned. A trigger sounds like the easier solution. Alternatively you could add a second data file to the database with read only, but if I am not mistaken. That means you have to create an entire table for this record.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Expert Comment

by:dqmq
ID: 33539276
That is not a bad solution:

A read-only table for the single record and then a view to union the two tables.
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 140 total points
ID: 33541173
Hi dqmq,

I'll disagree on this one.  A view to union the two tables can be a performance dog.

The result of the view will be a derived table without indexing.  The view will result in a full-table scan of both tables to generate a derived table that is returned as the result set of the view.  Another full table scan will be required for any queries against the results returned by the view.


Kent
0
 

Author Comment

by:softbless
ID: 33689970
Let me see it
0
 
LVL 4

Accepted Solution

by:
MSSystems earned 1600 total points
ID: 33690394
I have added an example below
/* Create new file group */
USE [master]
ALTER DATABASE [DatabaseName] ADD FILEGROUP [ReadOnly]
GO
/* Add data file to that group */
USE [master]
ALTER DATABASE [DatabaseName] ADD FILE ( NAME = N'ReadOnly', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReadOnly.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ReadOnly]
GO

/* Create a table in the file group */
CREATE TABLE [ReadOnlyData] (
Name Varchar(50)
) on [ReadOnly]

/* Add some data */
USE [DatabaseName]
INSERT INTO [ReadOnlyData]
VALUES ('MyName')

/* Set the file group to read only */
USE [Master]
USE [DatabaseName]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'ReadOnly'
SELECT convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'ReadOnly'
if(@readonly=0)
	ALTER DATABASE [DatabaseName] MODIFY FILEGROUP [ReadOnly] READONLY
GO

/* Now if you try to change any data. (DELETE, UPDATE or INSERT for example) you will get the following result. */

USE [DatabaseName]
INSERT INTO [ReadOnlyData]
VALUES ('MyName')

/*
Returns :
Msg 652, Level 16, State 1, Line 1
The index "" for table "dbo.ReadOnlyData" (RowsetId 72057594549436416) resides on a read-only filegroup, which cannot be modified.
*/

/* But you will be able to query the data */
USE [DatabaseName]
SELECT *
FROM [ReadOnlyData]

/* If you require to change the data. You can just disable the read-only */
USE [DatabaseName]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'ReadOnly'
if(@readonly=1)
	ALTER DATABASE [Dev_Paforma] MODIFY FILEGROUP [ReadOnly] READWRITE
GO
USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'ReadOnly'
if(@readonly=1)
	ALTER DATABASE [Dev_Paforma] MODIFY FILEGROUP [ReadOnly] READWRITE
GO


/* 

You can also do all of this via the Management Studio. 
To enable read-only or read write.
Select the database properties. Go to File Groups and clear the check box next to the required file group that is labelled Read-only. Click ok

 */

Open in new window

Untitled.jpg
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 33738316
Do you require any more help?
0
 

Author Closing Comment

by:softbless
ID: 33855612
THanks all
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question