I have 2 MySQL triggers, "before update" and "after update" on the same table, when invoked, I am getting an error: Error Code: 1442

Question

Table has employee working details, like company name, start date, income tax bracket, end date, salary etc. Each employee can work in multiple companies at the same time, one of the company's "income tax bracket" must be "category 01" and remaining companie's "income tax bracket" must be "category 05".

I am trying to add 2 triggers in MySQL, one is "before update" and another one is "after update" on the same table. The "Before update" trigger is invoked when I try to add end date to a company and then trigger sets column "working_status" to "inactive". Second Trigger "after update" should invoke after updating end date for a company, it should then check if there's a row with "Active" "working status" and "category 01" income tax, and if no such row exists, it should then update one of existing "Active" row's income tax bracket to "category 01" based on earliest start date.

When tried I am getting below error: Error Code: 1442. Can't update table 'companydetails' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

TRIGGER 1:

`DROP TRIGGER IF EXISTS workingStatusUpdate;
DELIMITER //
CREATE TRIGGER workingStatusUpdate
BEFORE UPDATE ON companyDetails FOR EACH ROW
workingStUpdate: BEGIN
    DECLARE Tri_userID INT(10);
    DECLARE Tri_endDate DATE;
    DECLARE Tri_companyName VARCHAR(60);    
    DECLARE endCount INT(10);
    SET Tri_userID = new.user_id;
    SET Tri_endDate = new.end_date;
    SET Tri_companyName = new.company_name;
    SELECT count(*) INTO endCount FROM companyDetails WHERE user_id = Tri_userID 
    and company_name = Tri_companyName and Tri_endDate IS NOT NULL;
    IF endCount > 0 THEN
    SET new.working_status = 'Inactive';
    END IF;
END //
DELIMITER ;
`

TRIGGER 2:

`DROP TRIGGER IF EXISTS atleastOneCat01Row;
DELIMITER //
CREATE TRIGGER atleastOneCat01Row
AFTER UPDATE on companyDetails FOR EACH ROW
incomeTaxBracketUpdate: BEGIN
    DECLARE Tri_userID INT(10);
    DECLARE Tri_endDate DATE;
    DECLARE catOneRowCount INT(5);
    SET Tri_userID = new.user_id;
    SET Tri_endDate = new.end_date;
    SELECT COUNT(*) INTO catOneRowCount FROM companyDetails WHERE user_id = Tri_userID 
    and working_status = 'Active' and income_tax_bracket = 'Cat01';
    IF catOneRowCount = 0 THEN
    UPDATE companyDetails SET income_tax_bracket = 'Cat01' WHERE user_id = Tri_userID 
        and working_status = 'Active' ORDER BY start_date limit 1;
    END IF;
END //
DELIMITER ;`

I tried changing to before update but it did not give the output I am expecting.

score 0 · Answer 1 · answered Mar 12 '23 at 12:35

If you update a row in your table which then starts the trigger, you cannot modify a different row in that table in a trigger:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

The only row of your table you can change is the row you just updated. It's just something MySQL cannot do yet.

There are some things you can try instead:

you do the additional update in your application, e.g. whenever your application changes the table, it will also run the tax-bracket-correction-query (you basically run your own trigger).
if your users work directly on the database (e.g. run their own queries), you could do changes to that table only via stored procedures, e.g. you could have a stored procedure setCompDetEndDate that sets the end date and then also runs your additional querys. You may wamt to revoke the right to modify the table without that procedure.
you can modify your data structure and store the information differently. E.g. you could maybe add a column Cat01Company to your employee-table, and update this column in your trigger (you can modify other tables, just not companyDetails). If you then want to know if a row in company details is cat01 or cat05, you check the employee table.
you don't store this information at all, but if you want to know the tax code, you check if there is an earlier active row for the same employee.

Note that your triggercode currently doesn't check if there are 2 rows with 'Cat01' (only if there is none), so if you go for one of the first two options, you may want to add a check for that too.

I have 2 MySQL triggers, "before update" and "after update" on the same table, when invoked, I am getting an error: Error Code: 1442

1 Answers1