SQL: "Write Protect" row of data possible?

Question

May be a stupid questions but can I protect a row of data in a SQL Server database from being deleted or updated without setting user permissions?

This is for a default row of data that can be referenced for its default values?

Thanks

Answer 1

Do this by relational integrity - do NOT use triggers as they're always a real pain to maintain afterwards (they have their place, just not here). Relation integrity will do everything you need.

Using relational integrity can be quite elegent, but what you need to do is slightly counter-intuitive so easily missed.

Create your main table table, tblMain, with a numeric primary key. For simplicity I tested this with a table with one column, intID, and I populated it with values 0,1 and 2.

Next create a second table, tblGuard, with a similar numeric primary key. I added one row into this table, value 1.

Now the reverse logic bit. Create a foreign key on the tblGuard table that reference the tblMain table

ALTER TABLE [dbo].[tblGuard] ADD 
    CONSTRAINT [FK_tblGuard_tblMain] FOREIGN KEY 
    (
        [intID]
    ) REFERENCES [dbo].[tblMain] (
        [intID]
    )

The constraint will ensure that the row with intID value 1 cannot be deleted from the tblMain table because the tblGuard table referential integrity requires that the value 1 exists in tblMain. This works with deletes and truncates.