MySQL Forums
Forum List  »  Triggers

INSERT Trigger which references two tables
Posted by: Dale Turley
Date: August 31, 2018 02:51AM

Hi,

I'm hoping to get some help with a trigger, which I've been stuck on for a while now.
What I need it to do is this: When a row is inserted into a table, insert that row's id and other values into another table, but use a third table to define which values to insert.

To clear this up a bit - when inserting a record into entries, insert the id and title plus the user from follows. So if a user follows the competition, they receive a row in notifications.

I keep getting syntax errors in the trigger creation.

CREATE TABLE entries
(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(512) NOT NULL,
competition BIGINT NOT NULL,
date_added DATETIME NOT NULL DEFAULT NOW(),
FOREIGN KEY (competition) REFERENCES competitions(id)
);
CREATE TABLE follows
(
user BIGINT NOT NULL,
competition BIGINT NOT NULL,
UNIQUE(user, competition),
FOREIGN KEY (user) REFERENCES users(id),
FOREIGN KEY (competition) REFERENCES competitions(id)
);

DELIMITER $$

CREATE TRIGGER trigger_name
AFTER INSERT ON entries
FOR EACH ROW BEGIN
INSERT INTO notifications (user, source, message)
SELECT follows.user, NEW.id, NEW.title
FROM follows
WHERE follows.competition = NEW.competition

END$$
DELIMITER ;

So, the result should looks like this:
notifications
User - n_source - n_message
2. - 1. - 'Post One'
3. - 1. - 'Post One'
2. - 2. - 'post Two'

Hope somebody can help.

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERT Trigger which references two tables
1940
August 31, 2018 02:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.