Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

noiticication: sender_id | receiver_id(follower_id of follow table)

I want to create trigger which inserts the values to activity and notification table whenever there is the entry for event table.
I am able to insert values to activity table because it is directly connected to event table.
but I am not able to insert to notification table because the receiver_id field in notification table is coming from 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.?

share|improve this question
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.