Preventing from delete

Question

I created a table.  i have all rights to delete it. How do i prevent the records from getting deleted, even from the owner of it?.

---

NSG12

Answer 1

You could always add a delete trigger and have it fail 100% of the time. Of course, this is not 100% foolproof because the trigger could get disabled.

JP

Answer 2

I suppose you could create an INSTEAD OF trigger on the table that does nothing:

CREATE TRIGGER MyTrigger ON MyTable INSTEAD OF DELETE AS
DECLARE @dummy int

You might want to put an error message or warning or something there... that's up to you.

But I'd wait to see what other opinions there are on the subject.  This may not be the "best practices" way of doing it.

---

--Brad

Answer 3

Why would you use an instead of trigger as opposed to a delete trigger?

JP

Answer 4

Do you mean "Instead of delete" trigger?

---

Kent Waldrop

Answer 5

Why would you use an instead of trigger as opposed to a delete trigger?

JP

Because a "regular" delete trigger WILL delete the records.

If I just said CREATE TRIGGER FOR DELETE, that implies CREATE TRIGGER AFTER DELETE, and by the time the trigger code runs, the deletion of the records happened already.

---

--Brad

Answer 6

Gotcha... Of course... My thought was that if I forced the delete trigger to fail, the records would not delete.

Thanks..

JP

Answer 7

I agree the instead of trigger does seem like a snuggle fit here.  The only thing I would change is you cannot make the trigger do nothing; otherwise, noone can delete from the table.  You would have to use susername to get the login requesting the delete and if it matches yours then delete otherwise disallow and return a message.

Something like below:

SET NOCOUNT ON
GO

USE [tempdb]
GO

IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
	DROP TABLE t;
END
GO

CREATE TABLE t(
id INT,
col CHAR(1)
);

INSERT INTO t VALUES (1,'a');
INSERT INTO t VALUES (2,'b');
GO

CREATE TRIGGER MyTrigger 
ON t 
INSTEAD OF DELETE 
AS
BEGIN
	IF SUSER_NAME() = 'MyUserName'
	BEGIN
		DELETE t
		FROM [t]
		INNER JOIN deleted d
			ON t.id = d.id
	END
	ELSE
	BEGIN
		RAISERROR('You do not have permission to perform this operation',16,1);
	END
END
GO

DELETE FROM t WHERE id = 1;
GO

SELECT * FROM t

---

http://jahaines.blogspot.com/

Answer 8

Another alternative might be to pass something through CONTEXT_INFO to allow for the delete. 

EDIT:

Maybe scratch the CONTEXT_INFO idea; this kind of stuff can get dicey.  I think Adam's check is more straight forward.

Answer 9

According to the documentation, this won't work against a TRUNCATE:

"Although a TRUNCATE TABLE statement is in effect a DELETE, it cannot activate a trigger because the operation does not log individual row deletions. However, only those with permissions on a table to execute a TRUNCATE TABLE need be concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE TABLE statement."

Answer 10

Referential Integrity.

Three steps:

1) CREATE a child TABLE with a COLUMN to match the PK in the main TABLE.
2) CREATE an AFTER INSERT TRIGGER to put any new records in the new TABLE.
3) CREATE an AFTER UPDATE TRIGGER to copy all UPDATEs to the new TABLE.

Answer 11

I thought about an alternative like this; however, I thought that in this case that the trigger presented fewer "moving parts".  But I am especially interested in other comments.

---

Kent Waldrop

Answer 12

True. I thought the same thing, and saw the TRIGGER answer which seemed fine.

However, as TRUNCATE will ignore the TRIGGER, this is not a complete solution. Only FK is, and nice thing too, it's there for the very purpose of RI.

Of course, the user could remove the child TABLE, but then again, he could remove the TRIGGER. :)

Answer 13

If you don't care about UPDATEs either, then you could move it to a read only filegroup.

Answer 14

There is not much you can do if you are trying to prevent a user whom has dbo permission from truncating a table.  A couple of things the OP can do to prevent a user from using truncate are: create an indexed view on the table, as this prevents any user from truncating the table.  You can then setup a database ddl trigger that  will prevent anyone other than yourself from dropping the indexed view and table.

---

http://jahaines.blogspot.com/

Answer 15

If you don't care about UPDATEs either, then you could move it to a read only filegroup.

I forgot about this option; I like putting my numbers table in either an RO database or an RO filegroup.  Good idea.

---

Kent Waldrop

Answer 16

A read-only file group is a good option, but only if the OP wants the data to remain static.  The OP says that he only wants to prevent users from deleting data. I take this as users can insert and update data, in the table. In this case, I do not believe that we want to move the data into a read-only file group.

---

http://jahaines.blogspot.com/

Answer 17

I fully agree.  As much as anything I wanted to jog my own memory on this one because even though this is something I do from time to time, this didn't cross my mind at all.  At my age forgetting / omitting things is vexing.

---

Kent Waldrop