database locking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

database locking

Author

Topic

marlon1300
Starting Member

2 Posts

Posted - 2009-11-19 : 00:58:10

good day sql masters, i am needing your help regarding the database locking, the problem is because of the illegal editing/changing data of the encoders in their from workstation which result to a wrong year end report. we are using ms access and sql server 200 for this.

everyday we are storing 200 data average, then at the end of the week we summarized it and make weekly,monthly and yearly report, but then when we are in the end of the month or a quarter, some of the data has been changed so, the reason is because encoders edit entries from the previous week, we tried to solve this problem by removing the update/edit button on their station but they find another way, delete the existing entry the add a new one on the same date replacing the old one. because of this, our final report was messed up. we summarized it on a weekly basis but in the end of the month data were never same.

i want to know how to lock data that were stored from the start in a weekly period, which they cannot add or edit anymore, in this manner even if they encountered mistake in the current week they can still change it within the current week but they cannot modify data from the previous weeks or months, but it can be unlocked through the server or another unit which monitors or control the whole system so we can have the control of the stored data.

thanks,

shaggy
Posting Yak Master

248 Posts

Posted - 2009-11-19 : 01:11:29

why database locking
database cannot be locked
we can make db readonly\take offline
In your scenario create trigger on tables that are not to be updated

see BOL for implementing trigger for tables

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 01:27:30

I would add a column to the tables that you want to "block" certain data that will have a value of 0 or 1. Name it Updateable or something similar. Have the default be 1 and then have a job update the column to 0 for the rows that should no longer be updated.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

marlon1300
Starting Member

2 Posts

Posted - 2009-11-19 : 10:32:41

thanks for your reply,

is there any way to lock data per period of time and not per data entry?

im using sql server 2000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 11:22:40

You would schedule a SQL job to do the update of that column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-19 : 11:24:31

You have temporal problems - nothing to do with Star Trek!
I strongly suggest you google 'temporal databases' and look at sorting out the design of the db.

Pro Tem triggers may help. Something like the following should only allow the user 'sa' to make changes to previous data:


SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO
CREATE TRIGGER YourTable_I
ON YourTable
FOR INSERT
AS

SET NOCOUNT ON

IF SUSER_SNAME() NOT IN ('SA')
	AND EXISTS
	(
		SELECT *
		FROM inserted
		WHERE DateAdded < DATEADD(week, DATEDIFF(week, 6, CURRENT_TIMESTAMP), 6)
	)
BEGIN
	RAISERROR('Only changes to this week allowed.', 16, 1)
	ROLLBACK
END
GO
CREATE TRIGGER YourTable_UD
ON YourTable
FOR UPDATE, DELETE
AS

SET NOCOUNT ON

IF SUSER_SNAME() NOT IN ('SA')
	AND EXISTS
	(
		SELECT *
		FROM deleted
		WHERE DateAdded < DATEADD(week, DATEDIFF(week, 6, CURRENT_TIMESTAMP), 6)
	)
BEGIN
	RAISERROR('Only changes to this week allowed.', 16, 1)
	ROLLBACK
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources