6 Replies - 773 Views - Last Post: 06 August 2012 - 02:13 PM

#1 jediokie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 215
  • Joined: 07-February 08

Locking data

Posted 06 August 2012 - 07:45 AM

I have a date field in a table. I need to lock it after it is initially populated. Is this possible within MS SQL or do I need to look at it programmaticly?
Is This A Good Question/Topic? 0
  • +

Replies To: Locking data

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 386
  • View blog
  • Posts: 2,657
  • Joined: 12-January 10

Re: Locking data

Posted 06 August 2012 - 07:48 AM

what do you mean by locking?
Was This Post Helpful? 0
  • +
  • -

#3 jediokie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 215
  • Joined: 07-February 08

Re: Locking data

Posted 06 August 2012 - 07:49 AM

View PostDarenR, on 06 August 2012 - 08:48 AM, said:

what do you mean by locking?

Making it read only. It's a date field that shows when a task/item was completed and we need it to be set in stone. Once they say the task/item is done, that's it.
Was This Post Helpful? 0
  • +
  • -

#4 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1001
  • View blog
  • Posts: 3,555
  • Joined: 05-June 09

Re: Locking data

Posted 06 August 2012 - 07:54 AM

What happens if you simply don't modify it?

There's no real way (in any database engine as far as I'm aware) to lock a row natively, but it can be done by the likes of a stored procedure that would be called to update the row, and either only updates certain columns on the row, or detects if the date field has been changed and rolls it back in that case.

This post has been edited by RudiVisser: 06 August 2012 - 07:54 AM

Was This Post Helpful? 2
  • +
  • -

#5 jediokie  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 215
  • Joined: 07-February 08

Re: Locking data

Posted 06 August 2012 - 08:01 AM

View PostRudiVisser, on 06 August 2012 - 08:54 AM, said:

What happens if you simply don't modify it?

There's no real way (in any database engine as far as I'm aware) to lock a row natively, but it can be done by the likes of a stored procedure that would be called to update the row, and either only updates certain columns on the row, or detects if the date field has been changed and rolls it back in that case.

Thanks! I kinda figured it would have to be done in the stored procedure. Now to learn more about stored procedure verbiage!
Was This Post Helpful? 0
  • +
  • -

#6 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 386
  • View blog
  • Posts: 2,657
  • Joined: 12-January 10

Re: Locking data

Posted 06 August 2012 - 08:37 AM

You can make an extra table with data types. Mkae one called read only and bring that into your stored procedure. This will save much time in the future if you want to do this for other columns or fields.
Was This Post Helpful? 1
  • +
  • -

#7 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: Locking data

Posted 06 August 2012 - 02:13 PM

As far as I know, there's two ways of doing this. The first is to simply revoke update rights on the column, like so:
REVOKE UPDATE (MyLockedField)
ON MyTable
FROM UserRole


Of course, you'll have to replace the lower-case values in the above statement. This solution is messy, as it will generate errors, and will prevent an entire UPDATE statement from executing if it includes that field.

The other way is to create an INSTEAD OF trigger, which is slightly more complex, but is a much more elegant solution, and doesn't impinge on the security/permissions of your database:
CREATE TRIGGER MyTable_MyUpdateTrigger
ON MyTable
INSTEAD OF UPDATE
AS
BEGIN
	UPDATE MyTable SET
		MyField2 = i.MyField2,
		MyField3 = i.MyField3,
		MyField4 = i.MyField4
	FROM MyTable
	INNER JOIN inserted AS i ON MyTable.MyKeyField = i.MyKeyField
END


As with the first solution, everything in lower-case you need to replace except for inserted. I haven't gone into using triggers for this type of work, but I'm aware of their existence, and as far as I know the word inserted is essentially a keyword in this instance.

You can of course INNER JOIN on multiple conditions, so if you have a composite key instead of a primary key, you can declare multiple join clauses.

Anything within the BEGIN..END block will be executed, so it is indeed possible to enforce conditional "locking" of data using IF statements and so on.

There is a great article on using INSTEAD OF in MSSQL located here.

This post has been edited by e_i_pi: 06 August 2012 - 02:15 PM

Was This Post Helpful? 2
  • +
  • -

Page 1 of 1