prevent DELETE and/or UPDATE

[Jon]

Hi all!
Are there any other way than using rights or Triggers to prevent a
DELETE or an UPDATE on a specific column.

The "problem" with rights is that they dont apply to all DB-users

The "problem" with triggers is that they generate lots of extra
SQL-code

I would like a solution something like below. If there are any
primitives like this or other more neat solutions I would be glad to
know

CREATE TABLE some_table NO DELETE
/* ^^^^^^^^^*/
(
some_column SOME_TYPE NO UPDATE
/* ^^^^^^^^^*/

)
For clarity, here is a trigger that currently solves the problem

CREATE TRIGGER check_updateable_columns ON some_table
FOR UPDATE
AS
IF UPDATE(some_column)
RAISERROR(...)
GO

or

CREATE TRIGGER delete_not_allowed ON some_table
INSTEAD OF DELETE
AS
RAISERROR(...)
GO

[David Portas]

I don't understand this statement:

The "problem" with rights is that they dont apply to all DB-users".

You can set DB permissions for whatever users you like. True, you can't
stop sysadmins from updating tables but that's not possible with
triggers or any other method either. Nor would it be a good idea since
corrective maintenance may be part of a DBA's role.

The problem with attempting to do this in a trigger is precisely that
there isn't an easy way to circumvent the trigger for ad-hoc
maintenance purposes without affecting all other users. A Trigger
containing only a RETURN statement should do for this if you must.

However, if you enforce the sensible practice of denying users all
permissions on tables and then performing updates only through procs
you won't have this problem.

--
David Portas
SQL Server MVP
--