Sample Triggers

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnInsert`//

CREATE TRIGGER `fjm`.`updateListingCountOnInsert` AFTER INSERT ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT count( * )+0 FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

END

//

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnUpdate`//

CREATE TRIGGER `fjm`.`updateListingCountOnUpdate` AFTER UPDATE ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT COUNT(*)  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

update `fjm_listing_breeds` lb  set lb.status = 0 where lb.listing_id = NEW.id;

update `fjm_listing_breeds` lb  set lb.status = 1 where lb.listing_id = NEW.id and NEW.status= 1 and NEW.is_paid = 1 and NEW.is_delete = 0;

END

//

DROP TRIGGER IF EXISTS `fjm`.`updateListingCountOnDelete`//

CREATE TRIGGER `fjm`.`updateListingCountOnDelete` AFTER DELETE ON `fjm`.`fjm_listings`

 FOR EACH ROW BEGIN

UPDATE fjm_classes b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1 AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id GROUP BY l.class_id);

UPDATE fjm_classes b set active_listing_count = (SELECT COUNT(*)+0   FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.class_id AND l.sold='0' GROUP BY l.class_id);

UPDATE fjm_states b set listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id GROUP BY l.state_id);

UPDATE fjm_states b set active_listing_count = (SELECT COUNT(*)+0  FROM fjm_listings l WHERE l.status = 1  AND l.is_delete = 0 AND is_paid =1  AND b.id = l.state_id AND l.sold='0' GROUP BY l.state_id);

UPDATE fjm_breeds_classes bc set bc.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND bc.breed_id = lb.breed_id AND bc.class_id = l.class_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id, l.class_id);

UPDATE fjm_breeds b set b.listing_count = (SELECT COUNT(*)+0  FROM `fjm_listing_breeds` lb, fjm_listings l WHERE lb.listing_id = l.id AND b.id = lb.breed_id AND  l.status = 1 and l.is_paid = 1 and l.is_delete = 0 GROUP BY lb.breed_id);

END

//