Ensuring a specific record NEVER gets deleted
Hi all. Quick question about triggers.
Is it possible to set up a trigger such that a specific record in a table NEVER can be deleted?
I'm writing a CMS and I want to set it up such that home pages cannot be deleted.
I guess it would be something like this:
CREATE TRIGGER is_home
BEFORE DELETE ON cms_pages FOR EACH ROW
EXECUTE PROCEDURE check_is_home(...params...);
The function will examine the row being deleted to see if the column is_home is set to 1 (true) or 0 (false) and then act accordingly.
Will doing this actually work? I haven't implemented any triggers on the database yet, I'm just going through everything now and seeing where I can clean up the code and this is one of the most important ones.
Any advice is greatly appreciated.
Thanks in advance,
it'll work as long as that one record matches the criteria you specify in the procedure. However, if other records match the same criteria that you have specified then they will also be exempt from deletion. You could potentially add another attribute that is unique on records to fully specify the ones you wish to keep around through the use of the trigger.
ps. triggers are scary! they can have adverse effects if written incorrectly so be careful!!!
Thanks for the tips.
I've actually created rules instead, which are much safer.
The records I wish to never be deleted are created when I create the database, so I can be certain that they will always be there, and the columns that I reference in the rules are primary keys, so there will never be more than one match.