Error in creating trigger

Question

I have 4 tables:

event:
event_id(p.k) | uid | circle_id(f.k)

activity:
uid | performed_activity_id(f.k->event_id) | activity_type_id

follow:
follower_id | circle_id(f.k)

notification:
sender_id | receiver_id(follower_id of follow table)

I want to create a trigger which inserts values into the activity and notification tables whenever there is an entry for event table.
I am able to insert values into the activity table because it is directly connected to event table.

However, I am not able to insert into the notification table because the receiver_id field in the notification table is coming from the follow table which is connected to event table by circle_id.

Here I am using select in trigger which is actually wrong.

DROP TRIGGER IF EXISTS `InsertToActivity` ;
CREATE TRIGGER `InsertToActivity` AFTER INSERT ON `event` 
FOR EACH ROW 
begin 
 INSERT INTO activity( uid, performed_activity_id, activity_type_id ) 
 VALUES (new.uid, new.event_id, '1');
 select follower_id from folow where circle_id=new.circle_id;
 insert into notification_table (sender_id,object_id,receiver_id) 
 values (new.uid,new.event_id,new.follower_id);
end;

Is it good way to do this type of work using TRIGGERS?

• ypercubeᵀᴹ: Is there a object_id column in notification table?
• Harjeet Jadeja: yes there is object_id column which will take event_id

Answer 1

I assume new.follower_id is supposed to be:

select follower_id from folow where circle_id=new.circle_id;

I don't think this will work. If you want to use this approach you need to declare a variable for follower_id and select into that. However, a better approach (IMO) is to do:

INSERT INTO activity( uid, performed_activity_id, activity_type_id ) 
 VALUES (new.uid, new.event_id, '1');
insert into notification_table (sender_id,object_id,receiver_id)
 select new.uid ,new.event_id, follower_id 
 from folow where circle_id=new.circle_id;

Answer 2

The trigger must be created using DELIMTER like this

DROP TRIGGER IF EXISTS `InsertToActivity` ;
DELIMITER $$
CREATE TRIGGER `InsertToActivity` AFTER INSERT ON `event` 
FOR EACH ROW 
begin 
 INSERT INTO activity( uid, performed_activity_id, activity_type_id ) 
 VALUES (new.uid, new.event_id, '1');
 select follower_id INTO @fid from folow where circle_id=new.circle_id;
 insert into notification_table (sender_id,object_id,receiver_id) 
 values (new.uid,new.event_id,@fid);
end $$
DELIMITER ;

or

# Code from Lenhart's Answer
DROP TRIGGER IF EXISTS `InsertToActivity` ;
DELIMITER $$
CREATE TRIGGER `InsertToActivity` AFTER INSERT ON `event` 
FOR EACH ROW 
begin 
 INSERT INTO activity( uid, performed_activity_id, activity_type_id ) 
 VALUES (new.uid, new.event_id, '1');
 insert into notification_table (sender_id,object_id,receiver_id)
 select new.uid ,new.event_id, follower_id 
 from folow where circle_id=new.circle_id;
end $$
DELIMITER ;