[jediokie]

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?

[DarenR]

what do you mean by locking?

[jediokie]

DarenR, 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.

[RudiVisser]

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

[jediokie]

RudiVisser, 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!

[DarenR]

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.

[e_i_pi]

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