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?

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;