I have a problem with a TRIGGER in MySql.
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
DELETE FROM posts WHERE posts.post_id = 165;
CREATE OR REPLACE TRIGGER CleanNotificationPosts
AFTER DELETE ON posts
FOR EACH ROW
DELETE FROM notifications_posts WHERE notifications_posts.id = THAT_ID_TO_DELETE;
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
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.
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.col_namerefers to a column of an existing row before it is updated or deleted.
NEW.col_namerefers to the column of a new row to be inserted or an existing row after it is updated.
OR REPLACE is not valid in MySQL. You can instead
DROP TRIGGER IF EXISTS before executing your
CREATE TRIGGER statement.
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;
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-
notifications_posts.id (down to the column type, including its size & sign).