469,090 Members | 1,099 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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
Jul 23 '05 #1
1 6265
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
--

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Alex | last post: by
16 posts views Thread by robert | last post: by
1 post views Thread by KEVIN97810 | last post: by
5 posts views Thread by rn5a | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.