Can I update the just added row using MySQL triggers

Question

The default initial value of one column in my database is the same as the row's auto-incremented id. I'm trying to use triggers to set it.

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
BEGIN  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 
END

But this keeps throwing a syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

I've tried all sorts of permutations of this with no luck. Can anyone see what I'm doing wrong?

Answer 1

As zerkms said, you need to change the delimeter. But since you only use 1 line of code, you don't need the BEGIN and END. And that way, you don't need to change the delimiter either

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 

Since you are getting an error you cannot update the row, I suggest the following:

Do NOT perform the update query at all. On default the order value = the ID value. So when the order value changes, you can update it properly.

If you are requesting the data with php, do something like this:

$order = $row['order'];
if ($order == '')
    $order = $row['id'];

After you need it updating, you've got the correct value.

Answer 2

I don't think you can do that. An AFTER INSERT trigger cannot modify the same table, neither by issuing an UPDATE nor by something like this:

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
    SET NEW.`order` = NEW.id ; 

which results in this error:

> Error Code: 1362. Updating of NEW row is not allowed in after trigger

---

You can't either use a BEFORE INSERT trigger because then the NEW.id is not known (if you modify the above, the order column will get 0 value after the Insert.

---

What you can do, is use a transaction:

START TRANSACTION ;
  INSERT INTO clusters (id)
    VALUES (NULL);
  UPDATE clusters
    SET `order` = id
    WHERE id = LAST_INSERT_ID();
COMMIT ;

Answer 3

You get the error because mysql treats ; in line 5 as the end of your trigger declaration, which obviously leads to the syntax error.

So you need to redefine delimiter before you specify the trigger body:

delimiter |

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
BEGIN  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 
END;
|

delimiter ;