SQL: is it possible to write the string "Write Protect"?

Question

These might be silly questions, but can I protect some of the data in the SQL Server database from deleting it or updating it without setting user rights?

Is this for a default data string that can be referenced for default values?

thanks

Answer 1

One possible approach that I once used is described in my blog post:

“Suppose you need to follow the following business rule: contracts cannot be changed after you start working on them (suppose that this business works in an ideal world). You can use the ROWVERSION column, the stored computed and foreign key constraint to enforce this rule - Use ROWVERSION to enforce business rules

Answer 2

Do this with relational integrity - use NOT using triggers, because they are always a real pain to maintain after that (they have their place, not here). The integrity of the relationship will do everything you need.

Using relational integrity can be quite elegant, but what you need to do is slightly counterintuitive, so it's easy to skip.

Create the main table of the tblMain table using a numeric primary key. For simplicity, I checked this with a single-column table, intID, and I populated it with values ​​of 0.1 and 2.

Then create a second tblGuard table with the same numeric primary key. I added one row to this table, value 1.

Now a bit of inverse logic. Create a foreign key in the tblGuard table that references the tblMain table

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

The restriction ensures that a row with intID 1 cannot be deleted from the tblMain table, because the tblGuard table referential integrity requires that a value of 1 exist in tblMain. This works with deletion and truncation.

Answer 3

You can create a trigger that causes an error if this row is updated or deleted.

Answer 4

Say your MyTable is on Primary.

Put the first row in the new table MyTableReadOnly, move this table to your own group of files and make only a group of files.

Remove the first row from MyTable

Now create a view that

 SELECT Columns From MyTableNew UNION SELECT Columns From MyTable 

Access to everything through presentation. If you want to update or remove from the view, you can do it in MyTable and ignore anything for MyTableNew. If you want to work with a view, you can use INSTEAD-OF triggers.

Answer 5

• Create a second table that has rows with the same unique identifiers as the rows you are trying to protect.

• Permission that second table as you wish

• Add a trigger to the first table, which will delete / update both tables if the corresponding row exists in the second table.

Thus, if you do not have a variable in the second table, you will not be able to change the “related” rows, since the trigger will end due to violation of rights in the second table.

NOTE. The contrast of this method with other main methods (using VIEW) is that it makes it easy to maintain a set of "fixed" rows, unlike the VIEW approach, and avoids various performance problems typically associated with views.

Answer 6

I say this "programmatically." For example, let the line with identifier 1 always be the default line, and then add “WHERE id! = 1” to all UPDATE or DELETE queries or execute the equivalent in any language that you use to write your logic (PHP, C, VB, and etc.)