Lock table row

Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
one else can make any changes to it; however, we'd like to allow others to
be able to still have read permissions during the lock. How do we accomplish
this? Thanks.What are you trying to do?
SQL Server will handle the locks for you. Althou you can specify by
hints if you want NOLOCK or ROWLOCK and such things.
If you want to lock a row manually i think that what you are looking
for is to either add a table where you can store the locks with a FK
that points to a certain post (PK) in the table you want to lock. Or
maybe just create a bit column to specify wheter the row is "locked" or
not.|||If row-level permissions exist, I've never heard of them. But, you could do
this
CREATE TRIGGER DontChangeThisRow ON [YourTable]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [YourTable] SET col1 = i.col1, col2 = i.col2 /*repeat as necessary */
FROM inserted i INNER JOIN [YourTable] y
ON y.primary_key_column = i.primary_key_column
WHERE
i.primary_key_column <> 'key value for row you want to preserve'
END
Here is a complete sample:
DROP TABLE yourtable
GO
CREATE TABLE [YourTable] (col1 int not null, col2 char(1))
GO
ALTER TABLE [YourTable] ADD CONSTRAINT PK_YourTable_col1
PRIMARY KEY (col1)
GO
INSERT INTO [YourTable] VALUES (1,'a')
INSERT INTO [YourTable] VALUES (2,'b')
INSERT INTO [YourTable] VALUES (3,'c')
INSERT INTO [YourTable] VALUES (4,'d')
INSERT INTO [YourTable] VALUES (5,'e')
CREATE TRIGGER DontChangeThisRow ON [YourTable]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [YourTable] SET col1 = i.col1, col2 = i.col2
FROM inserted i INNER JOIN [YourTable] y
ON y.col1 = i.col1
WHERE
i.col1 <> 1 --key value for row you want to preserve
END
UPDATE [YourTable] SET col2 = 'z' WHERE col1 = 4
SELECT col1, col2 FROM [YourTable]
UPDATE [YourTable] SET col2 = 'h' WHERE col1 = 1
SELECT col1, col2 FROM [YourTable]
"curious_Lee" wrote:

> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we accompli
sh
> this? Thanks.
>
>|||> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we
> accomplish this? Thanks.
Sounds like optimistic concurrency, no?|||BEGIN TRAN
SELECT @.par =MAX(blblbl) FROM Table WITH(UPDLOCK,HOLDLOCK) WHERE......
UPDATE Table SET col=@.par ......
COMMIT TRAN
It ensures that the data has not changed since you last read it.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:Oj6K1SqJGHA.1848@.TK2MSFTNGP12.phx.gbl...
> Hi, all. We've got a SQL Server 2000 database. We need to lock a row so no
> one else can make any changes to it; however, we'd like to allow others to
> be able to still have read permissions during the lock. How do we
> accomplish this? Thanks.
>|||Thanks, Patrik, Mark, Aaron, and Uri, for the great solutions. I think
either a transaction or an optimistic concurrency might do the trick.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uGO0W0vJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> BEGIN TRAN
> SELECT @.par =MAX(blblbl) FROM Table WITH(UPDLOCK,HOLDLOCK) WHERE......
> UPDATE Table SET col=@.par ......
> COMMIT TRAN
>
> It ensures that the data has not changed since you last read it.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:Oj6K1SqJGHA.1848@.TK2MSFTNGP12.phx.gbl...
>