Error #1442 - Unable to Update Database Table

Question

Error #1442 - Unable to Update Database Table

I recently set up a trigger to automatically calculate the total sum of numbers in the FinalMarks column every time a new record is inserted into the student_marks table.

Unfortunately, I encountered an error message when attempting to insert a new record into the table.

If you could please identify where my mistake is, I would greatly appreciate it.

Thank you,

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 , SemesterCourseId , 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.

php mysql sql triggers

Answer 1

Answer №1

Edit 1:

It's functioning properly for insertion, I would also like to trigger an update on FinalMarks whenever a Teacher updates any Field Marks. – Taha Kirmani

In addition to the BEFORE UPDATE trigger with the same SET logic,
Ensure that the trigger name and type are different.

For instance,:

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 appears that you want to assign a specific value to the field FinalMarks by summing up the inserted values. This can be achieved without executing another insert statement on the table.

Rather than using another insert statement, you can simply utilize the SET command within the trigger code to accomplish this task.

Modification:

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 achieve this:

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;

Reference:
MySQL: CREATE TRIGGER Syntax
Take a look at one of the examples in the User Comments section on the linked page.

Answer 2

Edit 1:

It's functioning properly for insertion, I would also like to trigger an update on FinalMarks whenever a Teacher updates any Field Marks. – Taha Kirmani

In addition to the BEFORE UPDATE trigger with the same SET logic,
Ensure that the trigger name and type are different.

For instance,:

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 appears that you want to assign a specific value to the field FinalMarks by summing up the inserted values. This can be achieved without executing another insert statement on the table.

Rather than using another insert statement, you can simply utilize the SET command within the trigger code to accomplish this task.

Modification:

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 achieve this:

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;

Reference:
MySQL: CREATE TRIGGER Syntax
Take a look at one of the examples in the User Comments section on the linked page.

Answer 3

Answer №2

Had to manually write PHP code in order to resolve this issue.

Visit this forum thread for more information

Encountering a MySql error: Unable to update table in stored function or trigger due to prior usage by invoking statement

Answer 4

Had to manually write PHP code in order to resolve this issue.

Visit this forum thread for more information

Encountering a MySql error: Unable to update table in stored function or trigger due to prior usage by invoking statement

Error #1442 - Unable to Update Database Table

Answer №1

Answer №2

Similar questions

Are there any issues with this Ajax request?

When a link is clicked, submit a form and send it to several email addresses simultaneously

PHP Issues with PHP While and ForEach loops causing unexpected outcomes

How should you handle a situation where a function is missing one argument?

Having HWIOauthBundle version 0.3.*@dev installed on Symfony 2.3.4 resulted in encountering the common error message "No oauth code present in the request."

I'm still searching for a proper solution on how to access JavaScript/jQuery functions within Colorbox

The "Submit Review" button is malfunctioning, as it inadvertently adds the product to the cart

can access the directory but unable to see the contents within

Search and extract JSON data in MySQL

Utilizing the 24-hour clock for both the date and time

Just need a quick example to kick start my project using fopen and curl for making requests

Guide to updating commas in fetchall to three columns using psycopg2

Omitting row information from a database using php and jquery

Using a single form, the rest of the form fields are restricted when uploading images with Ajax

Dividing an array in Laravel according to the key value

How to display specific MySQL results at the top of the query result

PHP and Ajax: Steps to start a session once a login is successfully completed (remaining code functions normally)

What is the reason for needing to use utf8_decode prior to inserting a UTF8 string into MySQL?

What is the best way to send a database connection to a PHP method?

Pass the response from a MySql query executed in ExpressJS to a basic JavaScript file that is responsible for modifying the