CODEWITHSUNDEEP
phpmysqlsqltriggers
Taha Kirmani
Taha Kirmani

Reputation: 1274

#1442 - Can't Update Table

I have created a trigger to Insert sum of all numbers in FinalMarks column, each time a new Record inserted in student_marks table.

I am getting the following error message when i am trying to insert a new record in the table.

Kindly tell me where i am making a mistake.

Thanks

Taha

-

CREATE DEFINER = `root`@`localhost` TRIGGER `insert_student` BEFORE INSERT ON `student_marks`
FOR EACH
ROW INSERT INTO student_marks( FinalMarks )
VALUES (
AssignmentMarks + QuizMarks + Hourly1 + Hourly2 + Hourly3 + ProjectMarks
)

ERROR 

  INSERT INTO `university`.`student_marks` (

StudentMarksId , StudentId , SemisterCourseId , AssignmentMarks , QuizMarks , Hourly1 , Hourly2 , Hourly3 , FinalMarks , Grades , ProjectMarks , GPA ) VALUES ( NULL , '1', '1', '10', '15', '20', '15', '10', '', '', '', '' )

-

#1442 - #1442 - Can't update table 'student_marks' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Upvotes: 0

Views: 1864

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

Edit 1:

Its working for insertion, I also want to fire a trigger and update FinalMarks whenever Teacher Updates any Field Marks.. – Taha Kirmani

You also need a BEFORE UPDATE trigger with the same SET logic.
But trigger name and type should be different.

Example: 

DELIMITER //

CREATE DEFINER = `root`@`localhost` 
  TRIGGER `bu_student` BEFORE UPDATE ON `student_marks`
  FOR EACH ROW 
BEGIN
    SET NEW.FinalMarks = NEW.AssignmentMarks + NEW.QuizMarks
                       + NEW.Hourly1 + NEW.Hourly2 + NEW.Hourly3 
                       + NEW.ProjectMarks;
END;
//

DELIMITER ;

Original answer:

It seems you want set a specific field FinalMarks a value by summing up the values being inserted. To get this done, you need not explicitly execute another insert statement on the table.

Instead, you can just use SET command for the field, in the trigger, so that it gets accepted.

Change: 

CREATE DEFINER = `root`@`localhost` 
  TRIGGER `insert_student` BEFORE INSERT ON `student_marks`
  FOR EACH ROW 
    INSERT INTO student_marks( FinalMarks )
    VALUES (
      AssignmentMarks + QuizMarks + Hourly1 + Hourly2 + Hourly3 + ProjectMarks
    )

To: 

CREATE DEFINER = `root`@`localhost` 
  TRIGGER `insert_student` BEFORE INSERT ON `student_marks`
  FOR EACH ROW 
    SET NEW.FinalMarks = NEW.AssignmentMarks + NEW.QuizMarks
                       + NEW.Hourly1 + NEW.Hourly2 + NEW.Hourly3 
                       + NEW.ProjectMarks;

Refer to:
MySQL: CREATE TRIGGER Syntax
Look into one of the examples under User Comments on the same page.

Upvotes: 1

jmail
jmail

Reputation: 6132

Had to write PHP code manually to fix this.

http://forums.mysql.com/read.php?99,122354,240978#msg-240978

MySql Error: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Upvotes: 1

Related Questions