Ensuring a specific record NEVER gets deleted

[pgosse]

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,

Pablo

 

[ceco]

you can do something like that (if your table is cms_pages and the field is is_home - it can be bool or interger)

CREATE OR REPLACE RULE cms_pages_delete AS ON DELETE TO cms_pages WHERE OLD.is_home DO INSTEAD NOTHING;
-- this is with is_home bool

CREATE OR REPLACE RULE cms_pages_delete AS ON DELETE TO cms_pages WHERE OLD.is_home != 0 DO INSTEAD NOTHING;
-- this is with is_home integer

 

[pgosse]

Gracias!!!!!!!!!!!

That will work perfectly.

I will also need to ensure that certain that records which are created when the database is created are never deleted, so I assume that the following would work as well:

CREATE OR REPLACE RULE cms_root_page_delete AS ON DELETE TO cms_pages WHERE OLD.p_id = 1 DO INSTEAD NOTHING

Thanks again. This is very helpful.

Cheers,
Pablo