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?
object_id
column innotification
table? – ypercubeᵀᴹ Mar 2 '13 at 9:41object_id
column which will takeevent_id
– Harjeet Jadeja Mar 2 '13 at 10:10