#1442 - Can't Update Table

Question

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.

As per my understanding you should use "after insert" instead of "before insert" ... — Zafar Malik, Apr 02 '14 at 06:57
Let me get this correct. You have shown a `before insert` trigger. Then you get an error on an `update`. That trigger has nothing to do with the error. — Gordon Linoff, Apr 02 '14 at 07:01
I think @jmail has answered you actually problem is that you are trying to update same table on which trigger is invoking. As trigger invoke on any event on the table it will lock the table to do the task mentioned in trigger. In this case trigger lock the table and again trying to update the same table, which is not possible. So you can achieve this by transaction..in single transaction first insert row then update desired column then either commit statement or rollback on failure. — Zafar Malik, Apr 02 '14 at 07:11

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

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

edited May 23 '17 at 11:49

Community

1
1

answered Apr 02 '14 at 06:52

jmail

5,944
3
21
35

Ravinder Reddy · Accepted Answer · 2014-04-02T09:50:26.910

1

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.

edited Apr 02 '14 at 09:50

answered Apr 02 '14 at 07:55

Ravinder Reddy

23,692
6
52
82

When i am trying to do this for `before update`, its not generating any error message but its also not working..and if i change it to `after update` then it stops supporting `NEW` Keyword. – Taha Kirmani Apr 02 '14 at 09:37
1

not working with `before trigger` means? what happened when issued a `insert` statement? Record is stored or not? If yes, the same values or not? Please explain. – Ravinder Reddy Apr 02 '14 at 09:41
Its working for insertion, I also want to fire a trigger and update `FinalMarks` whenever Teacher Updates any Field Marks.. – Taha Kirmani Apr 02 '14 at 09:44
1

Check my updated answer. I have included code snippet for `update` trigger. – Ravinder Reddy Apr 02 '14 at 09:51
Thank You so Much...Now The problem is that its not generating any error message, but its also not Updating `FinalMarks` Field.. – Taha Kirmani Apr 02 '14 at 09:55
1

what was the data before? what was your input? What is the data after? – Ravinder Reddy Apr 02 '14 at 09:57
I am updating data through phpmyadmin, Just inputting random numbers in `QuizMarks,AssignmentMarks,Hourly...`, but `FinalMarks` field is not updating... Kindly check the image from the following link. http://postimg.org/image/rpf82028t/ – Taha Kirmani Apr 02 '14 at 10:02
1

I am not sure how you are using PHPMyAdmin. But why don't you try an `update` statement in SQL editor on it and submit. Then browse through records and see if update worked or not? – Ravinder Reddy Apr 02 '14 at 10:07

#1442 - Can't Update Table

2 Answers2