Posted by Hugo Kornelis on 07/06/06 22:34

On Thu, 06 Jul 2006 13:42:39 +0200, R.A.M. wrote:

>Please help.
>I have a table with single row. I need to allow only UPDATEs of the
>table, forbid INSERTs and DELETEs. How to achieve it?
>Thank you for information
>/RAM/

Hi RAM,

Dan already replied how to do this with GRANT and DENY. If you also must
keep the database owner and administrators from accidentally inserting
or deleting a row, add the following trigger:

CREATE TRIGGER NoInsertOrDelete
ON SingleRowTable
AFTER INSERT, DELETE
AS
IF @@ROWCOUNT = 0 RETURN
ROLLBACK TRANSACTION
RAISERROR ('Don''t add rows to or remove rows from this table!', 16, 1)
GO

--
Hugo Kornelis, SQL Server MVP

[Back to original message]