Solved

Read Only REcord

Posted on 2010-08-26
9
505 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Assisted Solution

by:Kdo
Kdo 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now