#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    12

    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,

    Pablo
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    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!!!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    12
    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.

    Thanks again.

    P.

IMN logo majestic logo threadwatch logo seochat tools logo