CODEWITHSUNDEEP
mysqltriggers
Flock Dawson
Flock Dawson

Reputation: 1878

MySQL on update trigger

So, I have a MySQL table costumer with some fields

table <Customers>
ID    Name    Description    State
0     Jack    waiting        0
1     Jane    complete       1

I want to write a trigger, which updates the State field It needs to set the value of State to 1, if the Description is updated and gets the value complete

So far, I've come up with this:

delimiter $$
CREATE TRIGGER customerUpdated 
    BEFORE UPDATE ON Customers
    FOR EACH ROW 
BEGIN
    IF NEW.Description = 'completed' THEN  
    UPDATE Customers SET State = '1' WHERE id = OLD.id;
END IF;
END$$
delimiter ;

But I get the error:

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

Any help appreciated!

Upvotes: 1

Views: 198

Answers (1)

sgeddes
sgeddes

Reputation: 62831

I think you just need to set NEW.State instead of running an UPDATE statement at all:

IF NEW.Description = 'completed' THEN  
   SET NEW.State = 1;
END IF

Upvotes: 1

Related Questions