• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Read Only REcord

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
softbless
Asked:
softbless
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
dqmqCommented:
The easiest way is to use delete an update triggers that raise an exception if the row of interest is touched.
0
 
Om PrakashCommented:
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
 
MSSystemsCommented:
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!

 
dqmqCommented:
That is not a bad solution:

A read-only table for the single record and then a view to union the two tables.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
softblessAuthor Commented:
Let me see it
0
 
MSSystemsCommented:
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
 
MSSystemsCommented:
Do you require any more help?
0
 
softblessAuthor Commented:
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.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now