Locking a record against deletion.

By: Ben Matterson

Abstract: How to ensure that a record is not deleted from a table.

How can I lock a record in a table such that nobody else can delete this record?

You can store the username of the "owner" of the record in the table and then write a BEFORE DELETE trigger which checks the username and raises an exception if CURRENT_USER is anyone other than the owner of the record. This will allow only the owner of the record to delete the record.

If you change the owner field to some value that is not the username of any database user, then nobody accessing the database will be able to delete the record, including the person who inserted the record.

Server Response from: ETNASC03