Q:

multiple row update on related table

I would like to update multiple rows of column 'student_total' in Table 'Teacher' when a student is deleted, using triggers/procedures
Updating multiple rows in related tables, many-to-many realtionships
'n' teachers can have 'm' students

is it possible at all ?  because its not possible to store a
result set (learnt from your site)
in Mysql, Postgress etc? 

Thanks in Adv
Ritin
---------------   SQL
CREATE TABLE `Teacher` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `student_total` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
);
CREATE TABLE `Student` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `Teacher_has_Student` (
  `teacher_id` smallint(5) unsigned NOT NULL,
  `student_id` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`teacher_id`,`student_id`),
  KEY `fk_Teacher_has_Student_teacher` (`teacher_id`),
  KEY `fk_breeder_has_breed_student` (`student_id`),
  CONSTRAINT `fk_Teacher_has_Student_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `Teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_breeder_has_breed_student` FOREIGN KEY (`student_id`) REFERENCES `Student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

i have triggers for INSERT/DELETE/UPDATE 
ex: 
---------------------- SQL
CREATE TRIGGER teacher__student_insert AFTER INSERT ON Teacher_has_Student 
FOR EACH ROW 
BEGIN
UPDATE Teacher SET student_total = student_total + 1 WHERE id = NEW.teacher_id;
END;

The trigger below updates just 1 row, whereas the aim is to update all the rows. 

DELIMITER |
CREATE TRIGGER my_student__delete AFTER DELETE ON Student 
FOR EACH ROW 
BEGIN
set @std_id = old.id;
UPDATE teacher SET student_total = student_total - 1 
WHERE id = @std_id;
END
|

A:

I think your 2nd trigger doesn't make sense.  On a delete of a student you take his id and decrease the student_total of the teacher, which has accidentally the same id as the as the studend. 
Your definition plus the first trigger should already do the job.  If you delete a student his relations are deleted from Teacher_has_Student by the CASCADE and on this DELETE your triggers should be fired to decrease the student_total of the affected teachers.  Anyway a short Google research shows, that MySQL apparently doesn't fire triggers on a cascaded delete:
http://bugs.mysql.com/bug.php?id=13102
Possible Workarounds
You could try to fix your 2nd trigger to do the job manually:
DELIMITER |
CREATE TRIGGER my_student__delete AFTER DELETE ON Student 
FOR EACH ROW 
BEGIN
    SET @std_id = old. `id`;
    UPDATE
        `Teacher` AS T
        INNER JOIN `Teacher_has_Student` AS TS ON T.`id`=TS.`teacher_id`
    SET `student_total` = `student_total` - 1 
    WHERE ST.`student_id` = @std_id;
END
|

But this will not work, if the CASCADE on the FOREIGN KEY is executed before your trigger. 
Maybe a more reliable workaround is to drop the FOREIGN KEY definitions and to create triggers on Teacher and Student to cascade the deletes and updates to Teacher_has_Student. 
Normalize it
With the workarounds you have to keep a lot of different scenarios in mind to keep student_total valid.  If it‘s not absolutely necessary to store student_total directly at the Teachers-Table, I suggest strongly not to do it. 
The information is absolutely redundant.  Keep your constraints and cascades, but drop the student_total column and delete all triggers.  Then use a JOIN to get your student numbers:
SELECT
    T.`id`, T.`name`, COUNT(*) AS student_total
FROM `Teacher` AS T
    INNER JOIN `Teacher_has_Student` AS TS ON T.`id`=TS.`teacher_id`
GROUP BY T.`id`

