1

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?

  • Is there a object_id column in notification table? – ypercubeᵀᴹ Mar 2 '13 at 9:41
  • yes there is object_id column which will take event_id – Harjeet Jadeja Mar 2 '13 at 10:10
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.