Empeus Empeus - 6 months ago 11
MySQL Question

Simple TRIGGER which deletes a specific record based on a query

I have a problem with a TRIGGER in MySql.

notifications_posts
notification_id from_user on_post_id in_group_id date
2 1 162 3 2012-07-11 12:01:08
3 19 163 1 2012-07-11 12:03:26
4 19 164 1 2012-08-10 17:42:36
5 1 165 3 2012-08-29 12:14:01
6 1 165 3 2012-08-29 12:14:29
11 1 2 3 2012-08-29 14:38:42


Sometimes I need to delete posts, for example with this command:

DELETE FROM posts WHERE posts.post_id = 165;


After this, I want a TRIGGER that deletes all records from
notification_id
according to that id.

This is what I have so far:

DELIMITER $$
CREATE OR REPLACE TRIGGER CleanNotificationPosts
AFTER DELETE ON posts
FOR EACH ROW
BEGIN
DELETE FROM notifications_posts WHERE notifications_posts.id = THAT_ID_TO_DELETE;
END $$
DELIMITER ;


Is my syntax correct? Do I really need delimiters?

Answer
  1. do I really need delimiters?

    With the syntax as you currently have it: yes, you do need to use a delimiter other than the default ;—otherwise the CREATE TRIGGER statement will be terminated by the ; before the END keyword, which will result in a parse error (as the BEGIN ... END block will not have been terminated).

    However, since there is only one statement inside that block, you don't actually need to use BEGIN ... END at all:

    CREATE OR REPLACE TRIGGER CleanNotificationPosts
    AFTER DELETE ON posts
    FOR EACH ROW
    DELETE FROM notifications_posts WHERE notifications_posts.id = THAT_ID_TO_DELETE;
    

    In this case, no change of statement delimiter is required: the ; is indeed where the CREATE TRIGGER statement is terminated.

  2. Is my syntax correct?

    Almost, but not quite. Instead of THAT_ID_TO_DELETE, you should use OLD.post_id. As stated in CREATE TRIGGER Syntax:

    You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

    Also, OR REPLACE is not valid in MySQL. You can instead DROP TRIGGER IF EXISTS before executing your CREATE TRIGGER statement.

    Therefore:

    DROP TRIGGER IF EXISTS CleanNotificationPosts;
    CREATE TRIGGER CleanNotificationPosts
    AFTER DELETE ON posts
    FOR EACH ROW
    DELETE FROM notifications_posts WHERE notifications_posts.id = OLD.post_id;
    
  3. However, in this situation you don't actually need to use a trigger at all (as a foreign key constraint with ON DELETE CASCADE will suffice):

    ALTER TABLE notifications_posts
      ADD FOREIGN KEY (id) REFERENCES posts (post_id) ON DELETE CASCADE;
    

    Note the following pre-requisites in order to add such a foreign key constraint:

    • both tables must use the InnoDB storage engine;

    • there must be an index in the posts table of which post_id is the first column; and

    • there must be a matching record in posts.post_id for all non-NULL notifications_posts.id (down to the column type, including its size & sign).

Comments