Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Read Only REcord

Posted on 2010-08-26
9
Medium Priority
?
524 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
Suggested Courses

618 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