CODEWITHSUNDEEP
sql-server
user2587986
user2587986

Reputation: 99

how to lock a field in table for update in sql

Need lock for update a field in a table or maybe pop up a message alter user when this field is update. But still need do be insert or delete a record. i simply try to use command

   DENY UPDATE ON JobEmp (Job) TO public

It will not let me do any thing to Job Column, can not add, change or delete. Need some help. Thanks Using Code

   CREATE TRIGGER tr_No_Update_Job
   ON dbo.JobEmp
   FOR UPDATE
   AS
   BEGIN
   IF UPDATE(Job)
    BEGIN
   RAISERROR('This column cannot be updated', 16,1)
   RETURN;
   END
   END

But when insert a new record, it also throw the error message. How can i only lock for update?

Upvotes: 1

Views: 5754

Answers (4)

dean
dean

Reputation: 10098

Do the rollback from after update trigger:

create trigger trJobEmpUpd
on JobEmp
after update
as
if update(Job)
  rollback

Upvotes: 1

evenro
evenro

Reputation: 2646

If your questions is about Oracle
There is a syntax "for update" that locks a record (not a field) https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805

-> For SQL server I think the "for update" syntax is "Select ... WITH (updlock)..."
http://technet.microsoft.com/en-us/library/aa213026(v=sql.80).aspx
Note that as been noted by @huMpty duMpty you have to be in a transaction for the lock to be held...

This syntax will issue a row lock on the data selected, which will be released only on commit.
other users will be able to query the data (depends on the DBMS) but not to modify it.

I am pretty sure most other DBMS has the same / similar syntax for locking selected query results, while selecting the data - which allow atomicity - what you got from the "select" will be locked, in the same command, and no one else can intervene in the middle.

If you want to permanently not allow access to a column (for select or update) you should use a different scheme, and allow permisions on a view of the data , or only select permission with stored procedures for editing the actualy data.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

You can not Grant, Deny or Revok permission on one column of the table you can either deny UPDATE permission on a table on sql server permissions level or you need to create a Trigger to control column level permission.

Table Level Permissions 

DENY UPDATE ON OBJECT::[Schema].[TableName] TO [PrincipalName];

Column Level Update Control

CREATE TRIGGER tr_No_Update_Job
ON dbo.JobEmp
FOR UPDATE
AS
BEGIN
  IF UPDATE(Job)
   BEGIN
       RAISERROR('This column cannot be updated', 16,1)
       RETURN;
   END
END

Upvotes: 1

bjnr
bjnr

Reputation: 3437

Have a look to BEGIN/COMMIT TRAN and transaction isolation levels.

Upvotes: 0

Related Questions