Solved

Read Only REcord

Posted on 2010-08-26
9
520 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 30 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 35 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 35 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 400 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 Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

718 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