Solved

Read Only REcord

Posted on 2010-08-26
9
516 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql date time 14 50
MYSQL responding very slow 3 63
Generate PDF from MySQL using PHP 3 68
Mysql how to execute a commands file 5 38
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 …
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
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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