Prevent delete on all tables

[m***@gmail.com]

Hi, please I would know if there's a way to prevent the deletion on all tables using the triggers (for example) without exceptions.

I tried "transaction rollback" but it throws an error.

Thanks,
Matteo

[Erland Sommarskog]

Post by m***@gmail.com
Hi, please I would know if there's a way to prevent the deletion on all
tables using the triggers (for example) without exceptions.
I tried "transaction rollback" but it throws an error.

If you invent your syntax, that usually results in an error. A vile trigger
could look like this:

CREATE TRIGGER nono_tri ON tbl INSTEAD OF DELETE AS
RAISERROR ('Deletes on this table are not permitted!', 16, 1)
ROLLBACK TRANSACTION
RETURN

You need to put this on every table. There is no provision for a general
database trigger that applies to all tables.

[m***@gmail.com]

Post by Erland Sommarskog

Post by m***@gmail.com
Hi, please I would know if there's a way to prevent the deletion on all
tables using the triggers (for example) without exceptions.
I tried "transaction rollback" but it throws an error.

If you invent your syntax, that usually results in an error. A vile trigger
CREATE TRIGGER nono_tri ON tbl INSTEAD OF DELETE AS
RAISERROR ('Deletes on this table are not permitted!', 16, 1)
ROLLBACK TRANSACTION
RETURN
You need to put this on every table. There is no provision for a general
database trigger that applies to all tables.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I used "ROLLBACK TRANSACTION" not "transaction rollback", but also with RAISEERROR if I launch the command using .NET I obtain an exception.

I want a silent operation, no deletetion without errors, to support
the legacy application.

Thanks,
Matteo

[Erland Sommarskog]

Post by m***@gmail.com
I want a silent operation, no deletetion without errors, to support
the legacy application.

OK. In that case, take out the RAISERROR and ROLLBACK TRANSACTION and
keep the RETURN. It will be very silent.

[m***@gmail.com]

Post by Erland Sommarskog

Post by m***@gmail.com
I want a silent operation, no deletetion without errors, to support
the legacy application.

OK. In that case, take out the RAISERROR and ROLLBACK TRANSACTION and
keep the RETURN. It will be very silent.

Perfect, it works but I had no doubt to find the solution here :)

Thanks,
Matteo

[rpresser]

Post by m***@gmail.com
Hi, please I would know if there's a way to prevent the deletion on all tables using the triggers (for example) without exceptions.
I tried "transaction rollback" but it throws an error.

"ROLLBACK TRANSACTION" is the proper term; but it is not really the appropriate way.

You may want to look at having people use the db_datareader role if they only need readonly access to the database. Or, if you need insert and update allowed and delete denied, you could use DENY DELETE on each table, which might be simpler than creating a trigger on each table.

[Erland Sommarskog]

Post by rpresser
You may want to look at having people use the db_datareader role if they
only need readonly access to the database. Or, if you need insert and
update allowed and delete denied, you could use DENY DELETE on each
table, which might be simpler than creating a trigger on each table.

DENY is not going to stop db_owner or sysadmin...

[rpresser]

Post by Erland Sommarskog
DENY is not going to stop db_owner or sysadmin...

Neither is the RETURN trigger, in a real sense; if you are dbo or sysadmin
you can remove the trigger, do your deletes, dump the transaction log,
erase the backups, then put back the trigger and get away scot free.

But I doubt stopping dbo or sysadmin is really necessary.

[Erland Sommarskog]

Post by rpresser

Post by Erland Sommarskog
DENY is not going to stop db_owner or sysadmin...

Neither is the RETURN trigger, in a real sense; if you are dbo or sysadmin
you can remove the trigger, do your deletes, dump the transaction log,
erase the backups, then put back the trigger and get away scot free.

Correct. However, the trigger can stop accidental deletes, or someone who
does know what he is doing.